Hi everyone ... this is my first post...
INTRODUCTION
I am an old time programmer who has been pushed into writting a Sales, Purchasing, stock, etc system in Access2007.

The last time I programmed anything was 2001 (remember Y2K).

I have done quite a lot of programming in VB4 and VB6 for stand alone programs using SAM files but ADO database stuff is all new to me.

BACKGROUND
The current situation is theat I have all the modules in place.
The edit/update forms are UNBOUND using VBA to read the records and populate the control.
The write functions read the control value and update the database.
Today I have taken my single database file and split between Back-End and Front-End.
We do not have a Server, just a networked disk-drive.
Everyhing has worked and three users can access and update the Back-End database simutaneously.

**** BIG PROBLEM ****
I am using the structure below to read and lock the records.
This code DOES NOT APPEAR TO LOCK THE RECORD.
In that I can update the same record on two PCs and trample over the first record to be updated.
If I use a bound screen I do get a warning on the second write.

Can anyone give me any pointers or is it just that I can lock the records unless we use a Server?
'================================================= =========================================
Function aisSOPOrderHdrADOReadRecord() As Boolean
Dim conAlexIS As ADODB.Connection
Set conAlexIS = CurrentProject.Connection
Dim rstRecord As New ADODB.Recordset
Dim strSQL As String
Dim intErrId As Integer
intErrId = 0
aisSOPOrderHdrADOReadRecord = True

On Error GoTo ErrorHandler
intErrId = 1
'establish the connection to the table
rstRecord.ActiveConnection = conAlexIS

intErrId = 2
'open the table via a query
strSQL = "SELECT * FROM tblSOPOrderHdr" _
& " WHERE [Order] = " & Me.txtOrder.Value & ""
rstRecord.Open strSQL _
, conAlexIS _
, adOpenDynamic _
, adLockPessimistic
' I have also tried using adLockOptimistic
intErrId = 3
If rstRecord.EOF Then
aisSOPOrderHdrADOReadRecord = False
Else
aisSOPOrderHdrADOReadRecord = True
intErrId = 4
Me.txtOrder.Value = rstRecord![Order]
Me.txtAcntNo.Value = rstRecord![AcntNo]
: : : :
: : : :
Me.txtVATRate.Value = rstRecord![VATRate] * 100#
Me.txtVATAmount.Value = rstRecord![VATAmount]
Me.cmbStatus.Value = rstRecord![Status]
End If ' If rstRecord.EOF Then...
MsgBox "Record Read - Waiting", vbOKOnly, "{aisSOPOrderHdrADOReadRecord}"
intErrId = 4
rstRecord.Close

Set rstRecord = Nothing
Set conAlexIS = Nothing
intErrId = -1
Exit Function
ErrorHandler:
Select Case Err.Number
Case 94 ' invalid use of null
' No Action Required
Resume Next
Case Else
Call UnresolvedError(Me.Name, "{aisSOPOrderHdr ADOReadRecord}", intErrId, "Failed")
aisSOPOrderHdrADOReadRecord = False
End Select
End Function
'================================================= ============================================
Thanks in anticipation

There is a lot of extra code in there it seems. I would try this at least:

If you are using CurrentProject.Connection as your connection string, you don't need to declare or set it, just state it in the recordset.open line. Then VBA will autosense your cursor and lock.

Generally, I will use a separate ADO connection (DSN or Provider) for UPDATE, INSERT, DELETE on records, and only use CurrentProject.Connection when using SELECT statements. I think there is a problem with locking, especially with Jet. If you link SQL Server tables, you can only reliably use SELECT with CurrentProject.Connection.

Also, you can do this:

Did that help?

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Hi Sorry for the long delay in replying... been pulled on and off the project

I have tried you suggestions but still have the problem.
According to the online help...

adLockPessimistic
Data cannot be changed by other users while locked
also Prevents other users from accessing data while locked

adLockOptimistic
Other users able to access data.
also Data can be changed by multiple users at once

I tried both lock settings and niether managed to lock the record against update by another user.

If you open the table in access and try to update the record on the second PC the record is locked, but this is not recognized by the ADO routines.

I also tried using ADO perssimistic locks on both PCs and could quite happily trample over the other users updates.
The result is that which ever record is written last is saved in the database.

I haven't tried using DAO.

As there are relatively few screens/forms I need to protect I will create a lock table, just to lock the primary record key e.g. Order No, GRN No, etc., in the database and control the locking myself.

Thanks for your help I will let you know if I find a way to use ADO reliably.

Stuart