excelからmysqlの更新・登録・削除の操作

質問

EXCELのワークシートにマクロを登録したボタンを設置して、
mysqlのDBのレコードを更新・登録・削除する事は可能でしょうか？

SQLとかVBAの勉強も兼ねてテスト的に作ってみたいなと思っています。
上手くいけば仕事でも使えればとも思っております。

現在phpmyadminで作成したmydbがあります。
testというテーブルにid,商品名,価格,在庫というようなレコードがあります。

ODBCドライバ等の設定をして、EXCELの外部データの取り込みで、データを取り込む事は
できました。

この取り込んだデータを編集したり、新たに新しい商品を登録して、データベースを更新
できるようにしたいのですが、可能でしょうか？

VBAのサンプルコードなどあればご教示ねがいます。

因みに私のレベルはVBAもmysqlも素人なので、できれば解説などもしていただければ
尚助かります。
よろしくお願い致します。

回答No.2

hallo-2007 · ベストアンサー

あれま
.Update
を
.MoveNext

ではいかがでしょうか？
データの抽出は同様のコードでできましたでしょうか？

オブジェクトが開いている場合は、操作は許可されません
を　そのまま理解すると　ほかのユーザーがデータベースを開いている　いうことになりますが。
ほかのユーザーがデータベースを開いていても抽出はできるはずです。

とりあえず
On Error GoTo Err
の行を無効にして　どこで　エラーになっているか確認されてはいかがでしょうか？
もし
rs.Open Sql, con, adOpenStatic, adLockOptimistic
あるいは
.Update
あたりでは？
同じ状況が確認できる環境ではありませんので、確実な回答ができません。

お礼

ありがとうございます。

.MoveNext
を試してみましたがダメでした。

On Error GoTo Err
をコメントアウトしてエラーを確認してみるとご指摘通り

rs.Open Sql, con, adOpenStatic, adLockOptimistic
の行がエラーになっていました。

試しにコメントアウトして
'rs.Open Sql, con, adOpenStatic, adLockOptimistic

にしてみると、ちょっとだけ先に進むのですが、
!ID = Cells(i, 1).Value

のところでエラーになり、

現在の Recordset は更新をサポートしていません。
プロバイダか、選択されたロックタイプの限界の可能性があります。

というメッセージになります。

ロックタイプを指定してみたらいいのかなと思い
con.Open connectionString

のところに追記で
con.Open connectionString, adOpenStatic, adLockOptimistic

という風にしてみると
access denied for user ''@'localhost' using password yes

と、パスワードが違いますよというようなメッセージになってしまいます。

お手上げでしょうか。。。？

回答No.4

hallo-2007

別案ですが
SQL文でできるかもしれません。
con.Execute ("Update costomer Set 氏名='山田' Where 会員番号='1000'")
とかで実行してみてはいかがでしょうか？

For i = 2 To Cells(1, 1).CurrentRegion.Rows.Count
!会員番号 = Cells(i, 1).Value
!氏名 = Cells(i, 2).Value
!電話番号 = Cells(i, 3).Value
!住所 = Cells(i, 4).Value
.Update
Next i
の部分を
For i = 2 To Cells(1, 1).CurrentRegion.Rows.Count
NyID=Cells(i, 1).Value
MeMess= Cells(i, 2).Value
con.Execute ("Update costomer Set 氏名='" & MyMess & "' Where 会員番号='" & MyID & "'")
MyMess = Cells(i, 3).Value
con.Execute ("Update costomer Set 電話番号='" & MyMess & "' Where 会員番号='" & MyID & "'")
MyMess = Cells(i, 4).Value
con.Execute ("Update costomer Set 住所='" & MyMess & "' Where 会員番号='" & MyID & "'")
Next i
といった感じでしょうか？
重複しない会員番号があって、会員番号は変更しないという条件になりますが。
SQL文に強い人であればほかの書き方もあると思います。
ちなみにSQL　Serverでは　更新できました。
ちなみに　追加は　Insert　削除は　Delete で可能です・

回答No.3

hallo-2007

難しそうですね。
できないことはないと思いますが
MySqlについての情報って調べてみましたが私も該当するような
サイトが見つかりませんでした。
ちなみにAccessではできています。
でも仮にできたとしても追加と削除は運用であまり気にしたくても
大丈夫でしょうが、
データの内容を更新するとしたら、抽出したデータと更新する内容が
常に一致する条件が必要ですので複数のユーザーでひとつのデータベースを
活用する場合は注意が必要です。
でも
同じような方法でデータの抽出はできているのですよね。
近いところまで来ていると思います。
ちょっとほかのサイトで質問してはいかがでしょうか。
結果については、私もちょっと興味があります。

http://www.moug.net/
などではいかがでしょうか？
お力になることができずごめんなさいです。

回答No.1

hallo-2007

現在のコードを提示してもらうと回答が付きやすいかと思います。
たぶん、今は
http://www.cocoaliz.com/excelVBA/index/40/
で紹介されているような　Recoredsetを使って　データベースの内容を
エクセルに抽出、表示できた所だと想定して回答いたします。
ちょっと検索しましたがmysplの場合の

>この取り込んだデータを編集したり、新たに新しい商品を登録して、データベースを更新
について見かけませんでしたので　Accessの場合ですが
データを新しく登録する場合の例がありました。
http://www.moug.net/tech/exvba/0150040.htm

この取り込んだデータをエクセルで編集し、データベースの内容を置き換える場合は
上記のサイトの
.AddNew
!伝票番号 = Cells(i, 1).Value
!日付 = Cells(i, 2).Value
!コード = Cells(i, 3).Value
!得意先 = Cells(i, 4).Value
!金額 = Cells(i, 5).Value
.Update
の .AddNew　を抜いたコードで考えてみてください。
修正の場合は、エクセルに抽出したデータが編集中にほかのユーザーで変更されないことなど
注意して使うことになります。
エクセルのシートの上には
抽出のボタンと抽出した内容を編集後に、データベースの内容を置き換えるボタンをおいて活用しています。

参考までに
http://www.moug.net/tech/exvba/0150041.htm
データを削除する場合のサンプルがあります。

お礼

ご回答いただき、ありがとうございます。

早速
http://www.moug.net/tech/exvba/0150040.htm
の分のサンプルコードを参考にして、下記のような形にしました。

Sub 追加()
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim connectionString As String
Dim sqlStr As String

'接続文字列
connectionString = "Driver={MySQL ODBC 5.1 DRIVER};" _
& " SERVER=localhost;" _
& " DATABASE=torys;" _
& " USER=root;" _
& " PASSWORD=;"

'ADODB.Connection生成
Set con = New ADODB.Connection
On Error GoTo Err

'MySQLに接続
con.Open connectionString

'SQL文
sqlStr = "select * from customer"

'SQL文実行
Set rs = con.Execute(sqlStr)
rs.Open Sql, con, adOpenStatic, adLockOptimistic
With rs 'シートの項目行（1行目）を除いてデータ行数分ループ
For i = 2 To Cells(1, 1).CurrentRegion.Rows.Count
!会員番号 = Cells(i, 1).Value
!氏名 = Cells(i, 2).Value
!電話番号 = Cells(i, 3).Value
!住所 = Cells(i, 4).Value
.Update
Next i
End With

'クローズ
con.Close
Set rs = Nothing
Set con = Nothing
Exit Sub
Err:
Set rs = Nothing
Set con = Nothing
MsgBox (Err.Description)
End Sub

すると、
「オブジェクトが開いている場合は、操作は許可されません」
というメッセージが表示されます。

どこが問題でしょうか…

どんくさい質問で申し訳ありません。