下面是用DAO将Excel导入Access的例子,修改一下应该能实现
Private Sub ExportExcelSheetToAccess(sSheetName As String, sExcelPath As String, sAccessTable As String, sAccessDBPath As String)
Dim db As Database
Dim rs As Recordset
Set db = OpenDatabase(sExcelPath, True, False, "Excel 5.0")
Call db.Execute("Select * into [;database=" & sAccessDBPath & "]." & sAccessTable & " FROM [" & sSheetName & "$]")
MsgBox "Table exported successfully.", vbInformation, "Yams"
End Sub
Dim conn1 As New ADODB.Connection
Dim conn2 As New ADODB.Connection
Dim str As String
str="Provider=Microsoft.Jet.4.0;Database=" & $PATH$ & "\AAA.mdb;PWS=;"
conn1.open str
str="Provider=Microsoft.Jet.4.0;Database=" & $PATH$ & "\BBB.mdb;PWS=;"
Dim rs As New ADODB.Recortset
str="select * from table"
rs.Open str,conn,3,4
Dim i As Integer
For i = 1 To rs.RecordCount
str="insert into table(" & $FIELD1$ & …… & $FIELDn$ & ") " & _
"values('" & rs("field1") & "','" & …… & rs("field1") & _
"')"
conn2.Execute(str)
rs.MoveNext
Next i