Access 2003 TransferSpreadsheet Method errors while importing Excel 2003 document

I'm importing a number of excel worksheets using the code below.  I have one column that is defined as TEXT and is causing a numeric field overflow.

If I perform a manual Get External Data, the error log shows only records where the field value is text or a "/"

During the Get External Data, I also notice that the field value is Double - not TEXT which is defined for that field in the destination table.

Any thoughts are greatly appreciated.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImportedRegistrations", Me.txtFileName, True, Range:="RegistrationsV2!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblEventHeader", Me.txtFileName, True, Range:="EventHeader!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblLocations", Me.txtFileName, True, Range:="Locations!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblUsers", Me.txtFileName, True, Range:="Users!"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblCategories", Me.txtFileName, True, Range:="Categories!"

Open in new window

Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
<Would CSV be more reliable?>

Yes, but you need to create an import specification.

'to create an import specification
1. File>get external data>import
2. select in the File of types box   Text files (*.txt etc..
3. select the file
4. in the import text wizard window select  delimited
5. Click advanced
6. in the import specification window
    type the name of the field in the Field Name column
    (here you can use the field names of the destination table, specify data type,
      check the box Skip if you do not want to import the column)

7 click save as, give the specification a name  <-- this is the specification name that you will use in the command line below

Now you can use the code below to import the CSV file via code

   DoCmd.TransferText acImportDelim, "ImportSpecificationName", "myTableName", "C:\myCsv.csv", True

OnsiteSupportAuthor Commented:
To add to this problem, I removed all Alpha data from the column and the import works with no errors.
So, I have a column defined as TEXT, but sees numerics in the column during the import and it overrides it.  Then when Text is imported, the import fails.
Access 2003 import from Excel always seems to have that grief. It guesses based on the first 50 rows or so.
1)Put a dummy row of data in row two to fool Access and then whack that record from your destination
2)Build good dummy files to use as link tables.  Once Access decides what a column is, it doesn't change it's mind.
Replace the dummy tables with the real ones.
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

OnsiteSupportAuthor Commented:
Would CSV be more reliable?
Nick67Connect With a Mentor Commented:
That may work, and Access 2003 may not ignore your specified data assignments.
Access 2007 and 2010 do not ignore your specified data assignments.

How often do you do this?
Option 1 can be automated to open the Excel file, insert a dummy row, and then whack it out of the final table
Option 2 with the link tables is bulletproof but you have to set it up once, and then code to take the files named in your textbox and overcopy the dummy tables

capricorn1's option is good but you have to set it up once for each time the filename changes

No matter how you slice it, there's some hassle to getting what you want.
OnsiteSupportAuthor Commented:
Good thing is FN's will never change.
I use Option 2
I'll take the Excel file, make a copy, leave the headers and remove all but one line of data.
That data I make sure is the right type so Access gets it right and save it.
I then make it a link table in Acces.
I then overcopy that dummy file with the real thing.

Then I can just query the link table and do whatever with it.
Just make sure to never use the Linked Table Manager to change the link table file location and it works perfectly
OnsiteSupportAuthor Commented:
So, in theory, I could force all values in this column to be test and it should solve my problem? Say - Add a "T" in front of every record?
Rey Obrero (Capricorn1)Commented:
adding a dummy information to your data before importing ???

what are the values in the column that is giving problem ?
Rey Obrero (Capricorn1)Commented:
if you have mixed values of alpha and numbers for that column, just select all cells and do a descending sort on that column before importing.
<So, in theory, I could force all values in this column to be test and it should solve my problem? Say - Add a "T" in front of every record? >

It doesn't have to be every record.
Access looks at about the first 50 rows or so.
If it doesn't see an alphanumeric in the first 50, regardless of what you have declared the column to be in Excel, it'll make it numeric.
If it sees an alphanumeric in the first 50, it is likely to choose text, although sometimes it gets the smart ass idea that you made a typo with that alphanumeric one, and that the column really still should be numbers :(
This PITA is one of the few good reasons to upgrade to Access 2010

If you, and not end users, are doing this, you could manually add some letters into the value of the first few rows of your problem columns, and then fix the data in Access afterward--that works, but is not necessarily elegant.
OnsiteSupportAuthor Commented:
Since it's an Alpha field on the web side, I'm concatening text to the front end of the field contents.  Only problem now is the clown who is giving me the data killed the website! Lol
Now that you mention it, it would be easy enough to run an update query along the lines of

UPDATE Table1 SET Table1.Field1 = Right([field1],Len([field1])-1)
WHERE (((Table1.Field1) Like "t*"));

to get rid of the leading letter 't' if you concatenated 't' onto all the data prior to import.

Sometimes its not the answers that you get from the Experts, its the thought process you go through that gets you where you need to go!
Most of my own questions go that way :)
OnsiteSupportAuthor Commented: now I'm having a competition for the points :)

Please let me know why you feel you deserve the points
OnsiteSupportAuthor Commented:
The contest entries will be reviewed and judged by Donald Trump for sarcasm content  LOL
The points need to go on an answer that details how you actually solved the problem :)
So it isn't a question of deserved, it's a question of what worked
And while @capricorn1 has a bazillion points, and I have a whack, it's not really about that for me.
It's about seeing to it that working answers get posted--because when I started in MS Access, my googling lead to the site A LOT.

So,  what did you do in the end?
OnsiteSupportAuthor Commented:
I'm at a dead end.  I've modified the query on the web side (SQL2Excel joomla plug-in).
In PHPMyAdmin, the results look right.  But, since there are resource issues on the joomla website, I can't get an excel sheet that isn't corrupted.

I will be modifiying an older Excel sheet when the export was working to look like the expected results.
I may be beating up my counterpart on the web side if he doesn't fix it soon :)
Nick67Connect With a Mentor Commented:
CSV is six-for-one half-dozen-for-another.
Excel opens them too.
Instead of TransferSpreadsheet, it's TransferText.

Alternatively, open the CSV in Excel and save it as an xls.
Extra bonus points alternative: Open the CSV using Excel automation from Access VBA, save it as xls and then move on.

Whatever it takes to get good data into Access where you can transmogrify it as needed :)
OnsiteSupportAuthor Commented:
Good Morning-
Before the web export, I used concat("T",value).
Forcing the value to text seems to do the trick when I import to Access...that is until they add another curve ball :)

Thanks for the help.
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.

All Courses

From novice to tech pro — start learning today.