０から
配列の添え字
コンボボックスの comboboxname.Column(i) の i
レコードセットの rds(item)
１から
mid関数の文字位置
instr関数の文字位置
文字列関係は全部1で良いかな？
順次書き足し
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 の連結クエリを作成する の２段構え
もっといい方法があるのかもしれないが今のところこれしか思いつかない
バックエンドがSQLserverやMySQLならACCESSから一番上のパススルークエリの方が楽かな？
Accessのクエリでダイナセット（矛盾を許す）は要注意
select TableA.columnA(主キー),関数(tableB.columnB) from TableA innner join TableB on (tableA.columnA=tableB.No)
とかクエリを元にフォーム作成
関数によってはクエリが参照のみになることがあるらしい
でもどうしても更新削除がしたい
ということでこのクエリをダイナセット(矛盾を許す)にして強引に可能にしてしまう
更新は問題ないと思うがフォームからレコード削除の時
普通のダイナセットのクエリならクエリに主キーが入っていないTableBのレコードは削除されないのだが
ダイナセット(矛盾を許す)の場合はtableAもTableBもクエリに入っているレコードは削除されてしまう
これで１日棒に振りました
一時テーブルが消えるその前に
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で直接#テーブルいじってますので・・
クライアント２台で試してみました。
略
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はどうしているのかというと
一時テーブルが２つ出来ていました。
これは便利です
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
３択になってる？
Mysqlは少しだけかじったけど一択なんだけど・・
調べると再頒布ならSQL server native clientが良さげとGoogle先生がおっしゃるが
なぜか競合エラー 他のユーザーが同時に更新を・・で挫折
やむなく、とりあえず、SQL serverを選択 こちらでは何故か起きない
windows認証でSQLserverログインの場合
どのコネクターでもキモはクライアントのユーザーをSQL serverの
ローカルユーザーに同じ名前とＰＷで登録してかつSQL serverにも
登録して権限を与える。なんか矛盾してるような気もするが
これでクライアントからも接続できた。
accdeならコネクターの名前は開発元と同じ名前にする。これもキモ。
SQL server native clientは宿題とする
しかしOSは64bitだけどACCESSが32bitなのでコネクターも32ビット
なんですよね。
VBAでクエリ文書いているときも今ACCESS側のクエリなのか
SQL側のクエリなのかも時々混乱します。
まるで北斗琉拳の魔闘気の中にいるように自分の立ち位置がわからなくなります。
暗琉天破、受けたことはないですが・・。