vbaからレコードセット取る時カーソルタイプはdynamicが多いのだが
件数rs.recordcountは取得できない。
カーソルタイプのおさらい
dynamicは全機能、もちろんaddnew、delete、更新できる
keysetもだいたい全機能 ただし他のユーザーの削除、追加は見えない、
staticは他のユーザーの追加、更新削除は見えない、追加更新削除できない、移動は自由
forwardonlyもstatic同様だが移動がmovenextのみ
件数取得はstaticとkeysetで可能
adopenkeyset,adlockoptimistic でだいたいＯＫなのかな
とかく日付は難しい
SQLserverでもMySQLでもVBAでも
2018/1/1を文字の様に’2018/1/1’とか”2018/1/1”とか書けば日付として認識してくれる
と思っていたけど日付の比較で・・
SQLserverで
select iif(‘2018/1/4’>’2018-1-1′,’true’,’false’)
はtrueと思っていたらfalseを返します。文字と認識？
もちろん
declare @ParamS datetime,@ParamE datetime
select @paramS=’2018-1-1′
select @ParamE=’2018/1/31′
select iif(‘2018/1/4′ between @paramS and @paramE,’true’,’false’)
ときちんと宣言代入すればtrueです。
あるいは
select iif(‘2018/1/4’>cast(‘2018-1-1′ as datetime),’true’,’false’)
はtrueです。
片方だけでも日付型にしてあげるともう一方も日付と見てくれるようです。
VBAでもMySQLでも同じ挙動でした。
MySQLにiif関数はないのでnullif関数で
select nullif(‘2018-1-4′,’2018/1/4’);
select nullif(‘2018-1-4’,cast(‘2018/1/4′ as datetime));
で確認
上は別物と見なされ’2018-1-4’
下は同一と見なされnull
です。
Accessパススルークエリ
VBAからクエリの発行は文字列使ってダイレクトなんとかですので
パススルークエリはほとんど使わないのですがAccessレポートの
ソースにsqlserverのテーブルにsql関数など入ったクエリをソースにする必要があり久しぶりに作成。
しかし
パススルークエリはレポートのソースに使えませんと警告
パススルークエリをソースに全選択のAccessクエリを作成してソースにすれば美しくはないですがなんとか可能になります。
接続文字列はどうすれば？と思いましたがプロパティでビルドボタン押してODBC選択すれば自動で入るのですね。
if exists
mysqlでもsqlserverでも
訂正 SQLserverのみ
MySQLはテーブルがあらかじめないとダメでした。
drop table if exists Table
select * into Table from otherT where ・・・
は便利と思うのですが同じことをAccessのローカルテーブルにしようと
VBAでcurrentproject.connection に
cn.execute “drop table if exists Table” とやっても通用しません
しばらく悩みましたが
on error resume next
docmd.setwarnings false
cn.execute “drop table Table”
docmd.setwarnings true
でとりあえずはなんとか
VBA二次元配列
Public Function theSuccessTest()
Dim Str As String
Str = “a,b,c,d,e,f,g”
Dim Ary() As String
Ary = Split(Str, “,”)
Debug.Print UBound(Ary, 1)
Dim Ary2() As String
ReDim Ary2(0, 3)
Ary2(0, 0) = “a”
Ary2(0, 1) = “b”
Ary2(0, 2) = “c”
ReDim Preserve Ary2(1, 3)
Ary2(1, 0) = “d”
Ary2(1, 1) = “e”
Ary2(1, 2) = “f”
Debug.Print UBound(Ary2, 1)
Debug.Print UBound(Ary2, 2)
End Function
こういう風に今までやってきたのだが検索すると
二次元の順番が逆？？ 混乱
Public Function theFailureTest()
Dim Str As String
Str = “a,b,c,d,e,f,g”
Dim Ary() As String
Ary = Split(Str, “,”)
Debug.Print UBound(Ary, 1)
Dim Ary2() As String
ReDim Ary2(3, 0)
Ary2(0, 0) = “a”
Ary2(1, 0) = “b”
Ary2(2, 0) = “c”
ReDim Preserve Ary2(3, 1)
Ary2(0, 1) = “d”
Ary2(1, 1) = “e”
Ary2(2, 1) = “f”
Debug.Print UBound(Ary2, 1)
Debug.Print UBound(Ary2, 2)
End Function
いやいやこっちはなぜかできない
イメージはExcelの経時表
Accessのテーブルをイメージしたらだめ・・なのか？
format
SQLServerでもfromatと以前UPしましたが
馴れているVBAのformatとは大分違っている事に気がつく
今日2017/10/27の日付で
select format(getdate(),’D’) 2017年10月27日
select format(getdate(),’d’) 2017/10/27
select format(getdate(),’m’) 10月27日
select format(getdate(),’M’) 10月27日
select format(getdate(),’y’) 2017年10月
select format(getdate(),’Y’) 2017年10月
select format(getdate(),’DD’) DD
select format(getdate(),’dd’) 27
select format(getdate(),’MM’) 10
select format(getdate(),’mm’) 40(現在時の分）
select format(getdate(),’YY’) YY
select format(getdate(),’yy’) 17
select format(getdate(),’yy/mm/dd’) 17/01/27 01は現在時刻の分
select format(getdate(),’yy/MM/dd’) 17/10/27
最後のはどうにも変
VBAではどうかというと
debug.Print format(now,”D”) 27
debug.Print format(now,”d”) 27
debug.Print format(now,”m”) 10
debug.Print format(now,”M”) 10
debug.Print format(now,”Y”) 300 300はなんだかわからない
debug.Print format(now,”y”) 300
debug.Print format(now,”DD”) 27
debug.Print format(now,”dd”) 27
debug.Print format(now,”mm”) 10
debug.Print format(now,”MM”) 10
debug.Print format(now,”yy”) 17
debug.Print format(now,”YY”) 17
debug.Print format(now,”yy/mm/dd”) 17/10/27
debug.Print format(now,”yy/MM/dd”) 17/10/27
かなり違う
ではVBAで分はというとn
select format(getdate(),’MM’) ＝ debug.Print format(now,”nn”)
ODBC接続MSSQLテーブルに更新クエリは
ODBC接続MySQLテーブルに更新クエリが競合エラー と書いたけれど
バックエンドDBがMSSQLだったら？ SQL Server 2016 をバックエンドにNorthWind.accdbで実験
ODBC設定を開くとMSSQL関係は2つある？よくわからないがSQL Serverを選択
MYSQLのようなReturn matched rows instead of affected rowsのODBC設定が見つからない
MSSQLにはshow processlist はないのでコマンドプロンプトでnetstat | find “ポート” で確認
競合エラーは起きないものの”＊＊＊ where column=(?)” cmd.execute,param 更新クエリ発行では接続が増え続ける。
“＊＊＊ where column=” & param では増えない。
バックエンドはMSSQLに決定して
ODBCとcurrentproject.connectionはやめて接続文字列のdirectなんちゃら接続で進めていくこととする
今日は無題
カラム数が100を超えるようなテーブルを扱う際、あるカラム名が存在するのかどうか確認するのが面倒なので
Public Function isnullColumn(paramColumnName As String, paramTableName As String) As Boolean
なるものを・・バックエンドはMSSQL
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.ConnectionString = “接続文字列”
cn.Open
rs.Open “select column_name from DBの名前.information_schema.columns where table_name='” & paramTableName & “‘”, cn
rs.MoveFirst
Do Until rs.EOF
If rs!column_name = paramColumnName Then
isnullColumn = False
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
exit function
End If
rs.MoveNext
Loop
isnullColumn = True
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Function
最初丁寧にexitでループを抜けていたがそれはここではＸ
罠にかけたら即終わるべし・・かな？
VBAでアクションクエリ
バックエンドのデータベースに対してフロントエンドからVBAでアクションクエリを発行するとき
Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Set cn=New ADODB.Connection
Set rs=New ADODB.Recordset
cn.ConnectionString=”接続文字列”
cn.open
rs.open “アクションクエリ文”,cn
のあとに
rs.close と書くとエラーなのです。取っていないレコードセットを閉じるからですが
書かなきゃ通るからまあいいかとしていましたがだめらしいです
Dim cn as ADODB.Connection
Set cn=New ADODB.Connection
cn.ConnectionString=”接続文字列”
cn.open
cn.Execute “アクションクエリ文”
が正解なのか？でしょうね。
inputboxのキャンセルボタンの条件分岐
dim Ipt as Variant
Ipt=inoutbox(“ユーザー名を入力して下さい”)
で未入力でOKボタンを押しても、キャンセルボタンを押しても
Ipt=””
同じ戻り値らしいです。
なのでキャンセルボタンで条件分岐したい場合は
if StrPtr(Ipt)=0 then
らしいです。