Updating records using ADO

[NW]

How can I update a record in a FoxPro table using ADO?
So that all other users in the network can see latest changes?

Is there a way to find out if a record been changed (after loading the recordset)
before issue an "Update"?

This is my coding to connect to this dbf, Can someone tell me where I need changing please?

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Private Sub Form_Load()
Dim strCnn, sSQL As String

Set cn = New ADODB.Connection
strCnn = "SourceType=DBF; " & _
"SourceDB=\\data\custdata;" & _
"Driver={Microsoft Visual FoxPro Driver};" & _
"Exclusive=no"
Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.Open strCnn

sSQL = "SELECT * FROM CUSTdatx "
sSQL = sSQL & " WHERE Len(Trim(ActiveCust))>0"

Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open sSQL, cn, adOpenStatic, adLockOptimistic, adCmdUnknown
End Sub

 

[MikeCox]

It looks like you have a connection ok, so if you need to change a field's value and update you can do this:
-------------------------------
rs.Fields("MyFieldName&quot.Value = NewValue
rs.Update
-------------------------------

To find out wheather a record has been changed, you'll probably have to use a dirty flag variable and set it whenever a change has been made. If you're NOT using bound controls, and your control array indexes are synchronized with the recordset fields collection, you can put code in the Validate event to set the dirty flag (DataChanged):

---------------------------------
Private Sub txtFields_Validate(Index As Integer, Cancel As Boolean)
If IsNull(Adodc1.Recordset.Fields(Index).Value) = False Then
DataChanged = txtFields(Index).Text <> Adodc1.Recordset.Fields(Index).Value
Else 'Field is null, if text is not then DataChanged = True
DataChanged = txtFields(Index).Text <> &quot;&quot;
End If
End Sub

----------------------------------------

With Adodc1 (adLockOptimistic), if someone makes a change to a record that has already been read by another user, then the other user tries to make a change and commit, he will see an error. The error is something like &quot;specified row could not be located for updating. Some of the values may have changed since it was last read.&quot; That's the only way I know of to determine wheather another user has made a change to the recordset you have open, so if you have to you can trap for this in an error handler.

FYI: Multi-user collision Err.Number is -2147467259

Hope that helps!

~Mike
Any man willing to sacrifice liberty for security deserves neither liberty nor security.

-Ben Franklin

 

[NW]

Thanks for your input Mike.
You're absoulately correct. I am getting the same error &quot;specified row could not...&quot; when I try to update it. Is there any way to show latest changes to all users, when this occors?
Thanks for your time.

 

[varnit]

hi...

u can update that record by just adding the sql statement for update... and the command is like this..

set rs=cn.execute(<SQL STATEMENT>)

Good Luck
Varnit

 

[MikeCox]

One thing you can do when you hit this error is call Adodc1.Refresh, which will re-query the database based on the CommandType you've set up at design time (i.e. adCmdText or adCmdTable). Unfortunately this means that the updates the user was trying to make will be lost when the recordset gets repopulated (not sure there's a lot you can do about that), and obviously there's not much flexibility in reverting back to your design time query, but in a lot of cases this can be sufficient. Otherwise you can try to cancel the error (with Err.Clear), then requery the database with your own sql statement to read the newest changes.

If you anticipate these kind of collisions will be frequent, you may want to consider other LockTypes. adLockPessimistic will allow you to put the recordset in Edit mode, which locks the current record from any other users, and then commit or roll back the transaction as required. This way instead of having to recover from the collision, you can simply inform the user that the record is being edited by someone else, and to try again later.

Hope that helps!

~Mike
Any man willing to sacrifice liberty for security deserves neither liberty nor security.

-Ben Franklin