Ms access合并多个Access文件表

Question

我有60个MS Access文件与相同的数据库结构。我想从两个表中的数据，这是从每个数据库的关系，以使一个单一的数据库中的所有记录从这60个文件。有没有什么简单的方法可以合并所有这些MS Access文件？

Answer 1

如果我没有理解错的话，您有相同的表分布在60个数据库文件中，并且您正在寻找一种自动聚合它们的方法。

有几种不同的方法可以做到这一点。这可能取决于你的环境。我已经演示了两种不同的方法。

第一种方法很简单。它只是构建一个静态查询，将数据库名称替换到每个查询中。如果你的细节过于简单化，那么这将会起到作用。

第二种方法使用DAO打开每个数据库中的每个表，并将数据写入当前数据库。如果您有一次性的异常并需要添加一些智能，则此方法非常有用。

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

示例#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

您将需要根据您的特定情况定制代码-但它应该可以做到这一点。