ExcelからACCESSへの接続でエラーが発生する理由とは

前へ 次へ
このQ&Aのポイント
  • ExcelからACCESSファイルに接続し、該当する値を落とし込もうとしてもエラーが発生します。
  • ACCESSのクエリ経由では問題なく該当するものが表示されますが、Excel経由だとエラーが発生します。
  • エラーの原因は、Excelからの接続では未定義関数 'Nz' を使用していることです。
回答を見る
  • ベストアンサー

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経由だとエラーになるのでしょうか？

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

  • ベストアンサー
  • bin-chan
  • ベストアンサー率33% (1403/4213)
回答No.1

SQLSet組み立ての中の、日本語の列名を [ ] で括るとどうなりますか？

Minsoi34
質問者

お礼

解決しました、ありがとうございました

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

関連するQ&A

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

    ＡＤＯでアクセスからエクセルシートの件数を取得したいのですが、うまく行きません。 アクセスの標準モジュールに 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が返ってくる原因 ・エクセルの該当のシートの最終行の取得の仕方 をご教授ください。 ご回答よろしくお願いします。

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

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

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

  • .VBSだとADODBのプロバイダが見つからない件

    こんばんは .vbsファイルからデータベースを操作しようとして躓いています。 エクセルvbaでは動いているのに、.vbsに移植するとエラーが出てしまいます。 エラーの内容は「プロバイダーが見つかりません。正しくインストールされていない可能性があります。」です。 次のスクリプトです。 'ここから Dim ADO_CONNECT Dim MDBName Dim dbCon Dim strSQL ADO_CONNECT = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" MDBName = WScript.Arguments(0) 'エクセルVBAの場合はハードコード Set dbCon = CreateObject("ADODB.Connection") dbCon.Open ADO_CONNECT & MDBName & ";" strSQL = "命令文" dbCon.Execute (strSQL) 'ここまで プロバイダーの場所、バージョン、32/64ビット、あたりに問題がのかもしれませんが、調べてもよくわかりません。エクセルVBAでは動作するので、参照設定？の違いなのかもしれません。どのように解決したら良いでしょうか。 Win 7 Pro 64bit MS Accessなし Excel 2007 あり

  • ACCESSで、DMax関数の条件の書き方

    ACCESS2002を使用しています。 自動的に連番を採番するコードを書きましたが、DMax関数でうまく最大値を取得できません。 条件式がうまく書けていないと思うのですが、どうぞお知恵をお貸しください。 テーブル[t_受注]にあるフィールド[受注コード]は、「yyyymm000」のように9桁表示です。 「yyyymm」の部分は現在の年月から取得し、「000」の部分は年月ごとに001～連番を採番しています。 しかし年月が変わるとうまく最大値を取得できず、次のように連番が採番されてしまいました。 [受注コード] 200705001 200705002 200705003 200705004 200706001 200706005(本当は200706002と自動採番したいのです) 最大値(MaxID)取得するコードはこのように書いております。 よろしくお願いします。 ------------------------------------------------------------------------------------------- Dim NowYearMonth As String Dim MaxYearMonth As String Dim MaxID As String NowYearMonth = Format(Date, "yyyymm") MaxYearMonth = Nz(DMax("Mid$([受注コード],1,6)", "t_受注"), "") MaxID = Nz(DMax("Mid$([受注コード],7,3)", "t_受注"), "NowYearMonth=MaxYearMonth") -------------------------------------------------------------------------------------------

  • ADOでエクセルファイルの件数を取得したい

    エクセルvbaでadoを使って他ファイルの件数を取得することは可能でしょうか？ もともと件数を取得したいファイルは共有フォルダに入っていて、 開くのにすごく時間がかかるのでADOで試みたいのです。 しかしうまくいきません。 当方の環境は Win7、エクセル２０１０です。 http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_090.html を参考にしたのですが、 Const cnsProvider = "Microsoft.Jet.OLEDB.4.0" Const cnsExtProp = "Extended Properties" Const cnsExcel = "Excel 8.0" Const cnsDBName = "D:\Book1.xlsx" 'ローカルでテスト Sub ADO_WS_TEST1() Dim dbCon As ADODB.Connection Dim dbRes As ADODB.Recordset Dim GYO As Long, COL As Long Dim strSQL As String ' Connection生成 Set dbCon = New ADODB.Connection With dbCon .Provider = cnsProvider .Properties(cnsExtProp) = cnsExcel .Open cnsDBName End With の .Open cnsDBNameの部分で、 実行時エラー-2147467259 外部テーブルのフォーマットが正しくありません。 となります。 Excel 8.0が原因なのでしょうか？ オフィス２０１０でもExcel 8.0でいいのでしょうか？ ココがうまく通ったら、 "SELECT * FROM [Sheet1$]"の部分をCOUNTにして 件数が取得できるかなー と思うのです。 リンク先を読むと、adoでやってもあまり早くはならなそうですが一応やってみたいのです。 よろしくお願いします。

  • 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

  • vbaのDLookupの戻り値が0になる。アクセス

    実際の値は、1,293,237なのに、DLookupの戻り値が0になります。 名 = "あああ" 最新の取引日 = DMax("[取引日]", "T明細", "[名] = '" & 名 & "'") 残高 = DLookup("[残高]", "T明細", "[取引日] = #" & 最新の取引日 & "#") のようなコードを実行すると必ず0が返ります。 何も該当するものがないと0が返るのでしょうか？ フィールド名等が間違っていれば、エラーになりますよね？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

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

質問する