スキルアップ・問題解決なら、モーグにおまかせ！

ExcelのシートをAccessのテーブルに追加したいです
 
　Set RS = CN.Execute(xSQL)　でエラーメッセージが出てしまいます。
　実行時エラー　2147217904（80040e10）オートメーションエラーです。
 
　Sub アクセスへデータ取込み()
     
    Dim xTmpPath As String
    Dim DBFile As String
    Dim SheetName As String
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Dim xSQL As String
     
    DBFile = ThisWorkbook.Path & "\DB01_取込み.accdb"
    xTmpPath = ThisWorkbook.Path & "\Ex01_data.xlsm"
    SheetName = "A101"
 
    Set CN = New ADODB.Connection
    CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBFile
    CN.Open
 
    xSQL = "DELETE *FROM [A101]"
    Set RS = CN.Execute(xSQL)
     
    xSQL = "INSERT INTO [A101]("
    xSQL = xSQL & "[ID]"
    xSQL = xSQL & " ,[T1]"
    xSQL = xSQL & " ,[T2]"
    xSQL = xSQL & " ,[T3]"
    xSQL = xSQL & ")"
    xSQL = xSQL & "SELECT"
    xSQL = xSQL & "[ID]"
    xSQL = xSQL & " ,[T11]"
    xSQL = xSQL & " ,[T22]"
    xSQL = xSQL & " ,[T33]"
    xSQL = xSQL & " FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=" & xTmpPath & "].[" & SheetName & "$A2:F]"
　　Set RS = CN.Execute(xSQL)　　’★★ここでエラー
    CN.Close
    Set CN = Nothing
    Set RS = Nothing
    MsgBox "取込み終了しました。"
End Sub

こんばんは
 
エラーの箇所で xSQL の内容はどうなっているでしょうか。
 
イミディエイトウィンドウに次のような文字列を入力して Enterキーを押下すれば確認できます。
 
?xSQL
 

INSERT INTO [A101]([ID] ,[T1] ,[T2] ,[T3])SELECT[ID] ,[T11] ,[T22] ...

DELETE *FROM [A101]

... & SheetName & "$A1:F]"

たらのり様
ありがとうございます。
DELETEも * と FROM の間に空白がなくてもＯＫでした。
ヘッダは 2行目です。1行目は空白行です。
自宅環境では成功するのですが、職場環境では一部のシートがエラーになります。
同一のExcel内の別シートで、同様のコードを実行すると成功するシートもあり、エラーの原因が不明です。
空白とか細かいところでミスしているのかと思い、成功するコードをコピペしてファイル名、カラム名など、必要なところだけ変更しても、エラーになり困っています。
明日職場環境で、イミディエイトウィンドウに?xSQL入力で確認してみます。
 

?xSQL で下記が表示されます。
 
INSERT INTO [A101]([ID] ,[T1] ,[T2] ,[T3])SELECT[ID] ,[T11] ,[T22] ,[T33] FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=D:\Ex01_data.xlsm].[A101$A2:F]
テーブル名、カラム名に誤りはなく、原因がわかりません。

 
DELETEも * と FROM の間、SELECT と [ID] の間に空白を入れてみましたが、エラーになってしまいます。

mugiro7 さんの引用:

自宅環境では成功するのですが、職場環境では一部のシートがエラーになります。
同一のExcel内の別シートで、同様のコードを実行すると成功するシートもあり、エラーの原因が不明です。

xSQL = xSQL & " FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=" & xTmpPath & "].[" & SheetName & "$A2:F]"

Sub TEST()
	Dim xTmpPath As String
	Dim DBFile As String
	Dim SheetName As String
	Dim CN As ADODB.Connection
	Dim RS As ADODB.Recordset
	Dim xSQL As String

	DBFile = ThisWorkbook.Path & "\DB01_取込み.accdb"
	xTmpPath = ThisWorkbook.Path & "\Ex01_data.xlsm"
	SheetName = "A101"

	Set CN = New ADODB.Connection
	CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFile & "'"
	CN.Open

	xSQL = xSQL & "SELECT [ID] ,[T11] ,[T22] ,[T33] "
	xSQL = xSQL & " FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=" & xTmpPath & "].[" & SheetName & "$A2:F]"

	Set RS = CN.Execute(xSQL)

	Do While Not RS.EOF
		Debug.Print RS.Fields("ID").Type & vbTab & RS.Fields("ID").Value
		Debug.Print RS.Fields("T11").Type & vbTab & RS.Fields("T11").Value
		Debug.Print RS.Fields("T22").Type & vbTab & RS.Fields("T22").Value
		Debug.Print RS.Fields("T33").Type & vbTab & RS.Fields("T33").Value
		Debug.Print ""
		RS.MoveNext
	Loop

	CN.Close
	Set CN = Nothing
	Set RS = Nothing
	MsgBox "取込み終了しました。"
End Sub

データ型 について、
 
例コードの Typeプロパティーの戻り値は
DataTypeEnum
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/datatypeenum
となります。
 
その値が、Accessのフィールドのデータ型に代入できるか確認しましょう。

Suzu様
ありがとうございます。
 
エクセルは、書式設定を、数値、文字列を使用し、テーブル側は、数値型、短いテキストを設定しています。
イミディエイトには、数値=5　文字列=202 で表示されます。
テーブル側の数値のフィールドサイズは、倍精度浮動小数点型を選択しています。
 
