ADO and textfile driver - how to force all field types as text??

[DamoOz]

I have the follow code extract where I access the contects of a text file through ADO and run a SQL query to populate a table with the text file contents. This work fine apart from some fields which the text driver is assuming is a long integer and I need to access the field as a text data type. Is there any way of forcing the text driver to assume all fields are text and not allow it to guess the data type



here is the code extract

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & gsProgRoot & "aus_gsp_cdr.mdb"

sSQL = "INSERT INTO myTable & _ (REC_CODE,TYPE_CALL,......OUTP_NUM )" & _
"SELECT f1, f2, f3, f4,......f36, f37 FROM " & _
"[Text;Database=" & gsEDMPath_Syd & ";HDR=NO;FMT=Delimited].[myfile.txt]"

cn.Execute sSQL

 

[CCLINT]

You would need to create an Schema.Ini. Do a search in MSDE and the Knowledgebase. *******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[DamoOz]

I have tried this......

How do I reference the schema.ini when opening the recordset. I have checked msdn and found some info but not exactly what I need.

Do I need to use ODBC and create dsn etc

Thanks in advance

 

[CCLINT]

>>found some info but not exactly what I need<<

Then you probably didn't search correctly. Do an advance search and include the Knowledge Base articles.

This question has also been answered a few times in this forum the past 2-4 months. Do an advance search as well.

Plenty of info there.

The Jet ISAM TEXT driver for ADO will automatically use the INI file - which will overide any Registry defaults - if the INI is located in the same folder as the text file.

Here are a couple of Articles in the Kb and MSDE, found on the first 2 pages of the search results:

Q155512, Q254002, Q210001,

http://msdn.microsoft.com/library/d.../en-us/office97/html/workingwithtextfiles.asp

*******************************************************
_{General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[DamoOz]

CCLINT, whoever you are - THANK YOU