Office中国论坛/Access中国论坛

标题: 请教不同数据源记录复制 [打印本页]

作者: KenjiSato 时间: 2003-8-28 04:10
标题: 请教不同数据源记录复制

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=SA

WD=;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 时间: 2003-8-28 17:52
用循环遍历

[em24]

作者: KenjiSato 时间: 2003-8-28 17:57
下面的代码能行但用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=sa

assword=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 时间: 2003-8-28 18:23
主要是你的SQL连接有问题,要指明客户游标!

[em24]

作者: KenjiSato 时间: 2003-8-28 18:28
能写一下代码吗？

谢谢！

作者: zhuyiwen 时间: 2003-8-28 18:34
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 时间: 2003-8-28 18:44
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

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)