VBA - Using ADO to import a text file, I get errors because one text field can sometimes appear like a date

J--InstrumentOutputFiles-ICPMS-New-18202
The text files come from a laboratory instrument. The first field is of the form yymm-{sequence in month}-{sample sequence}
Examples: 1503-009-1; 1503-012-1; 1503-017-1; 1503-019-1. These are the 9th, 12th, 17th and 19th work orders for March 2015.

Notice that the first two could be interpreted as Sep 1, 1503 and Dec 1, 1503. And that is what happens when I use the following
    objconnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strpathtotextfile & ";Extended Properties=""Text;HDR=YES;FMT=Delimited"""
    objRecordset.Open "SELECT * FROM [" & ThisFileName & "]", objconnection, adOpenStatic, adLockOptimistic, adCmdText

The files are produced as .csv files by the Agilent ICP-MS instrument and changing the file extension: a. doesn't solve the problem; b. Is not practical.

The destination table has that Sample Name field as nvarchar(20).

Two ideas:
1. Is there some variant of ADO parameters that will basically tell ADO to NOT HELP!?
2. Manually doing the import with the source being a flat file works. Is there a way to code the same logic into a stored procedure?
Douglass MacLeanCEO, CTOAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
You can use stored specification (but due to strange MSAccess logic you should at first add extension .csv to your file):
1. Start manual import from text file with master
2. Select file, delimited, press advanced, fill types of fields and press Save as:
importSet specification name (Spec1, for example)
now you can import your file to Table1 with code:
Sub test(strpathtotextfile)
Dim Str As String
DoCmd.TransferText acImportDelim, "Spec1", "Table1", strpathtotextfile, True
End Sub

Open in new window

You can of course rename your file also in code
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Douglass MacLeanCEO, CTOAuthor Commented:
That solution basically works. However, I have decided on a different solution. I am using the SQL Server Bulk Insert feature. It has the benefit of not letting Access/Excel 'help' me by trying to interpret the data during the import.

Thanks for your time and effort on my behalf.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.