How to avoid error when importing Excel 2003 file into Access 2003 database [" is not a valid name. Make sure that it does not include invalid characters or punctuation]"?
I am trying to import an Excel file into a SQL Server table using Access 2003.
My Access VBA code is as follows:
ImportedFile = "U:\Tclientlink.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblClientLink", ImportedFile, True, ""
The data structure of the table tblClientLink is as follows with 3 columns:
TapsAccount Gim2Account SuppressionDate <--- Column Names
nvarchar(255) nvarchar(255) nvarchar(255) <--- Data Types
My spreadsheet is attached.
I get the following error message when the 2nd VBA Statement executes:
Run-time error '3125':
" is not a valid name. Make sure that it does not include invalid characters or punctuation
and that it is not too long.
Tclientlink.xls
My Access VBA code is as follows:
ImportedFile = "U:\Tclientlink.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblClientLink", ImportedFile, True, ""
The data structure of the table tblClientLink is as follows with 3 columns:
TapsAccount Gim2Account SuppressionDate <--- Column Names
nvarchar(255) nvarchar(255) nvarchar(255) <--- Data Types
My spreadsheet is attached.
I get the following error message when the 2nd VBA Statement executes:
Run-time error '3125':
" is not a valid name. Make sure that it does not include invalid characters or punctuation
and that it is not too long.
Tclientlink.xls
ASKER
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblClientLink", ImportedFile, True
I tried your recommendation using the code above.
However, the same exact error occurs. Thanks capricorn1 for the suggestion anyway.
I tried your recommendation using the code above.
However, the same exact error occurs. Thanks capricorn1 for the suggestion anyway.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Access 2007 will convert the "." to a "_"
(So it seems like Access 2003 may not like table names with ".")
I don'y have Access 2003 to test, ...but you may be able to rename the file, after import, to include the "."
JeffCoachman
(So it seems like Access 2003 may not like table names with ".")
I don'y have Access 2003 to test, ...but you may be able to rename the file, after import, to include the "."
JeffCoachman
ASKER
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblClientLink", ImportedFile, True
The above generates the same error taking off the dbo qualifier from the table name.
The above generates the same error taking off the dbo qualifier from the table name.
ASKER
it must be that there was a hidden field in a column. I deleted all the columns from col D on and then the import worked. thanks gentlemen.
Did you verify that there are no leading (or trailing) spacebar characters in the Excel field names?
Having these spaces generates the same error you are getting...
Having these spaces generates the same error you are getting...
cap.
Are you OK with this...?
I see you put your little smiley in your post.
;-)
As you know, I have no problem with the acceopted answer being changed if a better or earlier post was the actual answer...
(Or if I inadvertently posted the same syntax as you...)
Jeff
Are you OK with this...?
I see you put your little smiley in your post.
;-)
As you know, I have no problem with the acceopted answer being changed if a better or earlier post was the actual answer...
(Or if I inadvertently posted the same syntax as you...)
Jeff
ASKER
Thanks for your assistance.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblClientLink", ImportedFile, True, ""
to make it look like
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblClientLink", ImportedFile, True