ACCESS+ADO+SQL SERVER:Recordset记录集如何保存到本地表

lili　发表于：2013-10-12 11:33:19 　

在ACCESS中用ADO连接了SQL数据库建立了一个Recordset记录集，如何将这个记录集的数据保存到ACCESS本地表？

Private Sub 确定_Click()
    Dim oleconn As New ADODB.Connection
    Dim CONN As String
    CONN = "Provider=SQLOLEDB;Persist Security Info=False;Data Source=HRSERVER;Initial Catalog=Txcard;User ID=sa"
    oleconn.Open CONN

    Dim STRSQL As String
    Dim RST As New ADODB.Recordset
    STRSQL = "SELECT * FROM Kq_Source WHERE (EmpID = 4142) AND (FDateTime = CONVERT(DATETIME, '2013-10-08 08:33:00', 102)) "
    RST.Open STRSQL, oleconn, adOpenKeyset, adLockOptimistic

    '下面是我琢磨的将记录集插入到本地表"表1"的代码，不过不行，请大家帮我看下应该怎样写

    Dim stemp As String
    stemp2 = "INSERT INTO 表1 ( ID,FDatetime ) RST.Fields('id').Value,RST.Fields('FDatetime').Value;"
    DoCmd.RunSQL stemp2

    RST.Close
    Set RST = Nothing

End Sub

Top

smileyoufu 发表于：2013-10-12 22:24:08

再用ADO打开一个本地表，然后再将远程记录集，写入到本地表就是了啥。

Top

smileyoufu 发表于：2013-10-12 22:43:11

Option Compare Database

'把Access表的数据导入sql里（需要引用ADO）：

Private Sub Command1_Click()
Dim cnAccess As New ADODB.Connection, cnSql As New ADODB.Connection, rsAccess As New ADODB.Recordset, rsSql As New ADODB.Recordset

    '打开Access数据库的连接，具体的需要改一下
    cnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Test.mdb;Persist Security Info=False"
    rsAccess.CursorLocation = adUseClient

'获取Access里的Table1的你需要上传的字段的记录，准备导出入sql
rsAccess.Open "select 字段1,字段2,字段3,字段...... from table1", cnAccess, adOpenDynamic, adLockReadOnly

   '连接sql
    cnSql.Open "Provider=SQLOLEDB.1;Password=密码;Persist Security Info=False;User ID=登录名;Initial Catalog=sql数据库;Data Source=远程IP"
    rsSql.CursorLocation = adUseClient
    '打开sql的Table1表，准备导入数据
    rsSql.Open "select * from Table1", cnSql, adOpenDynamic, adLockPessimistic

    rsAccess.MoveFirst
    While Not rsAccess.EOF
        rsSql.AddNew
        '给sql的记录集赋值
        rsSql("某字段1") = rsAccess("字段1")
        rsSql("某字段2") = rsAccess("字段2")
        rsSql("某字段3") = rsAccess("字段3")
        '......
        rsAccess.MoveNext
    Wend
    rsSql.UpdateBatch '批量更新记录集

    Set rsAccess = Nothing
    Set rsSql = Nothing
    cnAccess.Close
    Set cnAccess = Nothing
    cnSql.Close
    Set cnSql = Nothing

End Sub

'这是本地写入远程的代码，你照着修改一下，修改成远程写入本地（表相应的代码修改一下）即可。

Top

lili 发表于：2013-10-22 16:07:42

谢谢，经过验证非常好用，正是我需要的。

Top

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

Access交流中心

[5分]ACCESS+ADO+SQL SERVER:Recordset记录集如何保存到本地表