Microsoft Access 掲示板

    'TBL1からTBL2へデータをコピー

    '接続1
    'SQL server
    Dim StConnectDB As String

    Dim dCnn As New ADODB.Connection

    dCnn.ConnectionString = "Provider=SQLOLEDB; " & _
                            "Data Source=SALESSV2\ABCINSTANCE; " &
                            "Integrated Security=SSPI;Initial Catalog= 'AAA'"

    dCnn.Open

    Dim RstD As New ADODB.Recordset
    Dim Sql As String
    Dim fDate As Date
    Dim tDate As Date

    fDate = "2021/08/01"
    tDate = "2021/08/31"

    Sql = "select * from TBL1 " &
            "where planDate between  '" & fDate & "'  and  '" & tDate & "' " &
            "order by ID"

    RstD.Open Sql, dCnn, adOpenKeyset, adLockOptimistic

    '接続2
    'Access local

    Dim Cnn As New ADODB.Connection
    Dim Rst0 As New ADODB.Recordset

    Set Cnn = CurrentProject.Connection

    Rst0.Open "TBL2", Cnn, adOpenKeyset, adLockOptimistic

    Set Rst0 = RstD.Clone
    'TBL2に反映させたい

    'while で addnewすれば書き込めるが、　SQLのinsertのように一気に書き込みたい
    'TBL1とTBL2は構造は同じ

    Cnn.Close