ＳＱＬ文で他のBookのデータを更新する。

目次
　 １　閲覧対象者
　 ２　得られる効果
　 ３　設計
　 ４　感想　

１　閲覧対象者

データを更新するエクセルBookと、データを蓄積するBookを別けたい方（いわゆるDBファーストです）。
更新用Bookからベタ打ちのSQL文を発行するだけなので、ADO接続、SQL文の書き方がよくわからない初心者の方。

２　得られる効果

１）　更新用Bookとデータを蓄積するBookを別けることでデータの管理が容易となる。
２）　ADO接続の方法が分かる。
３）　簡単なSQL文（Update）の使用方法がわかる。

３　設計

以下の画像のように、Bookを２つ準備します。

DB.xlsmの中身

「氏名」、「カード番号」の２行だけです。クレジットカードの番号管理的なイメージで！

使い方

参照設定

更新用BookにADO接続を使用するために、以下の画像の蛍光線部の参照設定をします。

DB更新用.xlsmを開きます。このBookの中に以下のコードを記述しています。

Const cnsProvider = "Microsoft.ACE.OLEDB.12.0"
Const cnsExprop = "Extended properties"
Const cnsExcel = "Excel 12.0"
Const cnsDBname = "DB.xlsm"
Const cnsYen = "/"

Sub Update_SQL()

Dim upSQL As String


'Connecionの作成
Set DBcon = New ADODB.Connection
With DBcon
    .Provider = cnsProvider
    .Properties(cnsExprop) = cnsExcel
    .Open ThisWorkbook.Path & "\" & cnsDBname
End With


'SQL文作成 (セルに付けた名前がテーブルとなる)
upSQL = "update [name$] SET 氏名 = '神田　えの' where カード番号 = '2'"


'DBの更新
DBcon.Execute (upSQL)


'終了。オブジェクトの参照破棄
DBcon.Close: Set DBcon = Nothing

End Sub

内容としては、カード番号「２」の氏名を「神田　えの」に変更します。
Update_SQLの実行結果は以下の画像の通り↓↓

更新したいDB.xlsmのPathは「ThisWorkbook.Path & "\" & cnsDBname」で指定しています。
ファイル名の変更をしたい場合は、「Const cnsDBname = "DB.xlsm"」を別のBook名に変更すればＯＫです。
ほかのフォルダを指定したい場合は、「ThisWorkbook.Path」ではなく、指定のファルダPATHを直指定してください。

最初のConstの部分でADO接続の設定をしていますのでそのままコピペでＯＫです。ここはあまり深く考えなくてよいです。

更新用ＳＱＬ文は変数「upSQL 」に作っています。
upSQL = "update [name$] SET 氏名 = '神田　えの' where カード番号 = '2'"
このＳＱＬ文を動作させるためには、DB.xlsmのSheet名を「name」にしておく必要があります。

「DBcon.Execute (upSQL)」でSQL文を実行します。これで「神田　えの」に変更されます。

４　感想

Update文を作成でエラーが出ていて２時間も格闘していました(;^ω^)。原因は、「氏名　=」この空白部分が全角空白でエラーになってました。
今回は、SQL文を直指定していますので実用性はありません。SQL文を条件で作成して更新すると実用的になりますね。
その辺は、次回の記事を作ります。

１つわからなかったことがありました。私はExcel2019で作成しています。なので、Versionは「Excel 16.0」になるのですが、「Const cnsExcel = "Excel 12.0"」の12.0を16.0にするとエラーになります。ここだけなぜかわかりませんでした。わかる方は教えていただけると喜びますm(_ _"m)
｛Twitterで教えていただいた方によれば、エクセルのversionではなく検索エンジンのversionらしいです。自分でも調べてみますm(_ _"m)｝

ではでは、この辺で(^^)/~~~