Row Level Locking from Excel VBA query

I'm trying to run a query from Excel VBA with SQL using Jet 4.0 to an
access database. There are multiple users accessing the database at
the same time, and I am trying to set up row level locking in the
query that I am using so that two users will not get the same order
that is waiting in queue. Here's the applicable code I'm using:

Dim dbsConn As ADODB.Connection
Set dbsConn = New ADODB.Connection
Dim myDBname As String

myDBname = "T:\SOC\LNP1\Call center errors\LNP Data Track Back End
Tables\LNP Data Track_be.mdb"
BCNPCNDate = Date - 7
LNPUserID = Range("AK1").Value


Dim connString As String
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
myDBname & "; Jet OLEDB:Database Locking Mode = 1;User Id=" &
LNPUserID & "; Persist Security Info=false; Jet OLEDB:System
database=T:\SOC\LNP1\Call center errors\LNP Data Track Back End Tables
\LNP Data Track_be.mdw"

dbsConn.Open connString

Dim rs As Recordset
Dim strSELECT As String, strSELECT1 As String, strSELECT2 As
String
Dim rSELECT As String

Set rs = New ADODB.Recordset
rs.LockType = adLockPessimistic
rs.CursorType = adOpenDynamic

LNPSpecName = Range("AK1").Value


strSELECT5 = "SELECT TOP 1 * from [RES LNP Production
Table] [READCOMMITED, XLOCK, READPAST] WHERE [LNPSpecName] is NULL
ORDER BY [LSRSentDate];"
' New/Unassigned orders


rs.Open strSELECT5, dbsConn, , , adCmdTableDirect

If rs.EOF Then
rs.Close
'do nothing
Else

With rs
.Fields("LNPSpecName").Value = LNPSpecName
.Fields("LNPProcessDate").Value = Date
.Fields("LNPProcessTime").Value = Time
.Update
End With

MsgBox ("New Order")
OrderType = "New Order"

GoTo PullValuesIn
End If



Unfortunately, any time two users query the database for a new order
at about the same time, they receive the same record. Can anyone tell
me what I'm missing? Why isn't READPAST ignoring the locked records?

.

Relevant Pages

  • Re: running fRefreshLinks from another database
    ... lock the database I want to update links in. ... Dim i As Integer, strDBPath As String, strTbl As String ... Dim dbCurr As Database, dbLink As Database ...
    (microsoft.public.access.modulesdaovba)
  • Re: Changing the background color and re-linking the backend when
    ... Essentially what I needed was to replace every reference to tblMember to be replaced to tblMemberUS in one Access database and to tblMemberNU in another Access database. ... Dim strDbName As String ... Function RefreshLinksBeSef() As Boolean ...
    (microsoft.public.access.formscoding)
  • Re: A VB 6 program to read from and save to text file
    ... I wrote a small program which for you, which imports the data from your text file into a database and displays them in a DBGrid. ... Const OldFile As String = "Datafile.txt.old" ... Dim MyData As String ... Kill MyPath & OldFile ...
    (microsoft.public.vb.winapi)
  • Re: DAO takes too much time to link tables
    ... I am trying to link some tables to a back-end database. ... 'Link the tables contained at the given query. ... Dim rst As DAO.Recordset ... Dim success As Boolean ...
    (microsoft.public.access.modulesdaovba)
  • RE: Multi select List box to filter query for editing
    ... Dim mFilter As String ... ' Complete string for filter to apply to query ...
    (microsoft.public.access.formscoding)