エクセルでアクセスのクエリを更新する方法と注意点

このQ&Aのポイント
  • エクセルでアクセスのデータを編集する方法を勉強しています。クエリをエクセルで呼び出すまでは成功しましたが、戻すときにエラーが出ています。解決方法や参考書籍・サイトを教えてください。
  • エクセルでアクセスのクエリを更新する方法について困っています。実行するとSQLステートメントのエラーが出てしまい、解決策が見つかりません。知識のある方や参考になる情報を教えてください。
  • エクセルでアクセスのデータを編集する方法を勉強していますが、クエリの更新でエラーが出てしまいます。解決方法や参考になる書籍・サイトを教えていただけないでしょうか?
回答を見る
  • ベストアンサー

エクセルでアクセスのクエリを更新したい

お世話になります。 アクセスのデータをエクセルで編集する方法を勉強しています。 アクセスのクエリをエクセルで呼び出すところまで成功しました。 ところが、クエリを読み込んだ後、アクセスのクエリに戻すところで煮詰まっています。 ************************************************************** Sub btn更新_Click() Dim DelCmd As String Dim z As Long Dim i As Long Dim cn As Connection Dim rs As Recordset Set cn = New Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=C:\Excel\Sample.mdb" cn.Open DelCmd = "DELETE * FROM クエリA" cn.Execute DelCmd If Range("A1").Value = "" Then z = 0 Else z = Range("A1").End(xlDown).Row End If Set rs = New Recordset rs.Open "クエリA", cn, adOpenKeyset, adLockOptimistic For i = 1 To z rs.AddNew rs!番号 = Range("A" & i).Value rs!日付 = Range("B" & i).Value rs!担当者 = Range("C" & i).Value rs!顧客名 = Range("D" & i).Value rs!内容 = Range("E" & i).Value rs.Update Next MsgBox "更新しました" '後処理 rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub *********************************************************** という構文を作ってみたのですが、 rs.Open "クエリA", cn, adOpenKeyset, adLockOptimisticのところで ストップし、SQLステートメントがただしくありません。 'DELETE'、'INSERT'、'PROCEDURE'、'SELECT'、または'UPDATE'を使用してください。というエラーになります。 持っている書籍にはこのことについて何も記述が無いので、ネット検索しながら独学で解決方法を探しているのですが、めぼしい記述に出会えず、困っています。 解決方法をご存知の方、また参考になる書籍・サイトをご存知の方、お力をお貸しください。 OS:windowsXP Excel:2003 Access:2003

質問者が選んだベストアンサー

  • ベストアンサー
  • m3_maki
  • ベストアンサー率64% (295/459)
回答No.2

> rs.Open "クエリA", cn, adOpenKeyset, adLockOptimistic とりあえず rs.Open "クエリA", cn, adOpenKeyset, adLockOptimistic, adCmdTable としてみてください。

nagoya-1
質問者

お礼

ご回答ありがとうございます。 無事動作確認できました。

その他の回答 (1)

  • hrkn-p
  • ベストアンサー率37% (68/182)
回答No.1

横道にそれますが、Accessをお持ちでしたら、アクセスでDBを扱ったほうが簡単です。 VBAの勉強としても、アクセス上でVBAをやるか、VBでやったほうが、実益があると思うのですが…。

nagoya-1
質問者

お礼

ご回答ありがとうございます。

