请教 不同数据源记录复制

KenjiSato

    Dim CNN1 As ADODB.Connection, CNN2 As ADODB.Connection
    Dim RST1 As ADODB.Recordset, RST2 As ADODB.Recordset
   
   
    Set CNN1 = New ADODB.Connection
    CNN1.ConnectionString = "DRIVER={SQL Server};" & "SERVER=SERVER1;UID=SAWD=;DATABASE=SQL数据库"
    CNN1.Open
    Set RST1 = CNN1.Execute("表1")

   
    CNN2.ConnectionString = "rovider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\数据库1.mdb;"
    CNN2.Open
    Set RST2 = New ADODB.Recordset
    RST2.Open "SELECT * From 表1;", CNN2, adOpenKeyset, adLockOptimistic
   
   
    '两个数据库的表结构是一样的,希望从SQL的表将数据批量更新到ACCESS的表中该如何做？
   
能否用操作查询？

谢谢！

zhuyiwen

用循环遍历

[em24]

KenjiSato

下面的代码能行但用ADSL太慢了，有无更好的方法？
Dim cnn0 As ADODB.Connection
    ' 本地的ACCESS数据库(数据源)
     Dim cnn1 As ADODB.Connection
     ' 远程的SQL Server7.0数据库(目标库)
     
     Dim rst0 As ADODB.Recordset
     Dim rst1 As ADODB.Recordset
     
     Dim strCnn As String
     
     ' 打开数据源连接。
     Set cnn0 = New ADODB.Connection
     strCnn = "数据源" ' 已建立DSN为"数据源"的ODBC
     cnn0.Open strCnn
     
     
     ' 打开目标库连接。
     Set cnn1 = New ADODB.Connection
     strCnn = "rovider=sqloledb;Data Source=srv;Initial Catalog=pubs;User Id=saassword=8848;"
     cnn1.Open strCnn
     
     ' 打开数据源表。
     Set rst0 = New ADODB.Recordset
     rst0.CursorType = adOpenKeyset
     rst0.LockType = adLockOptimistic
     rst0.Open "数据源表", cnn1, , , adCmdTable
     
     ' 打开目标表。
     Set rst1 = New ADODB.Recordset
     rst1.CursorType = adOpenKeyset
     rst1.LockType = adLockOptimistic
     rst1.Open "目标表", cnn1, , , adCmdTable
     
     rst0.Movefirst
     do until rst0.EOF
     rst1.AddNew
     rst1!emp_id = rst0!emp_id
     rst1!fname = rst0!fname
     rst1!lname = rst0!lname
     rst1.Update
     rst0.Movenext
     loop
     
     rst1.Close
     cnn1.Close
     
     rst0.Close
     cnn0.Close
     
    End Sub

zhuyiwen

主要是你的SQL连接有问题,要指明客户游标!

[em24]

KenjiSato

能写一下代码吗？


谢谢！

zhuyiwen

Sub test()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
   
    With cn
        .ConnectionString = "..."
        .CursorLocation = adUseClient
        .Open
    End With
   
    With rs
        Set .ActiveCommand = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .Open "table"
    End With
    .
    .
    .
End Sub

zhuyiwen

Sub test()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
   
    With cn
        .ConnectionString = "..."
        .CursorLocation = adUseClient
        .Open
    End With
   
    With rs
        Set .ActiveCommand = cn
        .CursorLocation = adUseClient
        .CursorType = adOpenForwardOnly
        .Open "table"
    End With
    .
    .
    .
End Sub