Type Conversion Failure

[jessgold]

Importing data from an excel spreadsheet that has numbers in the first several rows causes access to override my field selection as "text". As I understand the problem this has been a known issue for access for some time. Is there anyway to set something in my db to prevent this from happening? office 2010. I have read the Microsoft work around of inserting a (false data)text record in the first row of the data but this is far from ideal. Data is being downloaded in excel format dozens of times an hour and imported to the db. Having to do the extra step of creating false data greatly hinders what could be a very easy user friendly process. Is there any way to override this bad behaivior? I am not a coder by any means but I am beginning to suspect unless I learn vb there is no way around this bug. any suggestions welcome. Please keep in mind I am not a coder and somewhat new to access.

[jessgold]

As an alternative is there a way to get it to not create a table in access with the error information so we don't add dozens of tables per hour

[pbaldy]

How are you importing? You should be able to import into an existing table. That's what this does:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", strPath, True

[jessgold]

Ty for the reply. I am using the import wizard and saving the steps so I can automate them. Of the three choices you get with that I am using the second choice of "append" to an existing empty table with pre defined fields. Being that I do not know about the coding side of access I am not sure what to do with that line of code, where to put it or how to tweak it to suit my needs. The real problem I am facing is how to keep access from overriding the data field type based on the data in the first few rows of an import. or as an alternative to import without writing an error table for Type Conversion Failure. Thank you again for the reply, maybe I can learn to integrate the basic code I need but, for the moment, what to do with code snippets is beyond me.

[pbaldy]

If you're appending into an existing table, you should be able to define the data types in design view of the table. You might also be able to use a saved import specification, but I've only used those enough to be dangerous, not enough to actually know what I'm talking about. ;P

[jessgold]

In design view you can indeed define data type of the tables fields. The problem is that when importing the table sets that data type to whatever is in the first few cells of your import ignoring what the field is set to. so if you don't have letters in the first few records of that field then it sets the data type to be numerical even though the field stays set in options to text in actuality it is set to numbers and then throws this error as soon as one of the records uses a letter. From what little I could find on this it is a known issue for many years now. I was hopping some access expert would have a better way around it for 2010 then inserting false data but there may not be a way.