Access (VBA)

Access VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Access 2013)
更新クエリがうまくいかない
投稿日時: 21/10/26 15:50:56
投稿者: yamasho

お世話になります。
 
都道府県や市町村などのフィールドがあります。
それらすべてを格納する「フル住所」というフィールドを作りました。
 
更新クエリでフル住所にセットしようと思ったのですが更新されません。
エラーメッセージもなく、どこが間違っているのかもわかりません。
 
VBAは以下の通りです。
 
strSQL = "UPDATE T_顧客マスタ SET [フル住所] = [都道府県] & [市町村] & [住所]"
cn.Execute strSQL
 
フィールドを結合して他にフィールドにセットすることはできないのでしょうか?

投稿日時: 21/10/26 16:13:11
投稿者: yamasho

補足です。
 
バックエンドはSQL Serverで、SQL Serverのテーブルをリンクテーブルで接続しています。
そのリンクテーブルに対する更新クエリになります。
 

回答
投稿日時: 21/10/26 16:15:12
投稿者: hatena
投稿者のウェブサイトに移動

SQL自体には問題いなので、提示されていない部分に問題がありそうです。
 
実行したときに、エラーとかでてませんか。
とりあえず、cnの宣言部分を含めたコード全体を提示してもらえませんか。
 

回答
投稿日時: 21/10/27 10:29:38
投稿者: Suzu

VBAではなく、更新クエリを作り 手動で試してみては?

投稿日時: 21/10/27 22:25:50
投稿者: yamasho

お世話になります。
 
エラーは出ません。
コードは次の通りです。
 
 
Private Sub Form_Open(Cancel As Integer)
     
    Set cn = CurrentProject.Connection
     
    cn.Execute "UPDATE T_顧客マスタ SET フル住所 = 都道府県 & 市町村 & 住所"
     
    MsgBox "ok"
 
End Sub
 
 
更新クエリを作り、手動で試したところ、
更新することができました。
 
フル住所にすべて0をセットして、
VBAを実行してみました。
 
0がなくなり、空白になることが判明しました。
しかし全部空白になるのではなく、
10分の1くらいは思ったような結果になります。
 
クエリで手動だと思うような結果になり、
VBAだと全部ではないが大部分が空白になることが分かりました。
 

回答
投稿日時: 21/10/27 22:57:02
投稿者: hatena
投稿者のウェブサイトに移動

クエリでできるなら、下記でどうだろう。
 

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE T_顧客マスタ SET フル住所 = 都道府県 & 市町村 & 住所"
DoCmd.SetWarnings True

 
あるいは、DAOでやってみるとか。
 
CurrentDB,Excute "UPDATE T_顧客マスタ SET フル住所 = 都道府県 & 市町村 & 住所"

投稿日時: 21/10/28 08:40:33
投稿者: yamasho

お世話になります。
 
まず、DoCmd.RunSQLで実行したところ、思うような結果になりました。
CurrentDB,Excuteだと構文エラーになってしまい検証できませんでしたが、
 
strSQL = "UPDATE T_顧客マスタ SET [フル住所] = [都道府県] & [市町村] & [住所]"
cn.Execute strSQL
 
だと空白になるフィールドがたくさん出るということが分かりました。
SQL Serverのテーブルに対してではなく、
ローカルのテーブルで実行する分には、
思うような結果になります。
 
解決はしたのですが今後のために、
SQL文を実行するときは、
 
strSQL = "UPDATE T_顧客マスタ SET [フル住所] = [都道府県] & [市町村] & [住所]"
cn.Execute strSQL
 
という命令ではなく、
 
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE T_顧客マスタ SET フル住所 = 都道府県 & 市町村 & 住所"
DoCmd.SetWarnings True
 
という命令にした方がいいでしょうか?
 
どちらが一般的なのでしょうか?
 

回答
投稿日時: 21/10/28 09:54:22
投稿者: Suzu

引用:
まず、DoCmd.RunSQLで実行したところ、思うような結果になりました。
CurrentDB,Excuteだと構文エラーになってしまい検証できませんでしたが、

 
CurrentDB,Excute
ではなく
CurrentDB.Excute
 
にして、テストしてみてください。
 
 
 
