Consolidate tables from multiple databases into one database. (1 Viewer)

[Ajit Singh]

Hello

I have one Master database and multiple child databases in a folder. Each of these databases (both Parent and Child) have identical table called "source". I need a VBA code which will consolidate tables of child database into the Parent database table.

Thanks

 

[sensetech]

Is this a one-off exercise? If so, in the master database create linked tables to the tables in each of the child databases then then use queries to append the child data to the master data. No need for VBA.

 

[Ajit Singh]

But the plan is to roll out the databases to different locations and I want to provide them a command button which will consolidate the child databases tables into one table in Master database. I will just circulate them a instruction to save both Parent and Child databases in one folder (or whatever the best solution is). How will the concept of linked table will serve the purpose...user may save the db's according to there comfort. Please help

 

[jdraw]

Just for clarity, what is a child database?
Are you familiar with split databases?
Back end front end splits.

http://www.accessmvp.com/twickerath/articles/multiuser.htm

https://www.fmsinc.com/MicrosoftAccess/DatabaseSplitter/

 

[Ajit Singh]

By Child database I mean......in one folder (location wise), I will have one Regional database and multiple Office wise databases (i.e Child). When I will roll out these databases to differenct people around the world, they should have the ability to consolidate the data of multiple databases into one so that they can view the data for all at one place instead of going one by one and there are several other reasons & requirements like this coz of which I need something which can combine all the child db's into one Master.

 

[Ajit Singh]

I have one code to do the required job but this has certain limitations. In this I need to mention the filenames of all the databases present in the folder. Can somebody help with the code which can just surf inside the specific folder and combines the relevant tables into one master database for me? Here's the code am using and need help on it
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Dim DBFileList As Collection
Dim DBPath As String
Dim ForeignTableName As String
Dim LocalTableName As String
Dim db As DAO.Database
Dim rst, drst As DAO.Recordset
Dim fld As DAO.Field
' Configure
Set DBFileList = New Collection
DBFileList.Add "Al Khobar.accdb" ' db#1
DBFileList.Add "Anchorage.accdb" ' db#2
DBPath = "C:\D\Scorecards-DEV\Office" ' (No Trailing Backslash)
ForeignTableName = "consolidate_input"
LocalTableName = "consolidate_input"
Set drst = CurrentDb.OpenRecordset(LocalTableName)
For Each dbfile In DBFileList
Debug.Print "Transferring Data From " & dbfile
Set db = DBEngine.Workspaces(0).OpenDatabase(DBPath & "\" & dbfile)
Set rst = db.OpenRecordset(ForeignTableName)
Do Until rst.EOF
drst.AddNew
For Each fld In rst.Fields
If (fld.Attributes And dbAutoIncrField) = dbAutoIncrField Then
' We have an autonumber field - lets skip
Else
drst.Fields(fld.Name).Value = fld.Value
End If
Next
drst.Update
rst.MoveNext
Loop
rst.Close
DoEvents
Next
drst.Close
Set rst = Nothing
Set drst = Nothing
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 

[Cronk]

Just as you should define your table structure before you develop forms and reports, you should be designing your overall system and how you are going to handle various issues before you start writing code to import data.

You have not given any indication of what your system is. I'll assume it is something like customers and sales. If Region1 adds a customer, it will have Customer ID of 1, similarly Region 2. When the data is imported into the master table, the two customers cannot have the same ID. Lets say in the master database, the Region2 Customer 1 has an id of 4 in the master table. Future sales in region 2 are added to customer 1, but when imported have to be put against customer 4.

So it is a complexity to handle this.

Lets say customer 1 in region 2 changes information eg phone number. Now you have to not only add data but check for changes.

MS does provide some assistance with this. Look up Replication.

 

[nanscombe]

Depending on your version of Access you may find that Replication has been discontinued.



When I did this sort of thing, back in Access 97 & 2003, I added some special fields to all of my tables.

ID * - obviously the unique identifier of the record.

deleted - a Yes/No field to show whether a record had been marked as deleted. If you physically delete a record there is always a danger that it may reappear from an import at a later date.

dsChanged - a datestamp (Date/Time field) that is updated every time a record is changed.

When I imported data I would match the main data against the imported data by using the ID and update the records based on the newest datestamp.


* As far as the ID field was concerned I never relied on using an Autonumber because I wanted uniqueness across all of the copies of the database. Also Autonumber can be a bit of a bother when trying to add extra records from another source. I used to generate a range of Long Integers based on which building the database was in.

Given that the largest value that can be stored is 2,147,483,647 I figured I could have up to 213 separate databases each having numbers d,ddd,000,001 - d,ddd,999,999 where d,ddd represented the database instance. 426 if I used the negative numbers as well.

Building 1 would generate numbers 1,000,001 - 1,999,999

Building 39 would generate numbers 39,000,001 - 39,999,999

Building 67 would generate numbers 67,000,001 - 67,999,999
etc.

Of course how many records being produced a year would have an impact on this. If I had got close to running out of numbers I would have switched the database to use another range of numbers.

 

[Ajit Singh]

tables are of same structure and there's no concept of Primary Key in any of the table as records will repeat but I have something inside the table which will establish which record is of which database....just want to append tables into one (of the master db).