All righty, then, I did a little digging around. Here are the things affecting filtering and your where clause:
1. As you probably know, a csv file may or may not have the field names of the table in the first row. Your connection string says "HDR=No", which means to not treat the first row as the field names. If there are field names in the first row, they will be treated as the data in the first record, unless you say HDR=Yes. Conversely, if you say HDR=Yes and the first row isn't the field names, the first record in your table will be missing from the table and will instead be the field names of the table.
So, assuming that you have this right, and the source csv does not have field names in the first row: HDR=No assigns the field names F1, F2, F3 and so on. You can use these names in the where clause, forget about the filter. (You could use it there too.)
To use the following test code, I first went into Access, and exported the Customers table from the Northwind database to C:\Temp\Customers.txt. I played around with a file that has headers and a file that doesn't, and also with the Hdr setting in your connectionstring. You can use .Fields
.Name to find the names of fields.
Code:
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Public Sub DoStuff()
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
Dim cLine As String
Dim i As Integer
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
With rs
.Open "Select * from customers.txt where F1 = 'ALFKI'", cn
Do Until .EOF
cLine = .Fields(0)
For i = 1 To .Fields.Count - 1
cLine = cLine & vbTab & .Fields(i)
Next i
Debug.Print cLine
.MoveNext
Loop
End With
rs.Close
cn.Close
End Sub
Private Sub Command1_Click()
DoStuff
End Sub
So, being on a roll as I were, I thought I would give this a try:
Code:
Private Sub DoUpdate()
Dim x As Long
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
cmd.CommandText = "update customers.txt set f1 = 'ALFIE' where f1 = 'ALFKI'"
cmd.ActiveConnection = cn
cmd.Execute x
MsgBox x & " Records affected."
End Sub
This raises an error when trying to run cmd.Execute:
Code:
'In the debug window
? err.source & " " & vbobjecterror - err.Number & " " & err.Description
Microsoft JET Database Engine 245755 Updating data in a linked table is not supported by this ISAM.
One of Microsoft's clearer error descriptions, I would say. So, updating doesn't work directly.
So, I did some more digging around. The following code opens the Customers.txt file, saves it as an XML file, opens the xml file into the recordset, makes changes in the recordset, and saves the result in the same xml file. If you wanted to persist these updates to your text file, you could simply open the xml file into a recordset, and write code to write the recordset into a new text file.
Code:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=""text;HDR=No;FMT=Delimited"""
With rs
.CursorLocation = adUseClient
.Open "Select * from customers.txt", cn, , adLockBatchOptimistic
'add code here to delete target file if it exists; the following only raises an error
.Save "c:\temp\customers.xml", adPersistXML
.Close
.Open "c:\temp\customers.xml", "Provider=MSPersist", , adLockBatchOptimistic
.Filter = "F1 = 'ALFKI'"
!F1 = "ALFIE"
.Filter = "" 'Make sure you do this or you'll only save the records that pass the filter
'add code here to delete the xml file
'note that the Update method is unnecessary; the Save method simply translates the current state of the recordset into XML, so no underlying posting happens.
.Save ,adPersistXML
End With
The following section can be found in the resulting xml file:
Code:
<rs:update>
<rs:original>
<z:row F1='ALFKI' F2='Alfreds Futterkiste' F3='Maria Anders' F4='Sales Representative' F5='Obere Str. 57'
F6='Berlin' F8='12209' F9='Germany' F10='030-0074321' F11='030-0076545'/>
</rs:original>
<z:row F1='ALFIE'/>
</rs:update>
So, if you want to support updates, you could use ADO to open your text file, save it as an xml file, open the xml file, make changes to it and resave, and then convert the result to text. Kind of long-winded, of course. One of the reasons that xml is supplanting text as an interplatform data storage standard is that it's easier for a given platform to manipulate it, since it has a richer metadata context.
p. s. looks like Golom's right.
Bob
