分类: 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)