空白になる原因について、すぐには思いつきません。。
今回のSQLについて、特に問題になりそうな事も思いつきません。
あるとすれば、レコードロックの関係でしょうか。
 
 
 
引用:
SQL文を実行するときは、
 
strSQL = "UPDATE T_顧客マスタ SET [フル住所] = [都道府県] & [市町村] & [住所]"
cn.Execute strSQL
 
という命令ではなく、
 
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE T_顧客マスタ SET フル住所 = 都道府県 & 市町村 & 住所"
DoCmd.SetWarnings True
 
という命令にした方がいいでしょうか?

 
 
 
接続について
Access 内に、SQLServerのリンクテーブル を持つのは、
 
SQLServer - 接続仲介技術 - Access ← 操作
の様な形で接続します。
 
Docmd.RunSQL の場合、ほぼ上記の流れになります。
 
 
CurrntProject.Excute / Currentdb.Execute
は、 それぞれ、ADO/DAO を介し操作します。
 
SQLServer - 接続仲介技術 - Access - ADO/DAO ← 操作 
 
それぞれ良いところ悪いところがあるので、一概に 良い/悪い は言えません。
 
ただ、覚えておきたいのは、
Docmd.RunSQL 、CurrntProject.Excute / Currentdb.Execute
 
は、どれも、クライアント処理です。
 
対象のレコードの情報を、いちど全てAccess側に持ってきて、編集を行い、SQLServerに返します。
「もってくる」は通常、ネットワークを介する事が多いです。
 
SQLServer に対しての処理では、
SQL文を 投げ、その SQL をSQLServer側で処理する事が可能です。
この方法だと、ネットワーク中 の 実データのやり取りはありません。
(SELECT文を投げれば、そのSELECTの内容が流れる事にはなりますが)
その分、高速に動作します。
 
それを実行するのは、RunSQLではなく、
パススルークエリとして保存してあるクエリをOpenQuery にて、開く事で可能です。
 
パススルー クエリを作成する
https://support.microsoft.com/ja-jp/office/%E3%83%91%E3%82%B9%E3%82%B9%E3%83%AB%E3%83%BC-%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-b775ac23-8a6b-49b2-82e2-6dac62532a42
 
(ただし、SQL の構文は、Accessにて使用する JET-SQL ではなく
  SQLServeの T-SQL の構文にする必要があります。)
 
 
あるいは、ADO/DAO でも CurrntProject.Execute/Currentdb.Execute では
Access から SQLServer の接続を流用します。
そうではなく 自前で、接続文字列を用意し サーバサード接続を行うことで
SQL を投げ、サーバ側で処理させる事が可能です。

回答
投稿日時: 21/10/28 16:47:30
投稿者: sk

引用:
バックエンドはSQL Serverで、SQL Serverのテーブルをリンクテーブルで接続しています。
そのリンクテーブルに対する更新クエリになります。

引用:
Set cn = CurrentProject.Connection
 
cn.Execute "UPDATE T_顧客マスタ SET フル住所 = 都道府県 & 市町村 & 住所"

引用:
フル住所にすべて0をセットして、
VBAを実行してみました。
 
0がなくなり、空白になることが判明しました。

[都道府県], [市町村], [住所]のいずれかの値が
(空文字列ではなく) Null だからでは。
 
引用:
しかし全部空白になるのではなく、
10分の1くらいは思ったような結果になります

それらが[都道府県], [市町村], [住所]のどの値も
Null ではないレコードだからでは。
 
もしそうなら、恐らく ADO を介して UPDATE 文を実行した場合は、
SQL Server( Transact-SQL )における文字列連結のルール
Null と文字列連結した結果は Null である)通りの結果で
[フル住所]の値が更新されているのでしょう。
 
引用:
CurrentDB.Execute

DAO.Database オブジェクトの Execute メソッドなら
期待通りの結果になるはず。

投稿日時: 21/10/28 22:44:11
投稿者: yamasho

お世話になります。
 
SK様
 
おっしゃる通り、NULLが入っているものが、
フル住所がNULLになっているようです。
 
DAO.Database オブジェクトの Execute メソッドなら
期待通りの結果になりました。
 
Suzu様
 
パススルークエリの件、ありがとうございます。
トライしてみます。
 
最初の質問に関しては解決しましたので、
いったん解決とさせていただきます。
 
皆様、ありがとうございました。