Update Visual FoxPro 6 table using ADO

[NW]

Can someone help me with updating a VFP 6 table using ADO please? This is the code I'm using at present and it appears to returns a Read-only recordset? Also is there a way to limit no of records to return as my Select seems take quite some time for larger table?
Thanks
NW

Private Sub Form_Load()
Dim strCnn As String
strCnn = "SourceType=DBF; " & _
"SourceDB=y:\customer\sarm\;" & _
"Driver={Microsoft Visual FoxPro Driver}"
Set cn = New ADODB.Connection
cn.Open strCnn
End Sub

Private Sub cmdUpdate_Click()
Dim strSQL as String
Dim rs As New ADODB.Recordset
strSQL = "SELECT * FROM C:\test where Trim(Field1)='" & Trim(Text1.Text) & "'"
rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

If rs.RecordCount = 0 Then
rs.Fields("Field2&quot.Value = "NEW Value"
rs.Update
Else
MsgBox "No records returned!"
Endif
End Sub

 

[Sawatzky]

Dear NW,

performance:

If you know the list of field you want to fetch , list them in your select statement.
Select field1,field2 from source
this should be a little bit faster

readonly:

I am not sure about this, but try to open your recordset with the option
adOpenDynamic .


Also is there a way to limit no of records to return as my Select seems take quite some time for larger table:
there is a TOP 10 sql statement, but i am not sure whether this works with the fox pro driver, would be like this
SELECT TOP 10 field1, field2 FROM source (which fetches the top 10 records)

But in fact it would be better, if you have another possibility to hold your resultset small, for example with a where clause
WHERE key > thelastyouwanttoget

Hope this helps

regards Astrid