Write Import Spec "On The Fly...?

[cliffhanger9]

I am trying to automate an import process and was wondering if anyone has had any luck building an import specification on the fly from VBA.

I guess it would have to consider the list of fields and the delimiters etc..

If anyone has, or has any suggestions on how to accomplish this (if even possible at all) it would be greatly appreciated!!

ROCK ON and Thanks in advance!!!!!

 

[grande]

I gotta be honest, I don't know how to do that, BUT, if your import file has the field names, you don't need an import spec.

So, if possible, give your table-to-be-imported the fieldnames.

 

[cliffhanger9]

The import file does not

 

[HitechUser]

An alternative I found...

I think the fastest way to do this is to use ADO. To achieve this, create a reference to "Microsoft Active X data object" in your MsAccess vba project.
Then you have to create a schema.ini file in the directory with the file you want to import. This schema.ini file will define the structure of your text file.

Here is an example of how the schema.ini file could look like. Do note that the "Sample.txt" can be replaced with the filename you are using

[Sample.txt]
Format=FixedLength
CharacterSet=ANSI
Col1=field1 Text Width 2
Col2=field2 Text Width 5


Now the code to include in your VBA code.

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New Connection

'Connect to the Directory where the textfile is '
With conn
.CursorLocation = adUseClient
.ConnectionString = "Provider=MSDASQL;DRIVER={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=" & sDir
.Mode = adModeRead
.Open
End With


Set rs = New Recordset
With rs
.ActiveConnection = conn
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open "select * from [" & sFile & "]"
End With

Do While Not rs.EOF
docmd.runquery "Insert into t_table (field1, field2, field3) values (" & _
rs.fields("field1") & ", " & rs.fields("field2") & _
", " & rs.fields("field3") & ")"
rs.movenext
loop

rs.close

set rs = nothing
conn.close
set conn = nothing

 

[HitechUser]

Oh.. forgot

Alternatively, you can use a schema.ini file in a Microsoft Visual Basic program to provide even more control over data in the text file, such as specifying special currency formats or handling floating point data types. A schema.ini file is a text file containing entries that override default text driver settings in the Microsoft Windows registry. You store a schema.ini in the same folder as the imported or exported text file, and it must always be named schema.ini.

 

[HitechUser]

Sorry (again)..

These may help Microsoft and Microsoft

 

[payback]

Have a look at this -

thread958-950508

I have used VBA to import and export delimited and fixed length files many times.

If this is not completely what you require, post a little more information and I will try to assist.

 

[VBslammer]

You can create the import specs programmatically but it takes some work. Look at the example I posted a while back:

Write Import Specification in VBA Module

VBSlammer

"You just have to know which screws to turn." - Professor Bob