Linking to csv file using ADOX and import spec

[UHNSTrust]

I am linking to a csv file using ADOX but would like to know if I can use an import spec on the linked table like I can if I use.

DoCmd.TransferText "import_Spec", "tblName"

My code at the moment is

    Dim App_Cnn As ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table
    
    Set App_Cnn = New ADODB.Connection
    With App_Cnn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .Properties("Data Source") = "c:\MHMDSWorkFolder\Blank2000.mdb"
        .Properties("Mode") = adModeReadWrite
        .Properties("Jet OLEDB:Engine Type") = 5
        .Properties("Locale Identifier") = 1033
        .Open
    End With
    
    'Linking new file to DB
    Set cat.ActiveConnection = App_Cnn
    Set tbl.ParentCatalog = cat
    
    With tbl
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Provider String") = "Text"
        .Properties("Jet OLEDB:Link Datasource") = "c:\MHMDSWorkFolder\ImportFiles"
        .Properties("Jet OLEDB:Remote Table Name") = csvFile
        .Name = "N_" & impTbl
    End With
    cat.Tables.Append tbl
    
    cat.Tables.Refresh

Any help would be appreciated.

Thanks in advance

Jonathan

 

[JerryKlmns]

Jonathan,

is there a schema.ini file here

c:\MHMDSWorkFolder\ImportFiles

so you can play with?

That file contains the recordlayout plus formating info and record field delimiters etc....

The method you mentioned needs the spec file which isnt easy to modify (or to build if the csv has many fields).

 

[UHNSTrust]

Jerry,

I do not have a schema.ini

There is an import spec within the database that I created by manually doing a link table to a text file.

I was hoping I could then use this import spec within my code.

Jonathan

 

[JerryKlmns]

Jonathan,

that code plays with the schema.ini file. If you want your spec file then just use the method

DoCmd.TransferText acImportDelim, "YourImportSpecs", "YourTableName", "FullPathFileName.Ext"

The schema.ini file is easier to build or modify than the specs and that's my favorite way... although I know how to programmaticaly modify the specs.