Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.
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
Asked:
10-
7
1 Solution
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
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
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.
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.
OK you get the same error with the variable ImportedFile so the issue is not access to the file but the query. Try another table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True
This works.
This works.
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
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27926580.html
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27926580.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...)
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now
ImportedFile = "U:\TestFile.XLS"