Solved

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?

Posted on 2013-01-03
17
714 Views
Last Modified: 2013-01-03
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
Comment
Question by:zimmer9
  • 10
  • 7
17 Comments
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742741
Sounds like it's not finding ImportedFile
ImportedFile = "U:\TestFile.XLS"
0
 

Author Comment

by:zimmer9
ID: 38742752
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
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742765
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:zimmer9
ID: 38742773
Good suggestion. I tried the 1 statement and I get the same error as I do when I use the variable.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742785
Try it with a local file (e.g., copy file to C: drive)

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "dbo.tblGetDocumentAttributes", "TestFile.XLS", True
0
 

Author Comment

by:zimmer9
ID: 38742796
They took away our access rights to the C: drive at work.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742798
OK whatever drive you have access should work. Is that the U: drive?
0
 

Author Comment

by:zimmer9
ID: 38742800
Yes, I have put the XLS file on the U: drive to which I have access.
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742806
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
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742810
See if you can run something like this from command line

excel.exe "U:\TestFile.XLS"
0
 

Author Comment

by:zimmer9
ID: 38742812
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
 
LVL 29

Accepted Solution

by:
Randy Downs earned 500 total points
ID: 38742825
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
 

Author Comment

by:zimmer9
ID: 38742831
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "test", "TestFile.XLS", True

This works.
0
 

Author Comment

by:zimmer9
ID: 38742832
And this works too:

DoCmd.TransferSpreadsheet acImport, 8, "dbo.tblGetDocumentAttributes", "U:\GetDoc.XLS", True, "A1:K65535"
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742839
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
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742843
You can try this but you probably need to be user dbo

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblGetDocumentAttributes", ImportedFile, True
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 38742849
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

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question