Currently using MS Access 2000 and SQL Server Express. Using the
current DAO OpenRecordset code listed below, however I keep getting
the error message....
3254: ODBC --Cannot lock all records
The code below runs in a MS Access frontend while the table is linked
using an ODBC connected to an SQL Server (backend). The table
(located in SQL) only has one row and one column within it . I want
to try and stop any other request (other frontend clients) from being
able to access the record while someone is getting the case number (a
unique number).
This processed worked while all the tables were located in MS Access,
but does not work since the tables have been upgraded. While in MS
Access, all new request to access the table was given an chance to get
a number once the previous client call was completed [would loop them
in the error section until the table was released]
However just not sure if the SQL table is setup wrong, or I need to
use a different type of option within the DAO openRecordset command.
Can't upgrade the system to ADO, not enough time....
Would be thankful to any help that could be provided.....Here is the
code:
Set CaseRst = CurrentDb.OpenRecordset("SELECT CaseNumber FROM
tblCaseNumber", dbOpenDynaset, dbDenyWrite, dbPessimistic)
CaseRst.edit
With CaseRst
'Get Case Number then Increase by one
CaseNumber = CaseRst![CaseNumber]
![CaseNumber] = CaseNumber + 1
.UPDATE
End With
CaseRst.Close
Exit_CaseNumber:
Exit Function
Err_CaseNumber:
'If caserst is locked - Program comes here
Select Case Err
Case 3197
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
& Chr(10) _
& "Access Error: " & Err.Number & ": " &
Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
"Serious Error"
End If
Case 3260
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
& Chr(10) _
& "Access Error: " & Err.Number & ": " &
Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
"Serious Error"
End If
Case 3186
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
& Chr(10) _
& "Access Error: " & Err.Number & ": " &
Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
"Serious Error"
End If
Case 3262
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " &
Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13)
& Chr(10) _
& "Access Error: " & Err.Number & ": " &
Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added
To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13)
& Chr(13) _
& "You Will Have To Press The Button Again", vbCritical,
"Serious Error"
End If
Case Else
FormattedMsgBox Err.Number & ": " & Err.Description,
vbCritical + vbOKOnly, "Function: CaseNumber"
Resume Exit_CaseNumber
End Select 6 6182
Use ADO instead of ODBC when interfacing between Access and Sql Server.
Disclaimer: There are people in this NG who seem to have a problem
either with using ADO or with me suggesting to use ADO over ODBC. My
reply here is based on years of experience using ADO instead of ODBC for
manipulating data on a sql server from Access. I have had significantly
less problems with ADO than with ODBC (and even less than that with
ADO.Net except that ADO.Net only works in a .Net environment).
Here is some sample usage (need to make a reference in Tools/References
to Microsoft ActiveX Data Objects 2.x Library -- need 2.5 or higher):
Sub readFromSqlSvr()
Dim cmd As New ADODB.Command, RS As New ADODB.Recordset
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;Trusted_Connection= Yes"
'--or
'--cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSvr;Database=yourDB;UID=Steve;password= tiger;"
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select * from yourTbl"
Set RS = cmd.Execute
Do While Not RS.EOF
Debug.Print RS(0)
Loop
cmd.ActiveConnection.Close
End Sub
Rich
*** Sent via Developersdex http://www.developersdex.com ***
On Wed, 23 Jul 2008 15:12:00 -0700 (PDT), Mark <mm********@gmail.com>
wrote:
Take out ", dbDenyWrite, dbPessimistic" and try again.
Of course you realize you can do this with a simple SQL statement in a
tiny fraction of the time:
update tblCaseNumber
set CaseNumber = CaseNumber+1
-Tom.
>Currently using MS Access 2000 and SQL Server Express. Using the current DAO OpenRecordset code listed below, however I keep getting the error message.... 3254: ODBC --Cannot lock all records
The code below runs in a MS Access frontend while the table is linked using an ODBC connected to an SQL Server (backend). The table (located in SQL) only has one row and one column within it . I want to try and stop any other request (other frontend clients) from being able to access the record while someone is getting the case number (a unique number).
This processed worked while all the tables were located in MS Access, but does not work since the tables have been upgraded. While in MS Access, all new request to access the table was given an chance to get a number once the previous client call was completed [would loop them in the error section until the table was released]
However just not sure if the SQL table is setup wrong, or I need to use a different type of option within the DAO openRecordset command. Can't upgrade the system to ADO, not enough time....
Would be thankful to any help that could be provided.....Here is the code:
Set CaseRst = CurrentDb.OpenRecordset("SELECT CaseNumber FROM tblCaseNumber", dbOpenDynaset, dbDenyWrite, dbPessimistic)
CaseRst.edit
With CaseRst 'Get Case Number then Increase by one
CaseNumber = CaseRst![CaseNumber]
![CaseNumber] = CaseNumber + 1
.UPDATE
End With CaseRst.Close
Exit_CaseNumber:
Exit Function
Err_CaseNumber: 'If caserst is locked - Program comes here
Select Case Err
Case 3197
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " & Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13) & Chr(10) _
& "Access Error: " & Err.Number & ": " & Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13) & Chr(13) _
& "You Will Have To Press The Button Again", vbCritical, "Serious Error"
End If
Case 3260
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " & Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13) & Chr(10) _
& "Access Error: " & Err.Number & ": " & Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13) & Chr(13) _
& "You Will Have To Press The Button Again", vbCritical, "Serious Error"
End If
Case 3186
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " & Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13) & Chr(10) _
& "Access Error: " & Err.Number & ": " & Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13) & Chr(13) _
& "You Will Have To Press The Button Again", vbCritical, "Serious Error"
End If
Case 3262
intLockRetry = intLockRetry + 1
If intLockRetry < LOCK_RETRY_MAX Then
For i = 0 To intLockRetry * 1000
Next i
Resume
Else
ErrorMsg = "Date: " & Date & Chr(13) & Chr(10) & "Time: " & Time & Chr(13) & Chr(10) _
& "Possible Case Number Lost: " & CaseNumber & Chr(13) & Chr(10) _
& "Access Error: " & Err.Number & ": " & Err.Description
FormattedMsgBox "Case Number: " & CaseNumber & " Was NOT Added To The Database. Please Inform " _
& "Your Supervisor The Case Number, Date & Time" & Chr(13) & Chr(13) _
& "You Will Have To Press The Button Again", vbCritical, "Serious Error"
End If
Case Else
FormattedMsgBox Err.Number & ": " & Err.Description, vbCritical + vbOKOnly, "Function: CaseNumber"
Resume Exit_CaseNumber
End Select
On Wed, 23 Jul 2008 17:49:23 -0500, Rich P <rp*****@aol.comwrote:
Out of curiosity:
* If you think ADO is superior when working with a SqlServer backend,
would you not use ADP, which is a more pure ADO environment?
* If using MDB + ADO, how do you use bound forms? Me.RecordSource =
rs?
Thanks,
-Tom.
Microsoft Access MVP
>Use ADO instead of ODBC when interfacing between Access and Sql Server.
Disclaimer: There are people in this NG who seem to have a problem either with using ADO or with me suggesting to use ADO over ODBC. My reply here is based on years of experience using ADO instead of ODBC for manipulating data on a sql server from Access. I have had significantly less problems with ADO than with ODBC (and even less than that with ADO.Net except that ADO.Net only works in a .Net environment).
<clip>
On Jul 24, 12:06*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Wed, 23 Jul 2008 15:12:00 -0700 (PDT), Mark <mmazur7...@gmail.com>
wrote:
Take out ", dbDenyWrite, dbPessimistic" and try again.
Of course you realize you can do this with a simple SQL statement in a
tiny fraction of the time:
update tblCaseNumber
* set CaseNumber = CaseNumber+1
-Tom.
Hey Tom, not sure what you mean by this
Are saying that the code would look like this? And it would lock the
record so that no one else could access it?
Set CaseRst = CurrentDb.OpenRecordset("SELECT CaseNumber FROM
tblCaseNumber", dbOpenDynaset)
update tblCaseNumber
set CaseNumber = CaseNumber+1
CaseRst.Close
On Thu, 24 Jul 2008 05:08:54 -0700 (PDT), Mark <mm********@gmail.com>
wrote:
No, you can't open a recordset on an Action query like an Update
query.
Currentdb.Execute strSQL, dbFailOnError
It will run so fast locking is not needed.
-Tom.
Microsoft Access MVP
>On Jul 24, 12:06*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
>On Wed, 23 Jul 2008 15:12:00 -0700 (PDT), Mark <mmazur7...@gmail.com> wrote:
Take out ", dbDenyWrite, dbPessimistic" and try again.
Of course you realize you can do this with a simple SQL statement in a tiny fraction of the time: update tblCaseNumber * set CaseNumber = CaseNumber+1
-Tom.
Hey Tom, not sure what you mean by this
Are saying that the code would look like this? And it would lock the record so that no one else could access it?
Set CaseRst = CurrentDb.OpenRecordset("SELECT CaseNumber FROM tblCaseNumber", dbOpenDynaset)
update tblCaseNumber
set CaseNumber = CaseNumber+1 CaseRst.Close
>>
Out of curiosity:
* If you think ADO is superior when working with a SqlServer backend,
would you not use ADP, which is a more pure ADO environment?
* If using MDB + ADO, how do you use bound forms? Me.RecordSource =
rs?
Thanks,
-Tom.
Microsoft Access MVP
<<
Also based on years of experience, I have had more issues with ADP's
than with mdb's. If I have a large project that uses an ADP - I will
just migrate the whole thing over to .Net. But for smaller projects
which still use a sql server for the backend and Access for the
frontend, I find that using an mdb is easier to develop than the ADP -
plus you are in the native Access environment and only need ADO for
pulling/pushing and carrying out data edits.
The mdb is more like a pseudo .net app in that with ADO you are using
disconnected recordsets like in .Net -- except that instead of doing
everything in memory you do have I/O on the disk. But mdb tables
function pretty much the same as .Net dataTables - again - except for
the part where the mdb tables are persistent (written to the disk) and
the .Net tables are all in memory only. And with this configuation -
you can still use DAO within the mdb. There is no DAO with the ADP.
Rich
*** Sent via Developersdex http://www.developersdex.com *** This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: xixi |
last post by:
hi, we are using db2 udb v8.1 ESE with type 4 jcc driver. since DB2
can't support pessimistic locking , and our application required that,
which means when both users try to access the same record...
|
by: Gary Nelson |
last post by:
Anyone have any idea why this code does not work?
FileOpen(1, "c:\JUNK\MYTEST.TXT", OpenMode.Binary,
OpenAccess.ReadWrite, OpenShare.Shared)
Dim X As Integer
For X = 1 To 26
FilePut(1, Chr(X +...
|
by: Joe |
last post by:
Hi,
I have an asp.net page that opens up a connection to MS access DB and does insert into DB. I am using close() and setting the connection object to Nothing after I am done with inserting a...
|
by: Komandur Kannan |
last post by:
We have a smart device application running on handhelds(Symbol MC9000G). The
backend is Oracle and a middle tier web services development done in Vb.net.
We use pessimistic Locking due to...
|
by: adri4n |
last post by:
as wat ive mentioned in the title.. im would like to know whether the a
particular record/table is being locked in my program. some of the
methods which i would like to develop are as below:
...
|
by: HDI |
last post by:
Hi,
I'm writing a windows application where users can update records but
how can I lock a record when a user opens it.
With ado and vb6 you can lock the opened record but with ado.net I...
|
by: prakashwadhwani |
last post by:
The Delete Event/Proc & "Save_Fields_In_Form_Header" Event/Proc in
my form were working perfectly.
However, after I added a call to the "Save_Fields_In_Form_Header"
Event/Proc in the...
|
by: zmickle |
last post by:
Excuse my noobness. I am managing an access database that is shared
by 4 users. Management does not want to use any technologies outside
of access for this application (no SQL Server, etc). I...
|
by: Sam Lambson |
last post by:
Hi,
This is similar to another post, because I still haven't gotten to the
bottom of it.
I have a back end database on a shared drive. Each (of 4) user has a
front end file on their own C:...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |