Accessのサブフォームのカウント

親フォームに子フォームのレコード数を表示させたい あるいは取得して何かに利用したい
ってことは良くあることと思います。

サブフォームのレコードソースと同じようにレコードセットを取れば取得はできるでしょうけど
フォームでもできるはずだと検索すると
forms!親フォーム!子フォームの名前.form.recorsetclone.recordcount
で出てくるとあるがなぜかだめ
フォームデザインでビルドで入力してもインテリセンスのメンバーにrecordsetもrecordsetcloneも出てこない

VBAで
forms(親フォーム)(子フォーム).form.recordsetclone.recordcount
と書けば取得できるのだが

なぜ?

集計クエリ カウント

重複は除外してカウント、重複あっても全カウント 両方出したいとき
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 の連結クエリを作成する  の2段構え
もっといい方法があるのかもしれないが今のところこれしか思いつかない

バックエンドがSQLserverやMySQLならACCESSから一番上のパススルークエリの方が楽かな?

Accessのクエリでダイナセット(矛盾を許す)は要注意

select TableA.columnA(主キー),関数(tableB.columnB) from TableA innner join TableB on (tableA.columnA=tableB.No)

とかクエリを元にフォーム作成
関数によってはクエリが参照のみになることがあるらしい
でもどうしても更新削除がしたい
ということでこのクエリをダイナセット(矛盾を許す)にして強引に可能にしてしまう

更新は問題ないと思うがフォームからレコード削除の時 
普通のダイナセットのクエリならクエリに主キーが入っていないTableBのレコードは削除されないのだが
ダイナセット(矛盾を許す)の場合はtableAもTableBもクエリに入っているレコードは削除されてしまう

これで1日棒に振りました

一時テーブルが消えるその前に

SQLserverの#テーブルは消えてしまうものですが
消えてしまう前になんとかフロントエンドのAccessに保存してしまう方法

SQLserver側で ストアド dbo.test
たとえば

select * into #table from other table
中略
–ストアドの最後に
select * from #table
end

Access側で
パススルークエリ,sqlは
dbo.test
これだけ書いて保存 Qtest

さらにテーブル作成クエリでQtestをソースにしてAccessのローカルテーブルに保存する

SQLserverの一時テーブルへの接続は競合しないのか?

答えはしないです。
フロントAccessでDBアプリを開発中ですが不安になりました。
レコードセット取らずにcn.executeで直接#テーブルいじってますので・・
クライアント2台で試してみました。

cn.Execute “drop table if exists #no123”
cn.Execute “select カラム into #no123 from テーブル where カラム like ‘%ほにゃらら%'”
rs.Open “select * from #no123”, cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
Do Until rs.EOF
MsgBox rs!カラム
rs.MoveNext
Loop

一台で操作途中でもう一台から同操作
競合するならdropされるはずですが大丈夫でした。
このときSQLserverはどうしているのかというと

一時テーブルが2つ出来ていました。
これは便利です

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のテーブルをイメージしたらだめ・・なのか?

SQLserver とクライアントとODBC

ACCESSフロントエンド SQLserverバックエンドDBでDBアプリが開発できんかなあと奮闘中
形だけ整ってきたのでアプリが膨れ上がる前にローカルで開発したものをクライアントでも
動かすと・・
まず戸惑ったのがODBC 
SQL Server ODBC,ODBC Driver 13 for SQLserver,SQL server native client 
3択になってる?
Mysqlは少しだけかじったけど一択なんだけど・・
調べると再頒布ならSQL server native clientが良さげとGoogle先生がおっしゃるが
なぜか競合エラー 他のユーザーが同時に更新を・・で挫折
やむなく、とりあえず、SQL serverを選択 こちらでは何故か起きない
windows認証でSQLserverログインの場合
どのコネクターでもキモはクライアントのユーザーをSQL serverの
ローカルユーザーに同じ名前とPWで登録してかつSQL serverにも
登録して権限を与える。なんか矛盾してるような気もするが
これでクライアントからも接続できた。
accdeならコネクターの名前は開発元と同じ名前にする。これもキモ。

SQL server native clientは宿題とする

しかしOSは64bitだけどACCESSが32bitなのでコネクターも32ビット
なんですよね。
VBAでクエリ文書いているときも今ACCESS側のクエリなのか
SQL側のクエリなのかも時々混乱します。
まるで北斗琉拳の魔闘気の中にいるように自分の立ち位置がわからなくなります。
暗琉天破、受けたことはないですが・・。