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

来源：互联网发布：海军知耻陆军马鹿编辑：程序博客网时间：2024/04/28 19:17

在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.CloseEnd 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)

0 0