VBA Access SQL一括処理 – テックメモ

Excelのシート上に保存してあるデータを関数などでSQL化し、一括でAccessに流し込む必要があったので。

Sub test()

With ActiveSheet

Set cn = CreateObject("adodb.connection")
cn.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\x.mdb"

For r = 1 To 138
  q = ActiveSheet.Cells(r, 1).Value
  cn.Execute q
Next r

cn.Close
Set cn = Nothing

MsgBox "done"

End With

End Sub

Sub test()

With ActiveSheet

Set cn = CreateObject("adodb.connection")

cn.Open "provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.Path & "\x.mdb"

For r = 1 To 138

q = ActiveSheet.Cells(r, 1).Value

cn.Execute q

Next r

cn.Close

Set cn = Nothing

MsgBox "done"

End With

End Sub