テーブルにupdateトリガーを仕込んで更新日時カラムをcurrent_timestampにupdateさせている状態で
update TableName set TargetColumn=case when OtherColumn=’abc’ then ‘D’ else TargetColumn end
で更新すると更新日時カラムは全レコード現在時刻に変更される
更新クエリの前に
Alter table TableName disable trigger TriggerName
クエリの後に
Alter table TableName enable trigger TriggerName
トリガーは楽な反面、仕込んでいるのを忘れてしまう
今日見直すとこの更新クエリ結局全部更新？
where句でupdate の方が断然速い
なんでもcase で条件分岐すれば良いというわけではないらしい
これは時々必要になるのですが
scope_identity() , ident_current(‘Table’) , @@Identity
の３つの方法があるが他のセッションの影響を受けない scope_identity() が推奨と検索すると出てきます。
しかしVBAからADO ADODBでレコードセットを取って
前略
cn.execute “insert into table・・・”
rs.open “select scope_identity() as ID”,cn,adopendynamic,adlockoptimistic
としても取得してくれないのでしかたなく
rs.open “select ident_current(‘Table’) as ID”,cn,adopendynamic,adlockoptimistic
で取得していました
cursorlocationがserverサイドでdynamicを要求した場合だけ取得できないのに気がつきました
ほかの組み合わせは大丈夫のようです
しかしこのレコードセットのcursortypeは何を要求しようと結局forwardonlyに調整されます
テーブルやPK等で自動調節？
ならばなぜdynamic要求の場合だけＸなのでしょう。動的だから？
親フォームに子フォームのレコード数を表示させたい あるいは取得して何かに利用したい
ってことは良くあることと思います。
サブフォームのレコードソースと同じようにレコードセットを取れば取得はできるでしょうけど
フォームでもできるはずだと検索すると
forms!親フォーム!子フォームの名前.form.recorsetclone.recordcount
で出てくるとあるがなぜかだめ
フォームデザインでビルドで入力してもインテリセンスのメンバーにrecordsetもrecordsetcloneも出てこない
VBAで
forms(親フォーム)(子フォーム).form.recordsetclone.recordcount
と書けば取得できるのだが
なぜ？
最近Mysqlさわっていないので忘れている。いやさわってないから忘れるのではなく歳のせいかも。
とにかく
Mysqlのコンソールで
クエリを間違えてばかりで
-> から
mysql> へ戻りたいときのメタコマンドは
\c
カラム名にスペースとか入ってうまくクエリできないときは
select column name from table
はバッククォートの
シングルクォートの ‘ と間違えないように
ちなみにSQLserverでは
select [column name] from table
重複は除外してカウント、重複あっても全カウント 両方出したいとき
SQLserver、MySQLなら
select GroupColumn,Count(distinct TargetColumn),Count(TargetColumn) from Table group by GroupColumn
と簡単に出る
ACCESSでは？
このクエリはACCESSではできないようなのでいろいろ試したが今のところクエリ一文ではできていない
一度クエリでカウントして保存してさらにこれをソースにカウントしたカラムをカウントする のイメージで….
select GroupColumn as Group1,Count(TargetColumn) as Count1 from Table group by GroupColumn,TargetColumn
これをQ_1で保存して
select Group1,Count(Count1) as CountDistinct from Q_1 group by Group1
これでdistinct countができる
もう一つ全カウント
select GroupColumn as Group1,Count(TargetColumn) as CounAll from Table group by GroupColumn
のクエリQ_2を保存して
Q_1、Q_2 の連結クエリを作成する の２段構え
もっといい方法があるのかもしれないが今のところこれしか思いつかない
バックエンドがSQLserverやMySQLならACCESSから一番上のパススルークエリの方が楽かな？
before Sqlserver2017
select
replace((select Column as [data()] from Table for xml path()),’ ‘,’,’) as CSVcolumn
Sqlserver2017
select
string_agg(Column,’,’) as CSVcolumn from Table
before Sqlserver2017
select
Z.PKcolumn
,Z.columnA
,Z.columnB
,row_number() over (order by Z.columnB asc) Seq
,replace((select Z.columnD as [data()] from Table where Z.PKcolumn=PKcolumn for xml path(”)),’ ‘,’,’) Member
from Table as Z
group by PKcolumn,columnA,columnB
Sqlserver2017
select
Z.PKcolumn
,Z.columnA
,Z.columnB
,row_number() over (order by Z.columnB asc) Seq
,string_agg(Z.columnD,’,’) Member
from Table as Z
group by PKcolumn,columnA,columnB
Null でよく勘違いする
SQLserverテーブルでもMYSQLテーブルでも 数値型のカラムにnullを入れることはできる
テーブル設計でnullを許可にすれば良いだけの話
変数にnullを代入するのは不可能な場合もある
VBAでは
dim Var as long
var=null
は不可能、stringはOK stringもだめです
variantだけ可能
ではSQLserverでも同じだろうと思ったら
declare @Var int
set @Var=null
select @Var
とクエリを発行したらNullが返ってきた
では宣言のみで代入なしは？（=初期値？）
SQLserver
declare @Var int
select @Var
は Null
nvarcharでも同様
VBAは複雑
dim Var as long
debug.print Var
はなんと 0
dim Var as string
debug.print Var
は 空白
調べるとこれNullではなく ”” つまり空文字列
役立ちそうにない知識でしょうが一応書き留めておきます
今日 2018-10-19 の日付で
select case when current_timestamp>’2018-10-18′ then ‘true’ else ‘false’ end;
はSQLserverでもMYSQLでもtrue
select case when current_timestamp>’2018-10-20′ then ‘true’ else ‘false’ end;
はSQLserverでもMYSQLでもfalse
でもVBAでは
debug.print iif(now>”2018-10-18”,true,false)
はfalseとなってしまう
debug.print iif(now>cdate(“2018-10-18”),true,false)
としないとだめ
日付は型宣言や型変換するようにしないといけないですかね
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をチェック
