VB 6.0 / Visual FoxPro 6.0 problem

[bobdeemer]

I have tried everything, but I cannot seem to figure out why this
problem is happening (it could be that I am a newbie with VB and
FoxPro

The application resides on the server of a LAN (Win 9x) and interacts
with
Visual FoxPro tables (which also reside on the LAN). When the
application is executed on the the server, everything runs flawlessly.
However, when I run the application from a workstation (also Win 9x)
I cannot
update the data in the database. When executed from the workstation,
it will connect and query the database without errors. The program
will set the variables to what I want, but when I try to update the
database via the "rstProduct.Update" command, I get a "Run Time Error
2147467259 (80004005) Cannot Update Cursor". Again, if the program is
executed on the server, everything works flawlessly.

I have tried both server-side and client-side cursor locations, as
well as
adOpenKeyset and adOpenStatic cursor types (for server-side cursors)
with various types of locking. Nothing seems to work. Does anybody
have any ideas?

One note: I am able to change the CursorLocation to either
adUseServer or adUseClient and it is changed accordingly. However, no
matter what I do, the cursor type is ALWAYS adOpenStatic, no matter
what CursorType I try to use.

Here is the relavant code:

---> In the code module


Public conConnect As ADODB.Connection
Public rstProduct As ADODB.Recordset
Public strDBpath As String
Public strProductTable As String
Public strServerDrive As String


------> In the form event

strBarCode = Trim(txtBarCode.Text)

If strBarCode = "" Then
lblStatus.Caption = "Cannot Add Blank UPC Code!"
Else
lblStatus.Caption = ""
If (Len(strBarCode) > 12) Then
strBarCode = Right$(strBarCode, 12)
End If

strSQL = "SELECT * FROM " & strProductTable & " where upc='" &
strBarCode & "'"

Set conConnect = New ADODB.Connection
Set rstProduct = New ADODB.Recordset

conConnect.CursorLocation = adUseClient
rstProduct.CursorLocation = adUseClient

conConnect.Open "DSN=Visual FoxPro Database;UID=;PWD=;SourceDB=" &
strServerDrive & ":" & strDBpath &
";SourceType=DBC;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"

rstProduct.Open strSQL, conConnect, adOpenStatic,
adLockOptimistic, adCmdText

If rstProduct.RecordCount = 0 Then
' do irrelevant code here
ElseIf rstProduct.RecordCount = 1 Then
lngInvCartons = rstProduct!qty
lngInvCartons = lngInvCartons + lblScanQty.Caption
rstProduct!qty = lngInvCartons

rstProduct.Update ' this gives the runtime error on the
workstations, but not on the server.

' more irrelevant code after this

End if
End If

-Bob

[DNAunion2000]

... no matter what I do, the cursor type is ALWAYS adOpenStatic, no matter what CursorType I try to use.

...

rstProduct.Open strSQL, conConnect, adOpenStatic, adLockOptimistic, adCmdText

...

I found this about ADOc (ADO classic, not ADO.NET)...it might help.

"In this case, only the static CursorType is supported, and updatability is only provided if you choose adLockBatchOptimistic as the LockType." (William R. Vaugh, ADO.NET and ADO Examples and Best Practices for VB Programmers: Second Edition, Apress, 2002, p133)