ExcelでACCESSの更新(update)がエラーになる はじ

Writer: admin Type: arte Date: 2019-01-08 00:00
ExcelでACCESSの更新(update)がエラーになる はじめて質問させていただきます。利用OS:Win7Officeバージョン:MS Excel2007/Access2007ADOを利用して、AccessのテーブルからExcelへデータを接続し、Excelで編集した内容を更新してAccessのテーブルを更新しようと考ています。(Access) ファイル名:★時間外管理ツール.accdb テーブル名:TBL申請カレンダー(社員ごとに勤務日365件のデータがあります) フィールド:申請日|所属チーム|職位|氏名|社員番号|勤務日|開始時間|終了見込時間|申請理由|まず、Excelのブックを開くときにAccessのデータを読み込みます。Option ExplicitPrivate Sub Workbook_Open()Dim strFileName As String strFileName = "★時間外管理ツール.accdb"Dim Cn As Object 'ADOコネクションオブジェクトSet Cn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続Dim rs As Object 'ADOレコードセットオブジェクトSet rs = CreateObject("ADODB.Recordset") 'ADOレコードセットオブジェクトを作成Dim strSQL As String strSQL = "Select * from QRY_Mainデータ where 社員番号 = '" & Range("E1").Value & "'"rs.Open strSQL, Cn 'SQLを実行して対象をRecordSetへWorksheets(1).Range("B4:Q4") = "" ’編集するデータの入力範囲Worksheets(1).Rows("7:" & Rows.Count).Clear ’B7セルから最終行までクリアWorksheets(1).Range("B7").CopyFromRecordset Data:=rs ’B7セル以下にデータをセットWorksheets(1).Range("B7").CurrentRegion.Borders.LineStyle = xlContinuous 'データ範囲に罫線をひくWorksheets(1).Range("I7:J100").NumberFormatLocal = "hh:mm" ’時刻表示設定rs.Close 'レコードセットのクローズCn.Close 'コネクションのクローズSet rs = Nothing Set Cn = Nothing 'オブジェクトの破棄End Subここまで正常に読込できます。B7セル以下に読み込んだデータの中から、勤務日をクリックすると、B4セルからN4セルまでデータが自動で表示されます。データを編集して「更新」ボタンをクリックすると、B7セル以下のデータに反映すると同時にAccessのデータも更新したいのです。SQL分のUPDATEで、AccessのTBL申請カレンダーのフィールド”社員番号”とフィールド”勤務日”が一致したレコードを更新したいのですが、以下のWHERE条件の部分でエラーとなります。 エラー内容:データ型が相違しています。標準モジュールに記述Option ExplicitSub updateRecords()Dim ws As WorksheetSet ws = ThisWorkbook.Worksheets("各自用")Dim strFileName As StringstrFileName = "★時間外管理ツール.accdb"Dim Cn As Object 'ADOコネクションオブジェクトSet Cn = CreateObject("ADODB.Connection") 'ADOコネクションオブジェクトを作成Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";" 'Accessファイルに接続Dim strSQL As StringDim i As Longi = 7Do While ws.Cells(i, 7) <> "" strSQL = _ "UPDATE TBL申請カレンダー" & _ "SET " & _ ws.Range("B4").Value & "=" & ws.Cells(i, 2).Value & "," & _ ws.Range("C4").Value & "=" & ws.Cells(i, 3).Value & "," & _ ws.Range("D4").Value & "=" & ws.Cells(i, 4).Value & "," & _ ws.Range("E4").Value & "=" & ws.Cells(i, 5).Value & " " & _ ws.Range("F4").Value & "=" & ws.Cells(i, 6).Value & "," & _ ws.Range("G4").Value & "=" & ws.Cells(i, 7).Value & "," & _ ws.Range("I4").Value & "=" & ws.Cells(i, 9).Value & "," & _ ws.Range("J4").Value & "=" & ws.Cells(i, 10).Value & "," & _ ws.Range("M4").Value & "=" & ws.Cells(i, 13).Value & "," & _ ws.Range("N4").Value & "=" & ws.Cells(i, 14).Value & "," & _ ”WHERE 社員番号='" & ws.Range("D4").Value & "'" And "勤務日=#" & ws.Range("G4").Value & "#" ←ここで、データ型が相違しています。というメッセージが表示されます。 Cn.Execute strSQL 'SQLを実行 i = i + 1LoopCn.Close 'コネクションのクローズSet Cn = Nothing 'オブジェクトの破棄End sub実現したいことは、社員番号と勤務日が一致したデータを更新したいだけなのです・・・なにとぞ、ご教示いただきますよう、宜しくお願いいたします。共感した0###”WHERE 社員番号='" & ws.Range("D4").Value & "' And 勤務日=#" & ws.Range("G4").Value & "#" ですね。ANDの前後の " が余分です。ナイス0
###hat********さんご教示いただきありがとうございます。Where条件で、日付の指定方法がわからず四苦八苦しておりましたので、データ型の指定についてやっと理解することができました!ありがとうございました。今度は、「UPDATE構文エラー」が表示されて 一難去ってまた一難なのですが・・・
###この質問は投票によってベストアンサーに選ばれました!###strSQL = _"UPDATE TBL申請カレンダー" & _"SET " & _ws.Range("B4").Value & "=" & ws.Cells(i, 2).Value & "," & _ws.Range("C4").Value & "=" & ws.Cells(i, 3).Value & "," & _ws.Range("D4").Value & "=" & ws.Cells(i, 4).Value & "," & _ws.Range("E4").Value & "=" & ws.Cells(i, 5).Value & " " & _ws.Range("F4").Value & "=" & ws.Cells(i, 6).Value & "," & _ws.Range("G4").Value & "=" & ws.Cells(i, 7).Value & "," & _ws.Range("I4").Value & "=" & ws.Cells(i, 9).Value & "," & _ws.Range("J4").Value & "=" & ws.Cells(i, 10).Value & "," & _ws.Range("M4").Value & "=" & ws.Cells(i, 13).Value & "," & _ws.Range("N4").Value & "=" & ws.Cells(i, 14).Value & "," & _”WHERE 社員番号='" & ws.Range("D4").Value & "'" And "勤務日=#" & ws.Range("G4").Value & "#" ↓↓↓strSQL = _"UPDATE TBL申請カレンダー" & _"SET " & _ws.Range("B4").Value & "=" & ws.Cells(i, 2).Value & "," & _ws.Range("C4").Value & "=" & ws.Cells(i, 3).Value & "," & _ws.Range("D4").Value & "=" & ws.Cells(i, 4).Value & "," & _ws.Range("E4").Value & "=" & ws.Cells(i, 5).Value & " " & _ws.Range("F4").Value & "=" & ws.Cells(i, 6).Value & "," & _ws.Range("G4").Value & "=" & ws.Cells(i, 7).Value & "," & _ws.Range("I4").Value & "=" & ws.Cells(i, 9).Value & "," & _ws.Range("J4").Value & "=" & ws.Cells(i, 10).Value & "," & _ws.Range("M4").Value & "=" & ws.Cells(i, 13).Value & "," & _ws.Range("N4").Value & "=" & ws.Cells(i, 14).Value & "," & _”WHERE 社員番号='" & ws.Range("D4").Value & "' And 勤務日=#" & ws.Range("G4").Value & "#" でこのエラーは直ると思いますがCn.Execute strSQLで次はエラーが出そうですが。。。ナイス0

 

TAG