SQLServer と Access との 連携

ＳＱＬＳｅｒｖｅｒ上のテーブルをＭＤＢにリンク/インポートする (全テーブル)

接続文字列

lesson001.con

ODBC;DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;

lesson002.con

DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;

テーブル名取得用SQL

lesson002.sql

select * from information_schema.TABLES

リンク/インポート用スクリプト

lesson002.vbs

Option Explicit

'MDB ファイルを 作成
Dim de
Set de = CreateObject("DAO.DBEngine.36")
'Access95   DAO.DBEngine
'Access97   DAO.DBEngine.35
'Access2000 DAO.DBEngine.36

Dim dbLangJapanese: dbLangJapanese = ";LANGID=0x0411;CP=932;COUNTRY=0"

Dim db
Set db = de.CreateDatabase(WScript.Arguments(0), dbLangJapanese, 64)
'Access 2.0  16
'Access 95   32
'Access 2000 64
db.Close

'Access を 起動
Dim accessApp
Set accessApp = CreateObject("Access.Application")
accessApp.Visible = True

'作成した MDB ファイルを 開く
accessApp.OpenCurrentDataBase WScript.Arguments(0)


'接続文字列 読み込み
Private fs
Set fs = CreateObject("Scripting.FileSystemObject")

Private tsCon
Set tsCon = fs.OpenTextFile(WScript.Arguments(1))

Private sConSqlServer
sConSqlServer = ""
If Not tsCon.AtEndOfStream Then
    sConSqlServer = tsCon.ReadLine
End If
tsCon.Close

Set tsCon = fs.OpenTextFile(WScript.Arguments(2))

Private sConAccess
sConAccess = ""
If Not tsCon.AtEndOfStream Then
    sConAccess = tsCon.ReadLine
End If
tsCon.Close
Set tsCon = Nothing

'接続
Private con
Set con = CreateObject("ADODB.Connection")
With con
    .Provider         = "SQLOLEDB"
    .ConnectionString = sConSqlServer
    .Open
End With

'SQL 読み込み
Private tsSql
Set tsSql = fs.OpenTextFile(WScript.Arguments(3))

Private sSql
sSql = ""
Do Until tsSql.AtEndOfStream
    sSql = sSql & tsSql.ReadLine & vbNewLine
Loop
tsSql.Close
Set tsSql = Nothing
Set fs = Nothing

'テーブル一覧取得
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = con
    .CursorType       = 0 'adOpenForwardOnly
    .LockType         = 1 'adLockReadOnly
    .Source           = sSql
    .Open
End With

If Not rs.EOF Then
    Do Until rs.EOF
        Dim tableName: tableName = rs.Fields("TABLE_NAME").Value

        Dim acTable:  acTable  = 0
        Dim acImport: acImport = 0
        Dim acLink:   acLink   = 2

        'テーブル を インポート
        accessApp.DoCmd.TransferDatabase acImport, "ODBC", sConAccess, acTable, tableName, "IMPORT_" & tableName

        'テーブル を リンク
        accessApp.DoCmd.TransferDatabase acLink,   "ODBC", sConAccess, acTable, tableName, "LINK_"   & tableName

        rs.MoveNext
    Loop
End If
rs.Close

'切断
con.Close
Set con = Nothing

accessApp.Quit 
Set accessApp = Nothing

実行形式

C:\>cscript c:\study\vbscript\chapter007\lesson002.vbs c:\study\vbscript\chapter

007\lesson002.mdb c:\study\vbscript\chapter007\lesson002.con c:\study\vbscript\c

hapter007\lesson001.con  c:\study\vbscript\chapter007\lesson002.sql //nologo