vba 导入 Excel 电子表格时 MS Access 2013 类型转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21586405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS Access 2013 Type Conversion Failure when importing Excel Spreadsheets
提问by JaredTS486
I have been trying to import Excel spreadsheets into MS Access 2013 (Via the interface) as tables and producing "Type Conversion Failure" errors for a field "SegmentID" that is of Integer type.
我一直在尝试将 Excel 电子表格作为表格导入 MS Access 2013（通过界面），并为整数类型的字段“SegmentID”产生“类型转换失败”错误。
When checking the failed row numbers in the Excel spreadsheet they are indeed integers just as all the others. Even re-entering and setting the entire column as numbers (and removing the decimal places) still produces the same error. I need to do this import for many spreadsheets and almost every one i try has the same error ranging from a handful to thousands randomly throughout the spreadsheet. The spreadsheets are of .xlsx format.
在检查 Excel 电子表格中失败的行号时，它们确实和所有其他行号一样都是整数。即使重新输入并将整列设置为数字（并删除小数位）仍然会产生相同的错误。我需要为许多电子表格执行此导入，几乎我尝试的每个电子表格都会在整个电子表格中随机出现从少数到数千不等的相同错误。电子表格为 .xlsx 格式。
Any Idea's would be greatly appreciated. I have thought of manually doing this with something like:
任何想法将不胜感激。我曾想过使用以下方法手动执行此操作：
DoCmd.RunSQL "CREATE TABLE TableName ([Date] DATETIME, [Name] TEXT, ETC..."DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TableName", "C:\ExcelData.xlxs"
DoCmd.RunSQL "CREATE TABLE TableName ([Date] DATETIME, [Name] TEXT, ETC..."DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "TableName", "C:\ExcelData.xlxs"
回答by Jimmy Smith
Integers range from -32,768 to +32,767 . Your segment id looks like it needs a LONG integer. Your spreadsheet appears to go to 33581 which puts you over integer range.
整数范围从 -32,768 到 +32,767 。您的段 ID 看起来需要一个 LONG 整数。您的电子表格似乎转到了 33581，这使您超出了整数范围。
回答by JaredTS486
As i mentioned before i was going to try and manually create the table and import the data. All data is being transferred to the created table with no errors or loss of data. In your "Visual Basic" view in MS Access hit Ctrl + Gand you can use this code.
正如我之前提到的，我将尝试手动创建表并导入数据。所有数据都被传输到创建的表中，没有错误或数据丢失。在 MS Access 中的“Visual Basic”视图中Ctrl + G，您可以使用此代码。
DoCmd.RunSQL ("CREATE TABLE Wildlife (ID COUNTER NOT NULL PRIMARY KEY, SegmentID INT, RouteID TEXT, Type TEXT, Subtype TEXT, Subtype2 TEXT);") DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Wildlife", "C:\PathToFile\Wildlife.xlsx",-1
DoCmd.RunSQL ("CREATE TABLE Wildlife (ID COUNTER NOT NULL PRIMARY KEY, SegmentID INT, RouteID TEXT, Type TEXT, Subtype TEXT, Subtype2 TEXT);") DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Wildlife", "C:\PathToFile\Wildlife.xlsx",-1
ID COUNTER NOT NULL PRIMARY KEYAdds an AutoNumber field ID that is the primary Key
ID COUNTER NOT NULL PRIMARY KEY添加作为主键的自动编号字段 ID
The -1argument at the end of the second Do.Cmdtells Access that the first row in the field is the column names. DoCmd.TransferSpreadsheet Method (Access)
-1第二个末尾的参数Do.Cmd告诉 Access 字段中的第一行是列名。DoCmd.TransferSpreadsheet 方法（访问）
