Importing text file to Access - ADO preemptively interprets a text column as date datatype
We have an Access application with a SQL Server database for a testing laboratory. We are importing text files from an Agilent ICPMS instrument (sample attached). The Sample Name column is a text field of the format wwww-xxx-y. That matches a primary key in a main table in the SQL Server database. Examples:
1409-002-01, 1409-013-1, 1409-096-1. The first two look like dates to ADO and it reformats the data as such.
We're using this connection info:
objconnection.Open "Provider=Microsoft.Jet.OL
objRecordset.Open "SELECT * FROM [" & ThisFileName & "]", objconnection, adOpenStatic, adLockOptimistic, adCmdText 'select all text lines from the file
I have tried renaming the file as .txt. Same problem.
How can I force ADO to NOT HELP with interpreting the Sample Name values as dates?
G--GSAJDrive-InstrumentOutputFiles-ICPMS
1409-002-01, 1409-013-1, 1409-096-1. The first two look like dates to ADO and it reformats the data as such.
We're using this connection info:
objconnection.Open "Provider=Microsoft.Jet.OL
objRecordset.Open "SELECT * FROM [" & ThisFileName & "]", objconnection, adOpenStatic, adLockOptimistic, adCmdText 'select all text lines from the file
I have tried renaming the file as .txt. Same problem.
How can I force ADO to NOT HELP with interpreting the Sample Name values as dates?
G--GSAJDrive-InstrumentOutputFiles-ICPMS
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK. The good news is that it produces the right results.
Now, the trick is that my VBA code loops through all the text files in a specified folder and imports each of them. So I need to dynamically modify the import specification details on the fly.
How can I find that actual import specification that I created and turn it into VBA code that I can make dynamic?
The context is that the user clicks a button that says "Process Files". The user's only responsibility is to have put one to n files from the ICPMS instrument into a specified folder ready to be processed.
Now, the trick is that my VBA code loops through all the text files in a specified folder and imports each of them. So I need to dynamically modify the import specification details on the fly.
How can I find that actual import specification that I created and turn it into VBA code that I can make dynamic?
The context is that the user clicks a button that says "Process Files". The user's only responsibility is to have put one to n files from the ICPMS instrument into a specified folder ready to be processed.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
One format, exactly like the sample I attached
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
OK. That's good.
So, the import spec I saved is buried in the .accdb file somewhere and I don't need to see it and deal with it directly. Right?
So, the import spec I saved is buried in the .accdb file somewhere and I don't need to see it and deal with it directly. Right?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This is a great solution. Thanks much for your guidance.
ASKER
I'll try it and let you know. Thanks
Doug