Access で Oracle の特定のスキーマのテーブルを一括リンクするてけとーな VBA

Const dsn = "orcl"   ' dsn
Const user = "SCOTT" ' user
Const pass = "tiger" ' pass

Sub import()

    Dim conn          As Object
    Dim rec           As Object
    Dim tableName     As String
    Dim strSQL        As String
    
    DoCmd.SetWarnings False
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "DSN=" & dsn & ";UID=" & user & ";PWD=" & pass & ";"
            
    strSQL = "SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME"
    Set rec = conn.Execute(strSQL)
    Do While Not rec.EOF
        tableName = rec.Fields("table_name").Value
        On Error Resume Next
        DoCmd.DeleteObject acTable, tableName
        On Error GoTo 0
        DoCmd.TransferDatabase acLink, "ODBC Database", _
            "ODBC; DSN=" & dsn & ";UID=" & user & ";PWD=" & pass & ";", _
            acTable, user & "." & tableName, tableName, False
        rec.MoveNext
    Loop
    
    conn.Close
    Set rec = Nothing
    Set conn = Nothing

End Sub

関連

• Access + Oracle Instant Client で Oracle Database に接続する - ablog
• Access で Oracle の特定のスキーマのテーブルを一括インポートするてけとーな VBA - ablog