StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>If I understand you correctly, you have identical tables spread across 60 database files, and you are looking at a way of automating their aggregation.</p> <p>There's a few different ways you can do this. It'll probably depend on your circumstances. I've demonstrated two different approaches. </p> <p>The first method is straightforward. It simply builds a static query, substituting the database name into each query. If your specifics are simplistic - then this should do the trick.</p> <p>The second method uses DAO to open each table in each database and write the data to the current database. This method is beneficial if you have one-off exceptions and need to add some intelligence.</p> <pre><code>Public Sub SimpleCombine() Dim DBFileList As Collection Dim DBPath As String Dim ForeignTableName As String Dim LocalTableName As String Dim dbfile As Variant ' Configure Set DBFileList = New Collection DBFileList.Add "Test1.accdb" DBFileList.Add "Test2.accdb" DBPath = CurrentProject.Path ' (No Trailing Backslash) ForeignTableName = "Fruit" LocalTableName = "Fruit" For Each dbfile In DBFileList querystr = "INSERT INTO Fruit (FruitName, FruitValue) " & _ "SELECT FruitName, FruitValue " & _ "FROM Fruit IN '" & DBPath & "\" & dbfile & "'" Debug.Print "Transferring Data From " & dbfile CurrentDb.Execute querystr DoEvents Next End Sub </code></pre> <p>Example #2</p> <pre><code>Public Sub DAOCombine() 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 "Test1.accdb" DBFileList.Add "Test2.accdb" DBPath = CurrentProject.Path ' (No Trailing Backslash) ForeignTableName = "Fruit" LocalTableName = "Fruit" 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 End Sub </code></pre> <p>You'll need to tailor the code to your specific circumstances - but it should do the trick.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload