AccessVBAでやると意外と難しいAUTO_INCREMENT値の取得
MSAccessからMySQLへの移行で意外と手こずるのが「AUTO_INCREMENT値の取得」です。
11.2.6. ODBC で AUTO_INCREMENT 属性を持つカラムの値を取得する方法
ーーーーーーーーーー
※2018/04/20追記
上記リンク死亡を確認しました。
似た内容を探したので下記参照ください。
8.1.1 Obtaining Auto-Increment Values
23.8.15.3 最後に挿入された行の一意の ID を取得する方法
ーーーーーーーーーー
このリファレンスに書かれている方法で基本的には出来るのですが、AccessVBAを使った使用例がネット上になく、不可能なのではないかしらと思っていたのですが、最終的にはなんとかなりましたので紹介したいと思います。
MS-AccessとSQLの使用環境
PHP等ではこの手の使用例はたくさんでてくるのですが、VBA+ADOでは基本的な操作の使用例がほとんどで、今回の問題についての解答は見当たりませんでした。途方にくれても仕方がないのでなんとか自力で解決方法を模索します。
詳しい環境はこちら、データベースの開発環境に記載してあります。
データ追加後に自動インクリメント値を取得するコード
19行目と32行目に注目してください。
rs、rs2のふたつのレコードセットはcnという同じコネクションを使用します。これが肝心です。
ふたつのレコードセットが同じコネクションを使っていれば、データの追加方法は、ADOのAddNewでも、INSERTでもどちらでも問題なく取得できます。
自動インクリメント値を取得するために、last_insert_id()を使うことはリファレンスにもある通りです。ところが、それをどうやって参照したらいいのかがなかなか判りません。
32行目でレコードセットrs2をウォッチ式で探してみると、FieldsのItem 1にそれらしき値が入っています。これを参照するには、rs2.Fields.Item(0).Valueになります。テーブルのIDはAUTO_INCREMENTにする事も多いですし、Access内では当たり前の様にできていた事もODBC経由だと出来ない事もたくさんあるようです。
今回は回避方法があったので助かりました。
これができないと、フォームをさらに大改造しなければいけませんでしたから。
データ追加後に自動インクリメント値を取得するコード
'----- 変数宣言 -----
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs2 As ADODB.Recordset
'----- 初期値 -----
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset
cn.Open "Driver={MySQL ODBC 5.2 Unicode Driver};" & _
"server=[サーバーのIPアドレス]; " & _
"database=[データベース名]; " & _
"uid=[ユーザー名]; " & _
"pwd=[パスワード];"
'----- start_レコード操作 -----
'レコードオープン
rs.Open "[テーブル名]", cn, adOpenKeyset, adLockOptimistic
'新規追加
rs.AddNew
't_m_customer_customer_idが自動インクリメント型です。
'勝手に数字が割り当てられています。
rs!t_m_customer_tenpo_id = cmd_店舗選択.Value
rs!t_m_customer_syain_id = cmb_社員選択.Value
rs!t_m_customer_customer_shimei = txt_氏名.Value
rs!t_m_customer_customer_kana = txt_カナ.Value
rs.Update
'ここで顧客IDを取得
Set rs2 = cn.Execute("select last_insert_id();")
MsgBox rs2.Fields.Item(0).Value '顧客IDの表示
rs.Close: Set rs = Nothing
rs2.Close: Set rs2 = Nothing
cn.Close: Set cn = Nothing
'----- end_レコード操作 -----
19行目と32行目に注目してください。rs、rs2のふたつのレコードセットはcnという同じコネクションを使用します。これが肝心です。
ふたつのレコードセットが同じコネクションを使っていれば、データの追加方法は、ADOのAddNewでも、INSERTでもどちらでも問題なく取得できます。自動インクリメント値を取得するために、last_insert_id()を使うことはリファレンスにもある通りです。ところが、それをどうやって参照したらいいのかがなかなか判りません。
32行目でレコードセットrs2をウォッチ式で探してみると、FieldsのItem 1にそれらしき値が入っています。これを参照するには、rs2.Fields.Item(0).Value になります。テーブルのIDはAUTO_INCREMENTにする事も多いですし、Access内では当たり前の様にできていた事もODBC経由だと出来ない事もたくさんあるようです。
今回は回避方法があったので助かりました。
これができないと、フォームをさらに大改造しなければいけませんでしたから。