[Excel for VBA] ADOによるDatabase接続で情報を更新する。

Warning: Undefined property: stdClass::$Images in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 560

Warning: Attempt to read property "Primary" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 561

Warning: Attempt to read property "Small" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 579

Warning: Attempt to read property "URL" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 580

Warning: Attempt to read property "Width" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 581

Warning: Attempt to read property "Height" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 582

Warning: Attempt to read property "Medium" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 583

Warning: Attempt to read property "URL" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 584

Warning: Attempt to read property "Width" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 585

Warning: Attempt to read property "Height" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 586

Warning: Attempt to read property "Large" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 587

Warning: Attempt to read property "URL" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 588

Warning: Attempt to read property "Width" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 589

Warning: Attempt to read property "Height" on null in /home/porco/blockmodule.com/public_html/wp-content/themes/cocoon-master/lib/shortcodes-amazon.php on line 590

ExcelでユーザーフォームとVBAを使っていろいろとやってます。
昔はＡｃｃｅｓｓプラットフォームのものだったり、簡単なものはVisuaｌ.Studio使うのですが、Excelじゃないと駄目な理由があって、制約があるExcelと使ってます。

ところで、VBAでADOを使用したAccess Database接続で情報の更新（UPDATE）を行いますが下記のようなサンプルコードとなります。

Sample Code

[code lang=”vb”] Sub sample1()
Dim objCn As New ADODB.Connection
Dim strSQL As String

With objCn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0"
.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name
End With

strSQL = ""
strSQL = strSQL & " UPDATE [顧客マスタ2$]"
strSQL = strSQL & " SET"
strSQL = strSQL & " 顧客名 = ‘名前’"
strSQL = strSQL & " WHERE"
strSQL = strSQL & " 顧客番号 = 10001"

objCn.Execute (strSQL)

objCn.Close
Set objCn = Nothing
End Sub
[/code]

別の方法としは以下の様なやり方もあります。

[code lang=”vb”] Dim cn As New ADODB.Connection
Dim db_Path　As String
Dim strSQL As String
db_Path = “C:\Users\Username\Documents\12345.db”
cn.Open (“Provider = Microsoft.Jet.OLEDB.4.0;Data Source=” & db_Path)

strSQL = “”
strSQL = strSQL & ” UPDATE T_UserID”
strSQL = strSQL & ” SET”
strSQL = strSQL & ” Passwords = ‘” & update_pass & “‘”
strSQL = strSQL & ” WHERE”
strSQL = strSQL & ” k_ID = ‘” & login_user & “‘”

cn.Execute (strSQL)

cn.Close
Set cn = Nothing
[/code]

———————————————————————————-

ADOはSQL構文を使用しますので、次のお約束は覚えておいてください。

UPDATE　テーブル名　SET フィールド名＝値１，フィールド名＝値２　WHERE　更新条件

よく間違えるのは。。。

SELECT * FROM t_data WHERE f_id = ’UserID’

f_idというフィールドが’UsereID’という文字列のレコードを全て抽出します。
文字列は、「’（シングルクォーテーション）」で括るのがルールです。