Tab Delimited ODBC

[vbaprogammer]

I have the following code in my VBA module which works fine on a comma delimited text file. However, my text file is tab delimited. I have looked everywhere for a comparable statement to "Microsoft Text Driver ..." for tabs.

Can someone put me onto the correct coding?

On Error GoTo ErrorReadingDecList
Dim wrkODBC As DAO.Workspace
Dim conn1 As DAO.Connection
Dim myRS As DAO.Recordset
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
Set conn1 = wrkODBC.OpenConnection("", dbDriverNoPrompt, True, "ODBC;DRIVER={Microsoft Text Driver (*.txt; *.csv)};MaxScanRows=0;DBQ=" & strDB)
szSQL = "SELECT ApplicantAddress1, ApplicantAddress2, ApplicantCity " & _
"FROM " & strTable

Thanks,

Dan

 

[jimmythegeek]

Is there a reason why you are not using the TransferText Method?

If you manually import a text file and save the import specs, you can use that along with the statement, for example:

Let's say I wanted to import a text file called mytext.txt. I import it manually once, and save the import specs as "MyTextImportSpecs". In the specs you can specify what kind of delimiter you will be using (comma, tab, semicolon, etc.).

Now in code, I can simply put the following line:

DoCmd.TransferText acImportDelim, "MyTextImportSpecs", "YourTableName", "C:\mytext.txt"

Each time you run it, it will replace the existing table. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[vbaprogammer]

My text file consists of 53 variables I must retrieve and use to populate parallel variables in a Word template.

I discovered, after much frustration, that I can import a tab-delimited file AS LONG AS I leave the name xxx.CSV. It absolutely WILL NOT work with xxx.TXT.

So I simply added code to copy the original TXT to a CSV, pull my info from the CSV, and then delete the CSV file.

I will, however, take a look at the TransferText method. You know, there are so many commands it's hard to find the right one for the job.

Thanks for your help.

PS -- I've got a problem with VBA events I'm posting. If you can help, watch for it.

Dan

 

[coders4hire]

You may be able to override the 'csv' problem by programmatically developing your Import Specification on the fly. You can do this by populating your System tables (Spec and Column). Tools>Options>View System Objects

You could append the necessary import specs into the system tables, then call the spec with the TransferText method above. One caviat... back up your db in case you muck it up. Also - you have to append rows to the tables using SQL... not through the query builder.




---
doug@coders4hire.com

http://www.coders4hire.com

http://www.payraisecalculator.com

http://www.cliintel.com