Excelからmdbに接続してデータを取得 | Excel、ACCESSの2003を使用

前へ 次へ
このQ&Aのポイント
  • Excelからmdbに接続してデータを取得する方法を教えてください。Excel側でマクロを実行してmdbからデータを抽出したいのですが、うまくいきません。
  • ACCESSのSQLビューでは正しくデータが表示されるのに、Excel経由でデータが表示されません。mdb側のテーブルのデータ型や抽出条件に問題はありますか？
  • strSQLの条件式を「Like *」にすると、該当データが表示されません。処理やデータ型にどのような問題が考えられるでしょうか？
回答を見る
  • ベストアンサー

Excelからmdbに接続してデータを取りたい

Excel、ACCESSの2003を使用しています Excel側でマクロを動かしたら、mdbよりデータを取得したいのですが、うまく行きません。 Const cnsADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Const cnsADO_CONNECT2 = "パス名\ファイル名.mdb" Public Sub main接続処理() dbCon.Open cnsADO_CONNECT1 & cnsADO_CONNECT2 strTableName =　"テーブル"　& "." strDate = "20140905" 　'テスト用 strSQL = "SELECT " strSQL = strSQL & strTableName & "日時" strSQL = strSQL & vbNewLine & "FROM " & strTableName '''ここまでなら問題なく動く''' strSQL = strSQL & vbNewLine & "WHERE (((" & strTableName & ".日時)" strSQL = strSQL & " Like " strSQL = strSQL & """" strSQL = strSQL & strDate & "*" strSQL = strSQL & """" & "));" '''ここの処理がおかしい''' End Sub ACCESSのSQLビューではstrSQLの内容で該当するデータを表示できるのですが、Excel経由だと、データが1件も表示されません。 mdb側では「日時」フィールドはテキスト型で西暦+月日+時間の12文字で入っています。 201409051300 201409051310 201409051320.... 日付単位で抜き出したいので「20140905*」の型にしているのですが、うまく抽出できません。 ちなみにstrDateを「201408241300」にしてLike *を抜くと該当データが1件抽出されます。 Likeの処理が悪いのでしょうか？それともデータ型のせいでしょうか？ 分かる方が居ましたら教えてください、宜しくお願いします。

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

  • ベストアンサー
  • mitarashi
  • ベストアンサー率59% (574/965)
回答No.1

ときどき忘れていてはまりますが、 ADOのワイルドカードは*ではなくて%です。 リンク先に背景が書かれています。ご参考まで。 http://www.nurs.or.jp/~ppoy/access/access/acM012.html

fnfm0578
質問者

お礼

盲点でした・・・、教えてもらえなかったらいつまでも気付けませんでした ありがとうございました、おかげさまで処理が動きました

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • ExcelからACCESSへ接続するとエラーになる

    ExcelからACCESSファイルに接続し、該当する値を落とし込もうとしているのですが、エラーになってしまいます。 ACCESSのクエリ経由では問題なく該当するものを表示しています。 Const ADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Const ADO_CONNECT2 = "フォルダ場所" Dim dbCon As New ADODB.Connection Dim dbRes As New ADODB.Recordset Dim SqlSet As String Public Sub 接続() SqlSet = "SELECT " SqlSet = SqlSet & "テーブル名.日時, テーブル名.排出数" & vbNewLine SqlSet = SqlSet & "FROM テーブル名" & vbNewLine SqlSet = SqlSet & "WHERE (排出数=0) AND Nz(DLookUp(""排出数"",""テーブル名"",""日時='"" & DMax(""日時"",""テーブル名"",""日時<'"" & [日時] & ""'"") & ""'""),-1)<>0 ;" 'mdb接続 dbCon.Open ADO_CONNECT1 & ADO_CONNECT2 'レコードセット dbRes.Open SqlSet, dbCon, adOpenKeyset, adLockReadOnly レコードセットの部分で 実行時エラー　'-2147217900(80040e14)': 式に未定義関数 'Nz'があります。 と、出て処理が止まってしまいます。 イミディエイトでは ?SqlSet SELECT テーブル名.日時, テーブル名.排出数 FROM テーブル名 WHERE (排出数=0) AND Nz(DLookUp("排出数","テーブル名","日時='" & DMax("日時","テーブル名","日時<'" & [日時] & "'") & "'"),-1)<>0 ; と、なっており、これを該当するACCESSファイルのSQLビューに貼り付けると問題なく条件に一致した物が表示されます。 何故Excel経由だとエラーになるのでしょうか？

  • ExcelVBAでACCESSのクエリに接続したい

    Excel、ACCESSの2003を使用しています。 Excelで、指定した期間の情報をmdb上よりExcel側に出力する処理を考えています。 mdbファイルの接続先がテーブルならうまく処理できます。 しかし今回の接続先がクエリの為か、処理を動かしてもエラーは出ないのですが値が無い扱いになっています。 mdbのテーブルにはリンクテーブルで2つのテーブルがあります。 クエリ側で2つのテーブルをリレーションしており、クエリで表示している内容をExcel側に返したいのです。 下記の処理はクエリに接続をしていないのでしょうか？ Option Explicit Const cnsADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Dim dbCon As New ADODB.Connection Dim dbRes As New ADODB.Recordset Dim dbCol As ADODB.Field Dim strSQL As String, strStartDate As String, strEndDate As String Dim lngGyo As Long, lngCol As Long Dim strRootPath As String, strFileName As String, strPath As String Dim intField As Integer, i As Integer, j As Integer Public Sub ACCESS接続() strRootPath = "\\11.111.11.1\00_テストフォルダ\"　　'IPアドレスとフォルダ選択 strPath = "00_環境設定\01_テスト用\" 'パス先 strFileName "テスト.mdb" 'mdb名 dbCon.Open cnsADO_CONNECT1 & strRootPath & strPath & strFileName　　'mdb接続 strStartDate = "#09/01/2014# AND " strEndDate = "#09/23/2014#)" '''''SQLビューの内容をそのまま''''' strSQL = "SELECT テーブルA.年月日, テーブルA.実績No., テーブルB.依頼数, テーブルA.略号, テーブルA.作成No., テーブルA.数値No., テーブルA.名称, テーブルA.ＣＤ, テーブルA.長さ, テーブルA.場所, テーブルA.フラグ, " strSQL = strSQL & "Format([年月日],""yyyy/mm/dd"") AS 作成年月日" strSQL = strSQL & vbNewLine & "FROM テーブルB INNER JOIN テーブルA ON テーブルB.依頼No.=テーブルA.実績No." strSQL = strSQL & vbNewLine & "WHERE (((テーブルA.年月日) Between " strSQL = strSQL & strStartDate strSQL = strSQL & strEndDate strSQL = strSQL & " AND ((テーブルA.ＣＤ) Not Like ""%KN%"") AND ((テーブルA.場所) Like ""*IO*"") AND ((テーブルA.フラグ) Is Null))" strSQL = strSQL & vbNewLine & "ORDER BY テーブルA.年月日;" '''''SQLビューの内容をそのまま''''' dbRes.Open strZisseki_SQL, dbCon, adOpenKeyset, adLockReadOnly 'レコードセット intField = dbRes.Fields.Count lngGyo = 1 dbRes.MoveFirst Do Until dbRes.EOF lngGyo = lngGyo + 1 lngCol = 0 For Each dbCol In dbRes.Fields lngCol = lngCol + 1 Cells(lngGyo, lngCol) = dbCol.Value Next dbCol dbRes.MoveNext Loop dbRes.Close: Set dbRes = Nothing End Sub

  • 起動時設定が【最適化で終了】になっているmdb接続

    環境 Excel2003 Access2003 WindowsXP ツール→起動時の設定 フォーム/ページの表示 【最適化で終了】 これがついているmdbファイルをExcelから接続する方法はありますか？ 普段このmdbファイルはshift+Wクリックで開いています。 データを変数に入れてExcel側に表示しようとすると 実行時エラー3021 BOFとEOFのいずれかがTrueになっているか、または現在のレコードが削除されています。要求された操作には現在のレコードが必要です。 と表示されます。 Option Explicit Const cnsADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Public Sub mainデータベース '接続mdb名 FileName = \\～～テスト.mdb dbCon.Open cnsADO_CONNECT1 & FileName strsql = "SELECT * FROM テーブル名 WHERE 西暦年=2013 ORDER BY 開催順" dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly Gyo = 1 Rows("2:65536").ClearContents dbRes.MoveFirst Do Until dbRes.EOF Gyo = Gyo + 1 Set dbCols = dbRes.Fields Cells(Gyo, 1).Value = dbCols("～").Value Cells(Gyo, 2).Value = dbCols("～").Value Cells(Gyo, 3).Value = dbCols("～").Value Cells(Gyo, 4).Value = dbCols("～").Value Cells(Gyo, 5).Value = dbCols("～").Value Cells(Gyo, 6).Value = dbCols("～").Value Cells(Gyo, 7).Value = dbCols("～").Value Cells(Gyo, 8).Value = dbCols("～").Value Cells(Gyo, 9).Value = dbCols("～").Value ' 次のレコードに移る dbRes.MoveNext Loop ' レコードセット、データベースを閉じる dbRes.Close Set dbRes = Nothing dbCon.Close Set dbCon = Nothing End Sub dbRes.MoveFirstの部分でエラーが出ます。 記述がおかしいのでしょうか?

  • ADOでの接続

    Excel＋MySQLのADO方式での接続を行おうとしています。 Option Explicit Const cnsADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Const cnsADO_CONNECT2 = "C:\Program Files\MySQL\MySQL Server 5.0\data\test\uriage.frm;" Sub test() 　　＊変数宣言 　　Worksheets("Sheet1").Activate 　　dbCon.ConnectionString = cnsADO_CONNECT1 & cnsADO_CONNECT2 　　strSQL = "SELECT uri_no,uri_hi,kyaku_code FROM uriage" 　　dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly　←ここでエラー 　　処理 　　dbRes.Close 　　Set dbRes = Nothing 　　dbCon.Close 　　Set dbCon = Nothing End Sub やりたいことは、MySQLの内容をExcelに表示させるという処理です。 エラーの部分は、 *************************************************** 実行時エラー '3709' この操作を実行するために接続を使用できません。 このコンテキストで閉じているかあるいは無効です。 *************************************************** となっています。 このエラーの対処方法を教えてください。

    • ベストアンサー
    • MySQL
  • 【ADO】mdbのレコードの条件検索

    日付+特定のレコードの値が優のレコードをExcelのセルに表示するマクロを組んでいます。 Option Explicit Const cnsADO_CONNECT1 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Dim dbCon As New ADODB.Connection Dim dbRes As New ADODB.Recordset Dim dbCols As ADODB.Fields Dim strStartData As String, strEndData As String Dim strsql As String Dim Gyo As Long Public Sub testテスト() 'テスト用 strStartData = 20120401 strEndData = 20130331 '接続mdb名 FileName = \\～～テスト.mdb dbCon.Open cnsADO_CONNECT1 & FileName strsql = "SELECT * FROM テーブル名 WHERE 日付 BETWEEN '" & strStartData & "' AND '" & strEndData & "' ORDER BY 日付" 　 dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly strsql = Replace(strsql, "strEndData", strEndData) strsql = Replace(strsql, "strStartData", strStartData) dbRes.Filter = "座学判定　 = '優' or　WHERE 実技判定　 = '優' " 　 dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly　←ここでエラー Gyo = 1 Rows("2:65536").ClearContents dbRes.MoveFirst Do Until dbRes.EOF ' 行の変数を加算し必要項目を選択してセルにセット Gyo = Gyo + 1 Set dbCols = dbRes.Fields 'DT_P_点検Tblテーブルよりを抜き出す Cells(Gyo, 1).Value = dbCols("ID").Value Cells(Gyo, 2).Value = dbCols("日付").Value Cells(Gyo, 3).Value = dbCols("座学判定").Value Cells(Gyo, 4).Value = dbCols("実技判定").Value ' 次のレコードに移る dbRes.MoveNext Loop ' レコードセット、データベースを閉じる dbRes.Close Set dbRes = Nothing dbCon.Close Set dbCon = Nothing End Sub dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly　の部分でエラーになってしまいます。 「CommandオブジェクトのCommandTextが設定されていません」と表示されます。 dbRes.Filter = "座学判定　 = '優' or　WHERE 実技判定　 = '優' " 　 ここの部分が無ければ、指定の期間のデータを抽出してくれています。 指定期間　+　特定の部分がTrueの場合の抜き出しをしたいのですが、どこかで指定が不足しているのでしょうか？

  • エクセルＶＢＡでcommit,rollback

    エクセルＶＢＡでcommit,rollback 初めて質問させていただきます。 エクセルＶＢＡを一人で学習しています。 仕事で使ったことも、教えてもらったこともほとんどありませんのでほぼ初心者です。 今、エクセルファイルを二つ使って、一つをデータベースに、もう一つでそのデータベースを 利用するようなプログラムを書いています。 それで、２つの関連するテーブル（シート）を同時に変更(update)する必要があるのですが、 何らかのエラーが起きてしまったときのことを考えて、トランザクションを利用 出来たらと思っています。 いろいろググってアクセス用のコードを見つけたので、それを元に下のようなプログラムを 書いて試してみたのですが、ロールバックが効かずに更新されてしまいます。 '******** 定数 *********** Const cnsProvider = "Microsoft.Jet.OLEDB.4.0" Const cnsExtProp = "Extended Properties" Const cnsExcel = "Excel 8.0" Const cnsDBName = "SAMPLE_DB.xls" Const cnsYen = "\" '******** プログラム *********** Sub ADO_update_test() Dim dbCon As ADODB.Connection Dim strSQL As String ' -- Connection生成 -- Set dbCon = New ADODB.Connection With dbCon .Provider = cnsProvider .Properties(cnsExtProp) = cnsExcel .Open ThisWorkbook.Path & cnsYen & cnsDBName End With ' -- トランザクション開始 -- dbCon.BeginTrans ' -- sql作成 -- strSQL = "update [Sheet1$] set 金額 = 300 where ID = '001';" ' -- sqlを流す -- dbCon.Execute (strSQL) ' -- rollback -- dbCon.RollbackTrans ' -- クローズ・コネクション解放 -- dbCon.Close: Set dbCon = Nothing End Sub １、まずこの様なことが可能なのかどうか ２、出来るとしたらどのようにすればいいのか（間違っている点を指摘してください） 以上の２点についてお答えをいただけるとありがたいです。 よろしくお願いいたします。

  • 【EXCEL VBA】ローカルmdbからデータを取得したい

    （環境） 　WindowsXP 　Excel2003 　Access2003 現在、SQLサーバーからデータを取得しています。 下記のソースです（一部抜粋） Private Const SRC_SQL = "Provider=SQLOLEDB.1;User ID=testid;Password=testpass;Data Source=TEST-DB-1;Initial Catalog=testDB" Private Const TBL_TEST = "TEST.テストテーブル" Public Sub　TEST_PRO Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Set cn = CreateObject("ADODB.Connection") cn.CommandTimeout = 0 cn.Open SRC_SQL strSQL = "SELECT X.*, FROM " & TBL_TEST & " X" strSQL = strSQL & " WHERE X.担当者CD = '" & wNAME & "'" strSQL = strSQL & " AND X.オープン日 >= '" & start_dt & "'" strSQL = strSQL & " AND X.オープン日 < '" & end_dt & "'" strSQL = strSQL & " ORDER BY X.オープン日 ASC" Set rs = CreateObject("ADODB.Recordset") rs.Open strSQL, cn With rs ～～～ End With Set rs = Nothing End Sub これを、SQLサーバーではなく、 C:\TESTACCESS.mdbのテーブル：テストテーブル からデータを取得するように変更したいのですが、 どのようにコーディングすればよろしいでしょうか？ よろしくお願いします。

  • ExcelでAccessのテーブルの操作

    Excelで期間選択すると、Accessのクエリを使わずに直接テーブルを覗いて選択した期間のデータを抜き出す事は可能でしょうか？ 操作したいAccessのテーブルはこのような形になっています。 名称.tblというテーブルをビューすると 日付 　　　　名前　　値 20130101 大田　　10000 20130201 鈴木　　13000 20130211 加藤　　52000 20130301 石黒　　40000　 20130314 田中　　9500 20130401 石井　　1000 日付と名前と値が入っています。 Excelで指定した期間と名前を変数に保持するか、Excelのシートに保存したいのです。 現状はExcel側で 期間を選択して、実行ボタンを押す。 実行ボタンをクリックするとAccessのmdbファイルに接続。 Option Explicit Dim strAccess As String Private Sub cmb実行_Click() strAccess　= \\接続先\接続.mdb dbCon.Open cnsADO_CONNECT1 & strAccess これでAccessファイルに接続は出来ています。 日付のレコードに対して該当する期間があるかどうかをFor文辺りでループをかけて、該当する日付と名前と値を抜き出したいのです。 この処理は可能でしょうか？ 手詰まりを起こしていますのでお知恵をお貸し下さい。

  • ExcelでAccessのテーブルの操作2

    Excelで期間選択すると、Accessのテーブルから選択した期間のデータを抜き出す事は可能でしょうか？ 操作したいAccessのテーブルはこのような形になっています。 名称.tblというテーブルをビューすると 日付 　　　　名前　　値 20130101 大田　　10000 20130201 鈴木　　13000 20130211 加藤　　52000 20130301 石黒　　40000　 20130314 田中　　9500 20130401 石井　　1000 日付と名前と値が入っています。 Excelで指定した期間と名前を変数に保持するか、Excelのシートに保存したいのです。 現状はExcel側で 期間を選択して、実行ボタンを押す。 実行ボタンをクリックするとAccessのmdbファイルに接続。 Option Explicit Dim strAccess As String Private Sub cmb実行_Click() strAccess　= \\接続先\接続.mdb dbCon.Open cnsADO_CONNECT1 & strAccess strsql = "SELECT * FROM 接続先 WHERE 日付 " dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly Gyo = 1 Rows("2:65536").ClearContents dbRes.MoveFirst Do Until dbRes.EOF Gyo = Gyo + 1 Set dbCols = dbRes.Fields Cells(Gyo, 1).Value = dbCols("日付").Value Cells(Gyo, 2).Value = dbCols("名前").Value Cells(Gyo, 3).Value = dbCols("値").Value dbRes.MoveNext Loop dbRes.Close Set dbRes = Nothing dbCon.Close Set dbCon = Nothing End Sub しかし、これだと期間の指定が出来ていません。 dim strSData as string dim strEData as string strSData = Worksheets("設定").Range("C2")'201301 strEData = Worksheets("設定").Range("C3")'201304 この状態で strsql = "SELECT * FROM 接続先 WHERE 日付 BETWEEN strStartData AND strEndData ORDER BY 日付" と、追加して上記構文を動かしてみたのですがエラーが出て動きません。 「1つ以上の必要なパラメーターの値が設定されていません。」 と、出てここの部分で止まってしまいます dbRes.Open strsql, dbCon, adOpenKeyset, adLockReadOnly 変数の宣言の仕方が悪いのでしょうか？？

  • エクセルVBAでエラー！

    エクセルでVBAを組んでいます。 Aシート・Bシートにデータがあり、それをSQLで集計し、 Cシートに出力をしたいのですが、 『リンクされているExcelのワークシートを表示するための接続が切断されました。』 というエラーが出て、解決しません。 （調べてみましたが、似たような例がなく解決には至りませんでした・・・。） どなたかご教示お願い致します。 Private Sub CommandButton1_Click() Dim dbCon As Object Dim dbCols As Object Dim dbRes As Object Dim strSQL As String Dim sh1 As Worksheet Set sh1 = Worksheets("Cシート") Set dbCon = CreateObject("ADODB.Connection") dbCon.Provider = "Microsoft.Jet.OLEDB.4.0" dbCon.Properties("Extended Properties") = "Excel 8.0" dbCon.Open ThisWorkbook.FullName strSQL = "" strSQL = strSQL & "SELECT *" strSQL = strSQL & vbCrLf & "FROM [Aシート$] LEFT JOIN [Bシート$] ON [Aシート$].NO= [Bシート$].NO" Set dbRes = CreateObject("ADODB.Recordset") dbRes.Open strSQL, dbCon, adOpenKeyset, adLockReadOnly sh1.Range("A1").CopyFromRecordset dbRes dbRes.Close Set dbRes = Nothing dbCon.Close Set dbCon = Nothing End Sub

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する