accessのODBC接続でSQLサーバからデータ取得するときに、SQLサーバの参照の度にログイン画面がでてくのは、ウザイ。VBAでログイン処理を書いて自動化したい。つまり、ログイン画面をポップアップさせずに、VBAでODBCでSQLサーバにログインし、データを取って来たいのだ。
よくあるやりかたとしては、ADOコネクションを生成し、DB接続情報をセット(ODBC)、接続をOpenするというrecordsetでデータを取得するというアプローチで、1レコードずつ処理することになるので、面倒くさい。
Set objConn = CreateObject("ADO.Connection")
objConn.ConnectionString = "DSN=DSN1;UID=user1;PWD=password1;"
objConn.Open
SQLサーバのテーブルをリンクした、選択クエリを作成している場合、SQLサーバのテーブルへアクセスするのにログイン画面がでてくる。
そのログイン画面を出さないためには、Docmdを使った「簡単な」方法があるのだ。
以下のコードを標準モジュールのどこかに記述してやる。
DoCmd.DeleteObject Actable,"dbo_TBL-A"
DoCmd.TransferDatabase acLInk,"ODBC Database", _
"ODBC;DSN=dsn_name;UID=user_id;PWD=pass_wd; _
& "DATABASE= database_name",acTable,"tabl_name","dbo.tbl_name"
このテクニックのミソは、TransferDatabaseでSQLサーバのテーブルへのリンクをログイン画面をださすに接続できる、つまり、自動的にコード内で密かにログインしているところだ。そのためにTransferDatabase acLInkの後ろにDB接続情報を記述している。そこがミソなのだ。ちなみにこの文字列はACCESSのリンクテーブルにカーソルを持っていくと、ヌッと表示されるのだ。試してもらいたい。
そして、SQLのテーブルを参照した選択クエリを実行し、EXCELにエクスポートするという２つの操作を１つのDocmdで実現できてしまうのだ。実に効率がいいね。
Dim file1 As String
Docmd TransferSpreadsheet acExport,acSpreadsheetTypeExcel12Xmi,"SELECT_QUERY_USING_SQLserversTable",file1,True
