以下我收集到的文档,但好像不行啊。
=======================================================================
首先用ADO和远程SQL库建立联接:设CN1为远端SQL库的激活联连,我要将本地ACCESS库(假设为D:\TEST\DB1.MDB)中的TAB5表中的内容复制到CN1联连中的TAB1库(两库数据结构完全相同)
现用如下语句:
cn1.Execute "insert into tab1 select * from " + "[;database=D:\test\db1.mdb].tab5"
=======================================================================
=======================================================================
首先用ADO和远程SQL库建立联接:设CN1为远端SQL库的激活联连,我要将本地ACCESS库(假设为D:\TEST\DB1.MDB)中的TAB5表中的内容复制到CN1联连中的TAB1库(两库数据结构完全相同)
现用如下语句:
cn1.Execute "insert into tab1 select * from " + "[;database=D:\test\db1.mdb].tab5"
=======================================================================
Dim j As Integer
Dim info As String
Dim databasename(18) As String
databasename(0) = "AbandonBook": databasename(1) = "AbandonMagzine": databasename(2) = "Addbook"
databasename(3) = "AddMagzine": databasename(4) = "AddText": databasename(5) = "Books"
databasename(6) = "BorrowBook": databasename(7) = "BorrowMagzine": databasename(8) = "BorrowTime"
databasename(9) = "Class": databasename(10) = "Department": databasename(11) = "Destine"
databasename(12) = "GrantText": databasename(13) = "Magzines": databasename(14) = "Member"
databasename(15) = "TextBook": databasename(16) = "user1": databasename(17) = "publish": databasename(18) = "booktype"'未选择导入文件及路径
If dbpath = "" Then GoTo recover
'提示
msg = "原数据库中的数据将被覆盖,是否继续!": Style = 3 + 48 + 0: Title = "导入警告!"
result = MsgBox(msg, Style, Title)
If result <> 6 Then GoTo recover
For j = 0 To 18 '数据库中的数据表的数目
info = DataSave(databasename(j)) '单表复制传入表名
On Error GoTo over:
Next j
MsgBox "数据导入成功!完全OK!", vbOKOnly + vbExclamation, "数据导入提示"
Unload Me
menu.Enabled = True
Exit Subover:
MsgBox "第" & j + 1 & "个文件导入时出错!", vbOKOnly + vbExclamation, "导入错误提示"
MsgBox Err.Number & " " & Err.Description, vbOKOnly + vbExclamation, "导入错误提示"
Exit Sub
recover:
Unload datarecov
menu.Enabled = True
End SubPrivate Function DataSave(ByVal databasename As String) As String
Dim txtSQL As String
Dim MsgText As String
Dim mrc As ADODB.Recordset
txtSQL = "select * from " & databasename
Set mrc = ExecuteSQL(txtSQL, MsgText)
Dim CN As New ADODB.Connection '定义数据库的连接
Dim rs As New ADODB.Recordset
If dbpath = "" Then Exit Function
CN.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & dbpath & ";Persist Security Info=False"
'dbpath 是存放数据库的位置
CN.Open
rs.CursorLocation = adUseClient
rs.Open "select * from " & databasename, CN, adOpenDynamic, adLockBatchOptimistic
'm = mrc.Fields.Count
Set DataGrid1.DataSource = rs
'表为空时导入下一个表
If rs.EOF = True Then GoTo over
'清空原库中的数据SQL Server 中的数据
If mrc.EOF = True Then GoTo datacopy
mrc.MoveFirst
Do While Not mrc.EOF
mrc.Delete
mrc.MoveNext
Loop
datacopy:
'从ACCESS复制数据到构SQL库
Do While Not rs.EOF
i = 0
mrc.AddNew
Do While i < rs.Fields.Count 'm
If Trim(rs.Fields(i)) <> "" Then
mrc.Fields(i) = Trim(rs.Fields(i))
Else
End If
i = i + 1
Loop
rs.MoveNext
Loop
mrc.Update
over:
mrc.Close
End Function