在VBA中实现两个ACCESS数据库之间的数据导出与导入

在VBA中实现两个ACCESS数据库之间的数据导出与导入
分类： ACCESS
1.导出数据。
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentProject.Path & "\Destination.mdb", acTable, "SourceTableName", "DestinationTableName", False, False
 
2.导入数据。

DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentProject.Path & "\Destination.mdb", acTable, "SourceTableName", "DestinationTableName", False, False

    vba ado方式操作mdb
    Dim cn As New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=123;Data Source=C:\会计从业资格考试\Test.mdb"
    cn.Execute "delete from cache_tiku where tixing is null"
    cn.Execute "delete from cache_tiku where timian is null"

CurrentProject.Connection.Execute "INSERT INTO [;DATABASE=E:/first.mdb;].PInfo SELECT * FROM [;DATABASE=E:/second.mdb;].PInfo"

下面的示例从 Microsoft Access 数据库 NWSales.mdb 中将“NW Sales for April”报表导入到当前数据库的“Corporate Sales for April”报表中：

DoCmd.TransferDatabase acImport, "Microsoft Access", _
    "C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _
    "Corporate Sales for April"

下一个示例将 ODBC 数据库 (ODBC Database) 的“Authors”表链接到当前数据库中：

DoCmd.TransferDatabase acLink, "ODBC Database", _
    "ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=us_english;" _
    & "DATABASE=pubs", acTable, "Authors", "dboAuthors"

Sub OpenDB()
 Dim db As DAO.Database
 Dim ws As DAO.WorkSpace
 Dim rst As DAO.Recordset
 Set ws = DBEngine.WorkSpaces(0)
 Set db = ws.OpenDatabase _
 ("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
 False, False, "MS Access;PWD=northwind")
 Set rst = db.OpenRecordset("Customers", dbOpenDynaset)
 If rst.RecordCount > 0 Then
   rst.MoveLast
   MsgBox rst!CustomerID
 End If
 rst.Close
 db.Close
End Sub

Set dbs = OpenDatabase(DBPanel, False, False, ";pwd=nicola")

    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(mdbFilePath.Value, True, True, "MS Access;PWD=^Mn*(#Vx&$K@7r5I!)@^")
    DoCmd.TransferDatabase acImport, "Microsoft Access", db.Name, acTable, "tigan", "mdb_tigan"

   ' Dim objJRO As Object
    'Dim TempData As String
    'Set objJRO = CreateObject("JRO.JetEngine")
    'Dim objAcc As New Access.Application
    'Call objAcc.CompactRepair("F:\MY\百度云同步盘\云盘\RTF\TransferData.mdb", "F:\MY\百度云同步盘\云盘\RTF\TransferData.mdb.bak", False)