asked on How to resolve an error importing an Excel file into a SQL Server 2005 table: " is not a valid name. Make sure that it does not include invalid characters or punctuation?
I am creating an Access applicaton with an APD type file using Access as the front end and SQL Server 2005 as the back end database.
I use the following commands to import an Excel file into a SQL Server table.
ImportedFile = "U:\TestFile.XLS"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttribu
I get the following error:
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.
Do you know how I can resolve this error?
Is the problem the input data?
I provided 2 input records in the attached Excel file.
the SQL Server table fields are defined as follows:
Field Name Data Type
-------------- -----------------
MailID nvarchar(255)
PartyID nvarchar(255)
Account nvarchar(255)
DocumentDate datetime
ScanDate datetime
DocumentType nvarchar(255)
Mailroom_Address1 nvarchar(255)
Mailroom_Address2 nvarchar(255)
Mailroom_Address3 nvarchar(255)
Mailroom_Address4 nvarchar(255)
Mailroom_Address5 nvarchar(255)
TestFile.xls
I use the following commands to import an Excel file into a SQL Server table.
ImportedFile = "U:\TestFile.XLS"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttribu
I get the following error:
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.
Do you know how I can resolve this error?
Is the problem the input data?
I provided 2 input records in the attached Excel file.
the SQL Server table fields are defined as follows:
Field Name Data Type
-------------- -----------------
MailID nvarchar(255)
PartyID nvarchar(255)
Account nvarchar(255)
DocumentDate datetime
ScanDate datetime
DocumentType nvarchar(255)
Mailroom_Address1 nvarchar(255)
Mailroom_Address2 nvarchar(255)
Mailroom_Address3 nvarchar(255)
Mailroom_Address4 nvarchar(255)
Mailroom_Address5 nvarchar(255)
TestFile.xls
Microsoft AccessMicrosoft SQL Server 2005
Last Comment
Randy Downs
ASKER
I renamed the file to generate a file not found error.
Then I get:
Runtime error 3011
The Microsoft Jet database engine could not find the object 'U:\TestFile.XLS'.
Make sure the object exists and that you spell its name and the path name correctly.
Then I get:
Runtime error 3011
The Microsoft Jet database engine could not find the object 'U:\TestFile.XLS'.
Make sure the object exists and that you spell its name and the path name correctly.
Try it using the literal file name. The variable doesn't seem to work
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttribu
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttribu
ASKER
Good suggestion. I tried the 1 statement and I get the same error as I do when I use the variable.
Try it with a local file (e.g., copy file to C: drive)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttribu
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttribu
ASKER
They took away our access rights to the C: drive at work.
OK whatever drive you have access should work. Is that the U: drive?
ASKER
Yes, I have put the XLS file on the U: drive to which I have access.
Apparently your code doesn't have access to the U: drive that's why I was looking for another. You could also try making everything lower case but it shouldn't matter if you are in a Windows environment.
See if you can run something like this from command line
excel.exe "U:\TestFile.XLS"
excel.exe "U:\TestFile.XLS"
ASKER
Yes that works.
The program generates a run time error of 3125 when the file is there.
When the file is not there the program generates a runtime error of 3011.
The program generates a run time error of 3125 when the file is there.
When the file is not there the program generates a runtime error of 3011.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True
This works.
This works.
ASKER
And this works too:
DoCmd.TransferSpreadsheet acImport, 8, "dbo.tblGetDocumentAttribu
DoCmd.TransferSpreadsheet acImport, 8, "dbo.tblGetDocumentAttribu
Yeah and your variable can probably go back in as well. Just need to get access to your table.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", ImportedFile , True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", ImportedFile , True
You can try this but you probably need to be user dbo
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGetDocumentAttributes"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGetDocumentAttributes"
Try using dbo_tblGetDocumentAttribut
https://www.experts-exchange.com/questions/27926580/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.html
https://www.experts-exchange.com/questions/27926580/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.html
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo_tblClientLink", ImportedFile, True
(The "." may not be allowed)
Also check for trailing or leading space bar characters in the Excel field names.
(Some users add in these spaces to simulate indents...)
ImportedFile = "U:\TestFile.XLS"