With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.
Course Of The Month
5 days, 20 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
10-
7
17 Comments
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...)
Featured Post
This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette.
This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page.
The problem with sub-reports and headings:
Add a dummy group to the sub report using the expression =1:
Set the “Repeat Section” property of the dummy…
Suggested Courses
Course of the Month5 days, 20 hours left to enroll
707 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.