error msg when importing text file using access and vbs 1

[barny2006]

hi,
i have this code

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source = " & db_path

strSQL = "INSERT INTO tbl1 (x1, " & _ 
         "x2, " & _ 
         "x3, " & _ 
         "x4, " & _ 
         "x5, " & _ 
         "x6, " &_           
         "x7)  SELECT * FROM " & _ 
"[Text;HDR=yes;msysimexspecs=spec1;Database=c:\a_load_file\;FMT=Delimited].test#txt" 

objConnection.Execute strsql

the import file is a text file with fixed format. and tbl1 is already created and exists in access db.
i'm getting an error msg because i don't seem to be specifying the import specs correctly. any ideas what the correct syntax of the import specs would be? so that i can automate this function, which is currently is done by access user interface?

 

[Swi]

What is the error you are getting?

Swi

 

[Swi]

Also, here is an example. It is in VB6 but could easily be converted into script.

thread222-1288713

Swi

 

[barny2006]

the message is:
error: The INSERT INTO statement contains the following
unknown field name: 'F1'.
Make sure you have typed the name correctly, and try th
operation again.
code: 80040E14
The reason it's talking about 'f1' is because text
with headers, translates to f1, f2, f3 for the
fields. but i have to find a way to specify where
the import specs are.

 

[barny2006]

thanks swi.
i checked the link you posted. most of them are in
access vba using docmd.transfertext, which i can't
use in script. the other example which is in vb6
uses a method that i normally use, which is to insert
the values, using sql.
but my application is in vbscript and the customer wants
to be able to specify the import specs that is already
in access, using access impor/export function, as
i have posted.
the code that i posted, works with header=yes and csv
text file. but not with fixed format text file, which requires an import spec.
the difference between import and sql insert is that
it doesn't have to loop through the text file,
whereas in sql insert, i have to read the text file,
loop through the lines, and insert each row,
specifying fields, etc.




still looking for a solution.

 

[PHV]

most of them are in
access vba using docmd.transfertext, which i can't
use in script
Really ?
A starting point:
Dim appAccess
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase db_path
appAccess.DoCmd.TransferText 1, "spec1", "tbl1", "\path\to\file", True '1=acImportFixed

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barny2006]

that's great, phv.
i didn't know that you could use docmd in vbs.
thanks again.

 

[barny2006]

i'm still lost as to why my code works with
csv files and not with fixed files. and still
don't know the syntax for specifying import
specs.
thanks again, phv.

 

[PHV]

currently is done by access user interface
Have you saved the import specs ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barny2006]

yes, the import specs is saved as spec1.
your code that you posted, worked great. but
i still don't know how to specify an import
specifications for the code that i posted.
thanks.