Create Import Table Specification

[asingh]

Is it possible to create a Table Specification [and later use it for Text Transfer]..manually. Without actually creating the table on the access data base. For example I want to create a Table Specification for 5 columns...which will be of type TEXT. But I dont want to create a table for this. Later on I Will use this specification to import data [via text transfer] into a separate table. I dont want to also export/import any table specification from another .MDB file.......!


Any option : VBA or Access would be fine for me.

thanks and regards,

asingh

[stanl]

yes, just Google "Schema.ini" - can be constructed with Notepad of the INI API methods. Or [what I often prefer] just create a fabricated recordset and persist as XML.

.02
Stan

[asingh]

Stanl...I tried the google method..It did create a linked txt file for me..using VBA...and and INI file. But still an import specification is not visible to the .MDB..?

Could you explain the XML method you are using. What I want is to be create import table specifications, without having to link tables/files to the .MDB.....!

[stanl]

I may have missed your point. My first reply was based on the assumption that you have an MDB file and given a text file (CSV or SDF) you want to convert it to a table in the MDB, but with specified field lengths/data types.

This MS KB article explains how to create a schema.ini entry, it is based on an existing table, but could be created from specs from a text file.

http://support.microsoft.com/?kbid=210001

The schema.ini entry is then used with either DAO's transfertext or the Jet 4.0 Text Driver [for SELECT or INSERT INTO...].

Alternately, I suggested using a fabricated recordset; for example



oRS=CreateObject("ADODB.Recordset")
oRS.Fields.Append "name",200,10,2|32|64 'Text field, length of 10
oRS.Fields.Append "age",131,,2|32|64 'number field
oRS.Fields("age").Precision =3
oRS.Fields("age").NumericScale =0
oRS.Fields.Append "isMember",11,,2 'logical field
oRS.Open ,,1,4,-1
oRS.Save "C:\temp\temp.xml", 1
oRS.Close
oRS = Nothing



will create a structured XML Recordset, saved as an ASCII file in XML format. NOTE: the final parameters for Append are OR'd - but you can leave them blank for default values

XML recordsets are opend with the MSPersist Provider -

[stanl]

oops, I'm sorry, it is about 6am and I fat-fingered which posted prematurely.... anyway, to continue



oRS = CreateObject("ADODB.Recordset")
oRS.Open("C:\temp\temp.xml","Provider=MSPersist;",1,4,256)


Opens it. Now, if you were to create a similar table with the three fields in Access, open it as a recordset and persist it to say, temp1.xml, then compare the two xml ascii files, you will see the structures only differ by 2 parameters rs:basetable= and rs:basecolumn=.

What I normally do is convert text files to xml recordsets, then modify the structures of the xml fields to reflect the missing parameters [in effect producing a 'disconnected recordset']. When needed, I use ADOX to create a blank Access Table from the XML structure then a simple command oRS.updatebatch will transfer all the rows to a fully formatted Access table. Because this generic, I am able to use 2 functions for an unlimited number and type of text files. Oh, and if I wanted my xml file in Excel.... 1 command CopyFromRecordset()

Below are a few links I used for inspiration as I needed to invent a wheel not constantly re-invent it.

http://www.geocities.com/pmpg98_pt/txt2xml.html

http://www.eggheadcafe.com/articles/20020108.asp

http://www.jdhitsolutions.com/resources/scripts/ConvertCSVtoXML.txt


This may be completely off-base to your aims but I am sure others will assist you.
Stan