Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

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.tblGetDocumentAttributes", ImportedFile, True

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
0
zimmer9
Asked:
zimmer9
  • 10
  • 7
1 Solution
 
Randy DownsOWNERCommented:
Sounds like it's not finding ImportedFile
ImportedFile = "U:\TestFile.XLS"
0
 
zimmer9Author Commented:
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.
0
 
Randy DownsOWNERCommented:
Try it using the literal file name. The variable doesn't seem to work

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttributes", "U:\TestFile.XLS", True
0
Get 10% Off Your First Squarespace Website

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.

 
zimmer9Author Commented:
Good suggestion. I tried the 1 statement and I get the same error as I do when I use the variable.
0
 
Randy DownsOWNERCommented:
Try it with a local file (e.g., copy file to C: drive)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttributes", "TestFile.XLS", True
0
 
zimmer9Author Commented:
They took away our access rights to the C: drive at work.
0
 
Randy DownsOWNERCommented:
OK whatever drive you have access should work. Is that the U: drive?
0
 
zimmer9Author Commented:
Yes, I have put the XLS file on the U: drive to which I have access.
0
 
Randy DownsOWNERCommented:
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.
0
 
Randy DownsOWNERCommented:
See if you can run something like this from command line

excel.exe "U:\TestFile.XLS"
0
 
zimmer9Author Commented:
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.
0
 
Randy DownsOWNERCommented:
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
0
 
zimmer9Author Commented:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True

This works.
0
 
zimmer9Author Commented:
And this works too:

DoCmd.TransferSpreadsheet acImport, 8, "dbo.tblGetDocumentAttributes", "U:\GetDoc.XLS", True, "A1:K65535"
0
 
Randy DownsOWNERCommented:
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
0
 
Randy DownsOWNERCommented:
You can try this but you probably need to be user dbo

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGetDocumentAttributes", ImportedFile, True
0
 
Randy DownsOWNERCommented:
Try using  dbo_tblGetDocumentAttributes rather than dbo.tblGetDocumentAttributes


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...)
0
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

Featured Post

Free Tool: Port Scanner

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.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now