Connection to Visual Foxpro 6 tables Error Message

[Jimgarry]

Hi, thanks in advance. Im attempting to get data out of a VFP 6 table. I make the connection but while running through a recordset I get this message
"Data provider or oter service returned an e_fail status"

here is the code Im using

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strSql As String
Dim strCnString As String
Dim cnt As Integer

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset


strCnString = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=c:\stromberg;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine"
cn.Open strCnString
strSql = "Select * from jtm2040.dbf"

With rs
If .State Then .Close
.CursorLocation = adUseClient
.Open strSql, cn, adOpenStatic, adLockBatchOptimistic
.ActiveConnection = Nothing
End With

cnt = rs.RecordCount
Do Until rs.EOF
MsgBox rs.Fields("Name&quot & " " & rs.Fields("Number&quot
rs.MoveNext
Loop
End Sub


If I block out the cnt portion I get about 5 or 6 records before the error shows up.

Any suggestions. Just want to pull the data from the vfp file and put into a recordset

Thanks again
Jim

 

[salvatoredoria]

check out if that table is part of a fox pro datbase (.dbc)
In that case you have to connecto to the ".dbc" file with ado

 

[Jimgarry]

Thanks but no its not attached to a .dbc I found the problem is in the with RS

it needs to be:
With rs
If .State Then .Close

.Open strSql, cn, adOpenStatic, adLockBatchOptimistic

The Cursor and Active Connection had to be removed. then it worked great

.CursorLocation = adUseClient
.ActiveConnection = Nothing

Thanks for your suggestion though
Jim