関連するQ&A

  • アクセス クエリ-について

    テーブルには、 フィールド1 ------------------ エクセルaaa aaaエクセルaaa aaaエクセル ------------------ というレコードが入っていて、 ------------------------------------------------------ UPDATE テーブル1 SET テーブル1.フィールド1 = "Excel" WHERE (((テーブル1.フィールド1) Like "*エクセル*")); ------------------------------------------------------ という更新クエリ作り、実行すると、テーブルのデータが フィールド1 ------------------ Excel Excel Excel ------------------ になってしまいます。 やりたい事は、 フィールド1 ------------------ Excelaaa aaaExcelaaa aaaExcel ------------------ にしたいのですが、更新クエリじゃ無理なのでしょうか? 手動でテーブルで検索ダイアログを開いてフィールドの一部分のみ置換するしかないのですか? 最終的には、 ------------------------------------------------------ Sub 更新クエリ() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim 置換前 As String Dim 置換後 As String Dim SQL As String cn.Open "Provider = Microsoft.ACE.OLEDB.12.0;" & "Data Source= " & CurrentProject.FullName rs.Open "T置換", cn, adOpenKeyset, adLockOptimistic For i = 1 To rs.RecordCount 置換前 = rs("置換前") 'エクセル 置換後 = rs("置換後") 'Excel SQL = "UPDATE テーブル1 SET テーブル1.フィールド1 = ""Excel"" WHERE (((テーブル1.フィールド1) Like ""*エクセル*""));" DoCmd.RunSQL SQL rs.MoveNext Next rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub ------------------------------------------------------ のようにして、置換用テーブルのレコードを一つ一つ取得して、 テーブル1の値を置換していきたいのです。 何か対策があればご回答よろしくお願いします。

  • 【ACCESS2000】 VBAの更新処理に条件を加えたい。

    下記のような更新処理のVBAを組みました。 これにIDが5のものを更新するというのを加えるには どうすればよいでしょうか。 Dim cn As ADODB.Connection Dim rs As New ADODB.Recordset Set cn = CurrentProject.Connection rs.Open "tbl_D_売上", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect rs("入金方法") = 2 rs("入金方法名称") = "分割" rs.Update rs.Close Set rs = Nothing cn.Close Set cn = Nothing

  • Accessで、メモリを開放するタイミング

    すみませんが、教えてください。 次のコードように、SQL命令を2回以上行う場合、メモリの開放は、最後だけでよいのでしょうか? これでも、一応動くのですが、メモリを余計に消費していないのかどうか、よく分かりません。 SUB SAMPLE() Dim CN As ADODB.Connection Dim RS As ADODB.Recordset Dim SQL As String '接続 Set CN = CurrentProject.Connection 'レコードセットを取得(1) Set RS = New ADODB.Recordset SQL = "SELECT COUNT(*) AS CNT FROM 生徒名簿 WHERE クラス = 'TS'" RS.Open SQL, CN, adOpenKeyset, adLockOptimistic Msgbox(CNT) 'レコードセットを取得(2) Set RS = New ADODB.Recordset SQL = "SELECT COUNT(*) AS CNT FROM 生徒名簿 WHERE クラス = 'WS'" RS.Open SQL, CN, adOpenKeyset, adLockOptimistic Msgbox(CNT) '終了 RS.Close: Set RS = Nothing CN.Close: Set CN = Nothing End Sub

  • rs.MoveFirst

    アクセスvbaです。 Sub Sample() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim i As Long Set cn = CurrentProject.Connection rs.Open "Tテーブル", cn, adOpenKeyset, adLockOptimistic rs.MoveFirst '←これいらない? For i = 1 To rs.RecordCount rs.MoveNext Next rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub このようなコードの場合 rs.MoveFirst はあってもなくても変わらないですか?

  • Access2000でVBAからパラメータクエリを実行

    Access2000でフォームからの値を参照して実行するクエリを作りました。 SQLで表すと、以下のようなものです。 クエリ名:test SELECT TEST.HAKKODTE FROM TEST WHERE (((TEST.HAKKODTE)=Format([Forms]![F_メニュー]![txtNyukinDate],"yyyymmdd"))); このクエリをダブルクリックで実行すると問題なく実行できます。 これを、VBAからレコードを参照したいと思い、 以下のように記述しました。 Dim cn As ADODB.Connection Set cn = CurrentProject.Connection Dim rs1 As ADODB.Recordset Set rs1 = New ADODB.Recordset Dim sqlstr As String sqlstr = "SELECT * FROM test;" rs1.Open sqlstr, cn, adOpenKeyset, adLockOptimistic しかし、Open処理で、「1つ以上の必要なパラメータの値が設定されていません」 というエラーになってしまいます。 VBAを実行するときに、[Forms]![F_メニュー]![txtNyukinDate]に きちんと値は入力されています。 これを実行する方法はあるでしょうか?

  • MSアクセスで抽出した結果が何もなかった場合

    こんにちは。コンボボックスに入力した言葉でフィルターをかけるため下記のように記載しました。 ヒットした場合は問題ないのですが、ヒットしなかった場合、エラーが出ます。ヒットしなくてもエラーとせず、”ありません”のようにメッセージを出すにはどのようにすればよいのでしょうか? 宜しくお願いします。 Dim rs As New ADODB.Recordset Dim cn As ADODB.Connection Dim DETA1(1 To 10) i = 1 Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset DBnm = Me.CYUSYUTSU.Value KI = Me.KeyWord2.Value rs.Open DBnm, cn, adOpenKeyset, adLockOptimistic '********************************************** rs.Filter = "Tget='" & KI & "'" TI = rs!管理番号 NM = rs!名前 Do Until rs.EOF DETA1(i) = rs!地域 i = i + 1 rs.MoveNext Loop

  • アクセス VBAのエラー

    以下のコードをwindowsXPで問題なく使っていましたが、windows7で使ったところ 「保存できません」というエラーメッセージが出ます。ただ全く同じコードを(だと思うのですが)リストボックスのダブルクリックで実行すると作動します。参考に二つのコードを書いておきます。 何か原因に心当たりのある方よろしくお願いします。 (コマンドボタン) Private Sub コマンド選択_Click() Dim namecode As String namecode = リスト会員 Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Set cn = CurrentProject.Connection rs.Open "MT_会員", cn, adOpenKeyset, adLockOptimistic rs.Find "会員IDkai = " & namecode rs!Selectedkai = True '-1 rs.Save Me!リスト会員.Requery リスト印刷会員.Requery rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub (ダブルクリック) Private Sub リスト会員_DblClick(Cancel As Integer) Dim namecode As String namecode = リスト会員 Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Set cn = CurrentProject.Connection rs.Open "MT_会員", cn, adOpenKeyset, adLockOptimistic rs.Find "会員IDkai = " & namecode rs!Selectedkai = True '-1 rs.Save Me!リスト会員.Requery リスト印刷会員.Requery rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub

  • ExcelVBA Accessにデータ書き込み

    VBAでコマンドボタンを押した際に特定のセルの値をAccessDBに入力するプログラムを作りたいのですが、上手くいきません...。 実行した際に「実行時エラー '21472179000 (80040e 14)': オートメーションエラーです。」と表示されます。 また、ステップインで実行してみるとEnd Withのところでエラーが発生します。 恐らくインサート文が間違っていると思うのですが、試行錯誤しても解決できませんでしたので教えて頂きたいです。 以下プログラムです。 Private Sub CommandButton1_Click() Dim cn As ADODB.Connection Dim cmd As ADODB.Command Dim rs As ADODB.Recordset Dim constr As String Dim strSQL1 As String Dim a As String a = Range("A1").Value Dim b As String b = Range("A2").Value Dim c As String c = Range("A3").Value constr = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=~.accdb strSQL1 = "insert into " & _ "TableName (1,2,3) " & _ "values ('" + Range("A1").Value + "','" + Range("A2").Value + "','" + Range("A3").Value + "')" Set cn = New ADODB.Connection cn.ConnectionString = constr cn.Open Set cmd = New ADODB.Command With cmd .ActiveConnection = cn .CommandText = strSQL1 .Execute End With Set cmd = Nothing Set rs = Nothing cn.Close Set cn = Nothing End Sub 以上、宜しくお願い致します。

  • VBAのデバックをどなたかお手伝いください。

    もちろん自分でも調べてはいるのですが、急いでいるため、もしどなたか教えてくだされば大変助かります。 この(下記の)Then 以降からがわかりません。 Do Until rs.EOF '該当レコードあり If rs!MCD = "3162" Then '--------------------------------------------- strcriteria = "CAT = '" & rs!CAT & "'" ' --- A rs2.Find strcriteria, 0, adSearchForward If rs2.EOF Then ' Else rs!仕入単価世代1 = rs!仕入単価 rs!仕入単価 = rs2!discount End If '--------------------------------------------- rs!更新日 = Now() rs.Update End If 情報が不足していればお答えします。どうぞ宜しくお願いいたします。 (補足)これより前に入力されているのは以下のものです。 Dim cn As ADODB.Connection Dim cn2 As ADODB.Connection Dim rs As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim strmsg As String Dim lngRet As Long Dim strcriteria As String Set cn = CurrentProject.Connection Set rs = New ADODB.Recordset Set cn2 = CurrentProject.Connection Set rs2 = New ADODB.Recordset rs.Open "商品2_T", cn, adOpenKeyset, adLockOptimistic rs2.Open "商品2_T25discountてすと", cn2, adOpenKeyset, adLockOptimistic

  • なぜrs.Move i ではダメなのでしょうか?

    access2003です。 テーブル1には、 フィールド1 あ い う え お が入っています。 Sub test() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim i As Long Set cn = CurrentProject.Connection rs.Open "テーブル1", cn, adOpenKeyset, adLockOptimistic For i = 0 To rs.RecordCount rs.Move i Debug.Print rs.Fields(0).Value Next rs.Close: Set rs = Nothing cn.Close: Set cn = Nothing End Sub をすると、 あ い え とイミディエイトウインドウに表示されるのですが Debug.Print rs.Fields(0).Value の部分で 「実行時エラー'3021' BOFとEOFのいずれかTRUEになっていか、または現在のレコードが削除されています。 要求された操作には、現在のレコードが必要です。」 となります。 その時のiの値は、3です。 rs.MoveFirst For i = 1 To rs.RecordCount Debug.Print rs.Fields(0).Value rs.MoveNext Next にすると、 あ い う え お が取得されます。 なぜrs.Move i ではダメなのでしょうか? 理由を教えてください。

専門家に質問してみよう