I have a client who has access database. She exports it to tab delimited text file. I have to read it and update it to the database on the server. I have tried used the Scripting.FileSystemObject. And when I used the split function with tab as delimiter to separate the strings i encountered a problem: the split is giving the number of fields as 17 when there are total 19 fields in the table. It gives 17, because the user left the last two fields blank.

Anyway long story short, I read that ADO can read text file and manipulate it better. And the connection string works (atleast it did not give me errors), but it is giving me error when i read the text file using recordset object. Here is my code


Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

strPathtoTextFile = "/apps/apache2/chhs.gsu.edu-8000/htdocs/clinicalsdev/Website/import/CLINGAM.txt "

objConnection.Open "Driver={Microsoft Text Driver (*.txt)};Dbq="& strPathtoTextFile&";TableType=Tab"
query="SELECT * FROM clingam"
objRecordSet.open query,objConnection

I am also using the QETXT.INI that has the following

[Defined Tables]
CLINGAM.txt=clingam
[clingam]
FILE=CLINGAM.txt
FLN=1
TT=Tab
Charset=ANSI
FIELD1=Record No
FIELD2=AGENCY_NAM,varchar,255
FIELD3=DEPARTMENT,varchar,255
FIELD4= F_K_A_,varchar,255
FIELD5=MR_MS,varchar,255
FIELD6=CONTACT_FN,varchar,255< br>FIELD7=CONTACT_LN,varchar,255
FIELD8=DEGREES,varchar,255
FIELD9=CONTACT _LN,varchar,255
FIELD10=PHONE,varchar,255
FIELD11=FAX,varchar,255
FIELD 12=ADDRESS1,varchar,255
FIELD13=ADDRESS2,varchar,255
FIELD14=CITY,varchar, 255
FIELD15=ST,varchar,255
FIELD16=ZIP,varchar,255
FIELD17=EXPIRE_DAT,D ATe,m/d/yy
FIELD18=STATUS,varchar,255
FIELD19=REQUESTER,varchar,255


Now the problem is it does not recognize the file in select statement, when the file is in the path mentioned. Can somebody help me.

Thanks
Suvi65