mdbファイルをExcelから接続する方法とは？

前へ 次へ
このQ&Aのポイント
  • Excelからmdbファイルを接続する方法について調査しました。
  • mdbファイルをExcelに読み込み、データを変数に入れて表示する際に発生するエラーについて解決方法を探しました。
  • オンライン上にあるmdbファイルをExcelから接続する方法に関して情報を提供します。
回答を見る
  • ベストアンサー

起動時設定が【最適化で終了】になっている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の部分でエラーが出ます。 記述がおかしいのでしょうか?

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

  • ベストアンサー
  • nicotinism
  • ベストアンサー率70% (1019/1452)
回答No.1

>BOFとEOFのいずれかがTrueになっているか、または現在のレコードが削除されています。 レコードが無い・・・と言っているので 単純に strsql = "SELECT * FROM テーブル名" としたらどうなりますか？ あと、レコードセットを廻していますが、CopyFromRecordset でも？ http://www.moug.net/tech/exvba/0150068.html

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. その他MS Office製品

関連するQ&A

  • 【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の場合の抜き出しをしたいのですが、どこかで指定が不足しているのでしょうか？

  • 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 変数の宣言の仕方が悪いのでしょうか？？

  • 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

  • Excel VBA EOFに関して

    テキストボックスにDateを入力し、SQLServerに接続しSelectの結果をシートに書き込むだけのものなのですが、「BOFとEOFのいずれかがTrueになっているか、または現在のレコードが削除されています」というエラーでさっぱりわかりません。どなたかアドバイスをお願い致します。 -前後省略- stdate = UserForm1.stdate.Value endate = UserForm1.endate.Value strsql = "select ***" strsql = strsql & "from ***" strsql = strsql & "where *** and ***.start_date between '" & stdate & "' and '" & endate & "' " con.Open connectionString Set rs = con.Execute(strsql) Sheets("Data1").Select rs.MoveFirst i = 2 Do Until rs.EOF Cells(i, 1) = rs.Fields(0).Value Cells(i, 2) = rs.Fields(1).Value Cells(i, 3) = rs.Fields(2).Value Cells(i, 4) = rs.Fields(3).Value i = i + 1 rs.MoveNext Loop con.Close Set con = Nothing Exit Sub Err_DBConnectOpen: MsgBox Err.Description If con.State <> ADODB.adStateClosed Then con.Close End If Set con = Nothing End Sub

  • 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
  • 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の処理が悪いのでしょうか？それともデータ型のせいでしょうか？ 分かる方が居ましたら教えてください、宜しくお願いします。

  • ＡＤＯでアクセスからエクセルシートの件数を取得した

    ＡＤＯでアクセスからエクセルシートの件数を取得したいのですが、うまく行きません。 アクセスの標準モジュールに Sub ADO_TEST1() Const StrFileName = "D:\My Documents\検索.xls" Dim dbCon As ADODB.Connection Dim dbRes As ADODB.Recordset Dim i As Long Dim strSQL As String ' Connection生成 Set dbCon = New ADODB.Connection With dbCon .Provider = "Microsoft.Jet.OLEDB.4.0" .Properties("Extended Properties") = "Excel 8.0" .Open StrFileName End With ' SQL文作成 strSQL = "SELECT * FROM [Access接続用$];" Set dbRes = New ADODB.Recordset dbRes.Open strSQL, dbCon, adOpenDynamic, adLockOptimistic, adCmdText i = dbRes.RecordCount MsgBox "エクセルのAccess接続用シートの最終行は、" & i & "行です。" dbRes.Close: Set dbRes = Nothing dbCon.Close: Set dbCon = Nothing End Sub ************************************************************* を貼り付けて実行すると、エラーにはならないのですが-1が返ってきます。 実際の行は、200行あります。 ここで、エクセルの最終行を取得して、いづれエクセルのデータをアクセスのテーブルに転記したいのですが まずここで躓いてしまったので、 ・dbRes.RecordCountで、-1が返ってくる原因 ・エクセルの該当のシートの最終行の取得の仕方 をご教授ください。 ご回答よろしくお願いします。

  • AccessVBAコミットとロールバックの位置

    いつもお世話になっております。 BeginTrans、CommitTrans、RollbackTransの記述位置について お聞きしたい事があります。 合計で2のテーブルにインサート処理を行います。 Private Sub cmd_Click() Dim strSQL AS String '(1)adoコネクション生成 Set dbCon = Application.CurrentProject.Connection strSQL = "テーブルAに挿入するInsert文" '(2)テーブルaにデータ挿入 dbCon.Execute (strSQL) '(3)テーブルaのデータをレコードセットへ strSQL = "SELECT * FROM テーブルA" Set dbRes = dbCon.Execute(strSQL) Do Until dbRes.EOF ==================================================== ここでdbResの値を使用してテーブルbへ挿入するSQL文を作成 ==================================================== Loop '(4)テーブルbにデータ挿入 dbCon.Execute (strSQL) 以上の場合、テーブルaへのInsert文をCommitTransしなければ テーブルbで使用するためデータを作成できません。 (4)でエラーが発生するとテーブルaは更新確定されているので戻り事ができないのですが どのようにCommitTransを使用すればよろしいでしょうか？？ テーブルaの挿入が失敗した場合は処理を抜けて テーブルbの挿入が失敗した場合はテーブルaを元の状態（RollbackTrans）したいです。 ご教授お願い致しますm（ _ _）m

  • Yes/Noフラグの値をコピーできない

    すいません。ACCESS2003 VBAで下記の問題で困っています。 新たに作成したmdbファイル(テーブルの中身は空)に既存のmdbファイルのテーブル内容をコピーしたいのですが、Yes/Noフラグ型だけ移行できません。一応サンプルのソースです↓ Dim db As DAO.Database Dim RS As DAO.Recordset Dim strsql As String コピー元のmdbファイルをオープン Set db = OpenDatabase("コピー元mdb") 'SQLの生成 strsql = "" strsql = strsql & " SELECT * FROM テーブル名" 'レコードセット生成 Set RS = db.OpenRecordset(strsql, dbOpenSnapshot, dbReadOnly) 'コピー元テーブルにデータがあるか判定 If RS.RecordCount = 0 Then msgbox "データがありません" Else RS.MoveFirst 'コピー先のデータベースを設定 Set db = OpenDatabase("コピー先mdb") 'レコードの初期化 db.Execute (DELETE FROM コピー先テーブル名) Do While RS.EOF = False strsql = "" strsql = strsql & " INSERT INTO コピー先テーブル名(" strsql = strsql & " ,コピー先のYes/Noフラグ値" ' strsql = strsql & ")VALUES(" strsql = strsql & " ,'"コピー元のYes/Noフラグ値 "' " strsql = strsql & ")" db.Execute (strsql) RS.MoveNext Loop End If RS.Close db.Close

  • エクセル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セレクト

質問する