CODEWITHSUNDEEP
vbams-access
sanjay
sanjay

Reputation: 51

Ms access Merge Multiple Access file tables

I have 60 MS Access files with the same database structure. I want to take data from two tables which are in relation from each database to make one single database with all of the records from those 60 files. Is there any easy way to merge all those MS Access files?

Upvotes: 1

Views: 4201

Answers (2)

user19240261
user19240261

Reputation:

If the datastructure is the same you can use a JOIN Query.

SELECT PORDER_ARV.*
FROM PORDER_ARV
UNION 
SELECT PORDER_RAN.*
FROM PORDER_RAN
UNION
SELECT PORDER_HOL.*
FROM PORDER_HOL
UNION SELECT PORDER_HIN.*
FROM PORDER_HIN
ORDER BY PURCHASE_CODE;

When you use Union All ,you will also get duplicates, you can write vba code to auto construct this sql string with your 60 table names. I don't no if the union function works with 60 tables?

Upvotes: 0

DHW
DHW

Reputation: 1196

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.

There's a few different ways you can do this. It'll probably depend on your circumstances. I've demonstrated two different approaches.

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.

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.

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

Example #2

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

You'll need to tailor the code to your specific circumstances - but it should do the trick.

Upvotes: 3

Related Questions