この状態でINSERTしてもエラーになってしまいます。
実行時エラー　2147217900（80040e14）オートメーションエラーです。

mugiro7 さんの引用:
エクセルは、書式設定を、数値、文字列を使用し、テーブル側は、数値型、短いテキストを設定しています。
イミディエイトには、数値=5　文字列=202 で表示されます。
テーブル側の数値のフィールドサイズは、倍精度浮動小数点型を選択しています。

ID T1 T2 T3　　IDが数値、以降文字列で２レコードで実行しています。
1 1 1 1
2 2 2 2
 
イミディエイト表示
5 1
202 1
202 1
202 1
 
5 2
202 2
202 2
202 2
xSQL = xSQL & " WHERE IsNumeric([Access数値型フィールドに対応するExcelフィールド名])=False"
を実行すると、レコードは表示されません。

2レコードのみで、その内容についても理解できました。
 
そうならば
 

Sub TEST2()
	Dim xTmpPath As String
	Dim DBFile As String
	Dim SheetName As String
	Dim CN As ADODB.Connection
	Dim RS As ADODB.Recordset
	Dim xSQL As String

	DBFile = ThisWorkbook.Path & "\DB01_取込み.accdb"
	xTmpPath = ThisWorkbook.Path & "\Ex01_data.xlsm"
	SheetName = "A101"

	Set CN = New ADODB.Connection
	CN.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & DBFile & "'"
	CN.Open

	xSQL = "DELETE * FROM [A101]"
	Set RS = CN.Execute(xSQL)

	xSQL = "INSERT INTO [A101] ( [ID], [T1], [T2], [T3] ) "
	xSQL = xSQL & " SELECT [ID], [T11], [T22], [T33] "
	xSQL = xSQL & " FROM [Excel 12.0;HDR=YES;IMEX=1;DATABASE=" & xTmpPath & "].[" & SheetName & "$A2:F]"
	Set RS = CN.Execute(xSQL)

	CN.Close
	Set CN = Nothing
	Set RS = Nothing
	MsgBox "取込み終了しました。"
End Sub

データを追加しないカラムが、T33以降にあり、テーブル、エクセルともにカラムを削除すると成功しました。
カラム名と、データ型は一致している認識なのですが。
削除して成功するのですが、原因がわかりません。
カラムは削除せず実行したいです。
 
 

横から失礼します。
 
テーブル側の[T3]以降のテーブル定義
INSERTでデータを追加する場合、省略不可のような類の設定していませんか？
外しているかも？しれませんが、念のため、確認してみてください。

WinArrow様
ありがとうございます。
 
知識不足で、省略不可の存在を把握、理解していません。
詳細わかりましたご教示ください。
 
自宅環境では下記コードで成功しました。
職場環境では明日試します。
 
Sub テスト()
 
Dim strFileName As String
Dim adoCn As Object
Dim adoRs As Object
Dim ws As Worksheet
Dim i As Long
 
strFileName = "取込み先DB.accdb"
Set adoCn = CreateObject("ADODB.Connection")
adoCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & strFileName & ";"
Set adoRs = CreateObject("ADODB.Recordset")
Set ws = ThisWorkbook.Worksheets("A101")
 
i = 3
With adoRs
    .Open "T001", adoCn, adOpenKeyset, adLockOptimistic
    Do While ws.Cells(i, 1).Value <> ""
    .AddNew
        !T11 = ws.Cells(i, 1).Value
        !T22 = ws.Cells(i, 2).Value
        !T33 = ws.Cells(i, 3).Value
        .Update
     i = i + 1
Loop
.Close
End With
adoCn.Close
 
Set adoRs = Nothing
Set adoCn = Nothing
MsgBox "データ取込みました。"
End Sub

Null 不可 フィールドに Null を入れようとしている のではないでしょうか。

Suzu様
ありがとうございます。
 
空文字列の許可はすべて「はい」になっています。
 
 Sub テスト()を職場環境で試したところ、.AddNew でエラーになります。
　実行時エラー　2147467259（80040005）オートメーションエラーです。エラーを特定できません。
Accessのテーブルに問題がありそうです。

既存のテーブルを作り直し（既存テーブルをデサインビューで開きコピーし、新規テーブルに貼り付け）
したところ、Sub テスト、Sub アクセスへデータ取込み　ともに成功しました。

たらのり様
Suzu様
WinArrow様
ご指導いただきありがとうございます。
迅速に対応いただき感謝しております。
 
テーブル作り直しでエラー回避はできましたが、考えられる原因がわかりましたら、ご教示ください。

あと考えられるのは
・値要求 はい / 空白文字 いいえ
・複数フィールドでのインデックスが指定してあり、かつ 重複不可 設定
あたりでしょうか。

>実行時エラー　2147467259
オートメーションエラーは、様々なケースがあるようです。
門外漢が、「これだ！」と答を出せるかというと、
かなり難しいと思います。
「実行時エラー　2147467259」で、Web検索してみてください。
心当たりがあるかもしれない・・・・
 

ご指摘の通り、重複不可ではないのですが、複数フィールドでインデックスの指定がありました。
テーブル作成し直しで、インデックスの設定は外れるので、そこが原因のようです。
実行時エラーをWeb検索し、ヒントを探すのですが、私には難解です。
お力添えただきありがとうございました。
今後とも、よろしくお願いいたします。