2018年9月 – VBA SQL

VBAでSQLクエリを書くとき

VBAからデータベースへ向かってクエリを書くときいろいろと忖度してあげる必要がある

SQLserverのbit型はaccessで見るとbitの0はAccessの0でありfalse、bitの1はAccessの-1でありtrue
そこを踏まえて
Accessのフォームから・・

cn.execute “update sqlTable set BitColumn=case when ” & AccessForm.checkbox & “=-1 then 1 else ・・

となんだかおかしいようだがおかしくない

日付型を扱う時も

cn.execute “update sqltable set DateColumn=cast(format(日付,’yyyy/MM/dd’) as dattetime)・・・

MMを忖度せねばいけないがAccess側にいると忘れてしまう事がある。気がつけば良いが久しぶりに遭遇してmmと書いてしまうと気がつかないで慌ててしまう

TableAのADODBレコードセットを取ってデータ取得
TableBの複製を一時テーブルにexecuteメソッドでselect into
tableAのデータをパラメータに
update #table・・・

一時テーブルできないとき
レコードセットのcursorlocationがaduseserver　でかつ
レコードセットを閉じないまま
一時テーブル作成
これがエラー

作成前に変数に代入してレコードセット閉じれば可
cursorlocationがaduseclientなら閉じないままでも作成できる

できる時の方が多い
できないときはcursorlocationをチェック

Dim Parm As Long
Param= **
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.ConnectionString = “接続文字列”
cn.Open

cn.Execute “drop table if exists #table”
cn.Execute “select * into #table from Other where column=” & Param
cn.execute “update #table set ・・・”
以下略

シンプルにこれだけならば#テーブルは作成されますが

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = “接続文字列”
Set rs = New ADODB.Recordset
cn.Open

rs.open “SQL文”,cn,・・・
以下略

cn.Execute “drop table if exists #table”
cn.Execute “select * into #table from Other where column=” & Param
cn.execute “update #table set ・・・”
以下略

と複雑になるとオブジェクトは無効ですとエラーです。

なぜかはわかりませんがレコードセットが必要ならprivate sub を別に作って
callにしたらうまくいきます。