Function HELP!

[Guest]

I have this application inwhich I must import .txt files. I have put together
this Schema.Ini file that lists the various fields to be displayed in table
format. These imported files must all come into the Access database using the
same lay out. Here below is a sample of the Schema.Ini file:

==============

[Contacts.txt]
ColNameHeader=True
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
Col1="Time" Char Width 7
Col2="FV_Cor" Char Width 7
Col3="FV_Nom" char Width 7
Col4="ADD" char Width 4
Col5="ADD_Ca" char Width 6
Col6="DI" Char Width 3
Col7="E" char Width 2
Col8="Lot_Number" char Width 12
Col9="Mar" char Width 4
Col10="Tin" char Width 4
Col11="SKU_Code" char Width 11
Col12="D_A_B" Char Width 6
Col13="Pad_X" char Width 6
Col14="Pad_Y" char Width 6
Col15="Pad_Z" char Width 6
Col16="MI_Tr" char Width 6
Col17="Check" char Width 6
Col18="Check_V" char Width 3
Col19="Quali" char Width 6
Col20="Quali_V" char Width 2
Col21="GR" char Width 3
Col22="Radius_CC" char Width 9
Col23="Index" char Width 7
Col24="CenTh" char width 6
Col25="Clic" char width 5
Col26="Bnom" char width 5
Col27="Pa" char width 3
Col28="Dpt_Cal" char width 8
Col29="ProX" char width 5
Col30="ProY" char width 5
Col31="GriX" char width 5
Col32="GriY" char width 5
Col33="CliX" char width 5
Col34="CliY" char width 5
Col35="ErrPro" char width 6
Col36="Suivi" char width 6
Col37="T_Tot" char width 6
Col38="T_Man" char width 6
Col39="Resea" char width 6
Col40="T_L" char width 4
Col41="T_P" char width 4
Col42="T_O" char width 4
Col43="TPC" char width 4
Col44="TQC" char width 4
Col45="T_" char width 4
Col46="TXY" char width 4
Col47="TRO" char width 4
Col48="_" char width 4

===============

Then, I run this procedure/function, which will link the target file. I want
to import, NOT link it! I also want to be able to randomly import any file
that may have the .Txt as it's extension:

========================
Function LinkSchema()
Dim db As Database, tbl As TableDef
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Linked Text")
tbl.Connect = "Text;DATABASE=D:\Documents and Settings\BTitre\My
Documents;TABLE=contacts.txt"
tbl.SourceTableName = "contacts.txt"
db.TableDefs.Append tbl
db.TableDefs.Refresh
End Function
=====================================
I'd like to have these two function work together to pull in the target data
into the database using the Schema.ini lay-put. Is it possible to have the
two procedures combined? Is this possoble? Is how?

 

[John Nurick]

Hi Jay,

To import the data from the file, you can either use something like

DoCmd.TransferText acImportFixed, , "TheTable", _
"C:\My Folder\Contacts.txt", False

or else construct and execute the appropriate SQL statement. Assuming
you're importing to an existing table, the syntax for the latter will be
like this:

INSERT INTO TheTable
SELECT *
FROM [Text;HDR=No;Database=C:\My Folder\;].Contacts#txt

ALternatively, you can link the file as you are doing now and then
execute an append query to move the data into your actual table.

However, schema.ini must have a section for each filename you plan to
import, so it cannot be used to import files with arbitrary names. So
realistically your code needs to do one of the following before
importing the data:

1) copy or rename the file it is importing to a standard name to match
the existing schema.ini

2) modify the [...] section heading in the existing schema.ini to match
the file about to be imported

3) generate a new schema.ini or schema.ini section to suit the file.

I have this application inwhich I must import .txt files. I have put together
this Schema.Ini file that lists the various fields to be displayed in table
format. These imported files must all come into the Access database using the
same lay out. Here below is a sample of the Schema.Ini file:

==============

[Contacts.txt]
ColNameHeader=True
Format=FixedLength
MaxScanRows=0
CharacterSet=OEM
Col1="Time" Char Width 7
Col2="FV_Cor" Char Width 7
Col3="FV_Nom" char Width 7
Col4="ADD" char Width 4
Col5="ADD_Ca" char Width 6
Col6="DI" Char Width 3
Col7="E" char Width 2
Col8="Lot_Number" char Width 12
Col9="Mar" char Width 4
Col10="Tin" char Width 4
Col11="SKU_Code" char Width 11
Col12="D_A_B" Char Width 6
Col13="Pad_X" char Width 6
Col14="Pad_Y" char Width 6
Col15="Pad_Z" char Width 6
Col16="MI_Tr" char Width 6
Col17="Check" char Width 6
Col18="Check_V" char Width 3
Col19="Quali" char Width 6
Col20="Quali_V" char Width 2
Col21="GR" char Width 3
Col22="Radius_CC" char Width 9
Col23="Index" char Width 7
Col24="CenTh" char width 6
Col25="Clic" char width 5
Col26="Bnom" char width 5
Col27="Pa" char width 3
Col28="Dpt_Cal" char width 8
Col29="ProX" char width 5
Col30="ProY" char width 5
Col31="GriX" char width 5
Col32="GriY" char width 5
Col33="CliX" char width 5
Col34="CliY" char width 5
Col35="ErrPro" char width 6
Col36="Suivi" char width 6
Col37="T_Tot" char width 6
Col38="T_Man" char width 6
Col39="Resea" char width 6
Col40="T_L" char width 4
Col41="T_P" char width 4
Col42="T_O" char width 4
Col43="TPC" char width 4
Col44="TQC" char width 4
Col45="T_" char width 4
Col46="TXY" char width 4
Col47="TRO" char width 4
Col48="_" char width 4

===============

Then, I run this procedure/function, which will link the target file. I want
to import, NOT link it! I also want to be able to randomly import any file
that may have the .Txt as it's extension:

========================
Function LinkSchema()
Dim db As Database, tbl As TableDef
Set db = CurrentDb()
Set tbl = db.CreateTableDef("Linked Text")
tbl.Connect = "Text;DATABASE=D:\Documents and Settings\BTitre\My
Documents;TABLE=contacts.txt"
tbl.SourceTableName = "contacts.txt"
db.TableDefs.Append tbl
db.TableDefs.Refresh
End Function
=====================================
I'd like to have these two function work together to pull in the target data
into the database using the Schema.ini lay-put. Is it possible to have the
two procedures combined? Is this possoble? Is how?