[RESOLVED] Error Updating a text file using ADO

[stricknyn]

Hello, everyone,

I'm trying to update a comma delimited text file using ADO. I am able to connect to it and do everything I need to it except update one of the fields.
The same code works in every thing else I use it to update except text files.
The error I'm getting is:

err.Number
-2147467259

err.Description
Updating data in a linked table is not supported by this ISAM.

Here is my code:

Option Explicit
 
Sub Main()
 
Dim X As Long
 
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
 
Dim strPath As String
Dim strSQL As String
 
    strSQL = "SELECT * FROM temp2.txt"
    strPath = "G:\share\credit\Credit MIS\MIS Secure\"
   
DoCmd.Hourglass True
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\share\credit\Credit MIS\MIS Secure\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
DoCmd.Hourglass False
 
Do Until rs.EOF
X = X + 1
    If Format(rs!CallDT, "hh:mm:ss") >= "00:00:00" And Format(rs!CallDT, "hh:mm:ss") < "04:00:00" Then
    rs!CallDT = Format(rs!CallDT, "Short Date") - 1
    rs.Update
        Else
        rs!CallDT = Format(rs!CallDT, "Short Date")
        rs.Update
    End If
rs.MoveNext
Loop
    
rs.Close
cn.Close
 
Set rs = Nothing
Set cn = Nothing
 
End Sub

So you know, I've also tried opening the recordset using these three methods:

rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic

What am I doing wrong?

Thanks in advance!

Strick

[RhinoBull]

... What am I doing wrong? ...

... where to begin ?..
Anyway, can you please explain first what is it that you need to do ...

[stricknyn]

Hello,

I am reading a field (long date format) in a text document and assigning it a value of Date - 1 if it is between 12:00 am and 4:00 am. I am then updating
the field with the Date - 1 value.

On a side note:
I know I can read it into a array and output it in the desired format, but I'd rather use ADO to just update the text file. Due to the size of the file, I run out of memory if I do this in Access. So I decided to just use VB to update the text's recordset.

Thanks

Strick

[RhinoBull]

Hmm ...
You cannot use JET (MS Access) OLEDB provider if you want to read text file: you must specify TEXT DRIVER ... here is a quick sample:

conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};DBQ=" & App.Path & ";", "", ""
RS.Open "select * from [test#txt]", conn, adOpenStatic, adLockReadOnly, adCmdText
'...