Access通过ADO访问SQL server，如何将SQL server表中数据抓取到Access对应表中？

Shine　发表于：2017-03-22 13:18:37 　

Dim i%, strCn$, strSQL$, serIP$, uid$, pwd$, dbName$, mydate
Dim cn As Object
Dim rs As Object

    serIP = "10.86.152.100"
    uid = "SHTCSC"
    pwd = "1234567"
    dbName = "Warehouse"

    Set cn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.RecordSet")
    strCn = "Provider=sqloledb;Server=" & serIP & ";Database=" & dbName & ";Uid=" & uid & ";Pwd=" & pwd & "; "

    strSQL = "insert into [Out](Outbound, SalesDoc)values('" + Delivery + "','" + [SOrg.] + "')"

    strSQL = " select * From Z_OBNISS "
    'Out表是access中表，Z_OBNISS是SQL server中表，中间这段不知道该怎么写，求大师协助
    cn.Open strCn
    rs.Open strSQL, cn

    cn.Execute (strSQL)
    DoCmd.RunSQL strSQL

rs.Close
cn.Close

Top

MDZZ 发表于：2017-03-22 14:01:50

可以用记录集来追加

Top

Shine 发表于：2017-03-22 14:39:42

请问怎么用记录集追加？

Top

chinasa 发表于：2017-03-22 15:06:33

rst.Open strSQL, ConnDB, 3, adLockPessimistic

rs.Open "你想写入的表", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
rs.AddNew
rs![ZD1] = rst("ZD1")
rs![ZD2] = rst("ZD2")
rs.Update
rst.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
rst.Close
Set rst = Nothing

Top

总记录：3篇页次：1/1 9 1 :

Access交流中心

Access通过ADO访问SQL server，如何将SQL server表中数据抓取到Access对应表中？