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 MacLeanCTOAsked:
Who is Participating?
 
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
 
Douglass MacLeanCTOAuthor 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
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.