Rs.recordcountは件数が多いと遅くてイライラした記憶が。
Dim i as long,MyCount as long
略
rs.movefirst
i=1
do until rs.eof
rs.movenext
i=i+1
Loop
MyCount=i
コードが長くなるけどこちらが速かったような
レコードセット
vbaからレコードセット取る時カーソルタイプはdynamicが多いのだが
件数rs.recordcountは取得できない。
カーソルタイプのおさらい
dynamicは全機能、もちろんaddnew、delete、更新できる
keysetもだいたい全機能 ただし他のユーザーの削除、追加は見えない、
staticは他のユーザーの追加、更新削除は見えない、追加更新削除できない、移動は自由
forwardonlyもstatic同様だが移動がmovenextのみ
件数取得はstaticとkeysetで可能
adopenkeyset,adlockoptimistic でだいたいＯＫなのかな
VBAエディターで
AccessのVBAで
dim A,B,C,D as String
と宣言したら A,B,C,D 全部Stringだと思っていたら
StringになっていたのはDだけで
A,B,CはVariant たぶん
今まではたまたまうまくいっていただけでした。
先ほど気がつきちょっとショック
まだまだ未熟なり
string_agg と string_split
SQLserverでGroup concatみたいな、縦の列のCSVみたいなことは以前書いた様に
for xml path を使ってできていたのですが何度見ても難解で覚えられないクエリでした。
SQLserver2017 からはstring_agg関数で
string_agg(column,’,’) within group (order by Seqcolumｎ asc)
などと簡単にできる様になりました。
これならなんとか覚えられそうです。
これでCSVを作っておいて
select * from string_split(‘CSV’,’,’)
とかやれば配列みたいなこともできそうな・・
ついでに何番目の要素とかも返してくれる関数があるとありがたいのですが今のところなさそうです。
文字列操作でできるとは思いますが何万列とかになると負荷高すぎてだめかな、たぶん。
IDのあるテーブルにinsertすればよさげな気がする。
string_agg は文字列を返しますが
string_split テーブルで返ってきます。
とかく日付は難しい
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
です。
わかったようでわっかってない
１バイト=８ビット
ビットは 0 or 1
半角文字は8ビットで表せるので通称１バイト文字
全角文字は通称２バイト文字
なので半角数字で最大バイト数1
は８ビットだから最大の数は
11111111
2進法なので10進法に換算すると
1×27+1×26+1×25+1×24+1×23+1×22+1×21+1×20
=128+64+32+16+8+4+2+1=255
まじめに書けばこうなるが
11111111の次の数は100000000
十進法では28だから1引いて256-1=255 でもある
そこを踏まえてSQLserverでtinyintは1byteだから
0~255
ということになる
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
でとりあえずはなんとか
sqlserverの和暦
VBAでは和暦は出てくるけどsqlserverは日本の暦は表示できません
年月日ならば
FORMAT ( @d, ‘D’)で表示されるが実はこれ for china?
ということでH29/12/17のように変換するスカラー関数
CREATE FUNCTION iswareki
(
@paramDate datetime
)
RETURNS nvarchar(10)
AS
BEGIN
— Declare the return variable here
DECLARE @ResultVar nvarchar(10),@VarYear int,@VarDate nvarchar(10),@VarDateInt int
select @VarYear=cast(format(@paramDate,’yyyy’) as int)
select @VarDate=format(@paramDate,’MM/dd’)
select @VarDateInt=cast(format(@paramDate,’yyyyMMdd’) as int)
— Add the T-SQL statements to compute the return value here
select @ResultVar=(case
when @VarDateInt>19890107 then
concat(‘H’,format((@varyear-1988),’00’),’/’,@VarDate)–平成
when @VarDateInt>19261224 then
concat(‘S’,format((@varyear-1925),’00’),’/’,@VarDate) –昭和
when @VarDateInt>19120729 then
concat(‘T’,format((@varyear-1911),’00’),’/’,@VarDate) –大正
when @VarDateInt>18680124 then
concat(‘M’,format((@varyear-1867),’00’),’/’,@VarDate) –明治
else ‘0’
end
)
— Return the result of the function
RETURN @ResultVar
END
GO
一括insertできない件
失敗の原因はトリガーでした。
トリガー内の from inserted のinsertedを
レコードと思っていたのが原因です。
よく考えればfromだから次はテーブルですね。
さらにnull値更新のコード簡素化のために関数作ってパラメータに
from insertedを入れ込んだのでますますいけませんでした。
関数をやめて長々とコード書いて最後に
where no in (select no from inserted)
で解決
inserted deleted はテーブルと肝に銘じよう