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.....He re is the
code:
Set CaseRst = CurrentDb.OpenR ecordset("SELEC T 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  6223 
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.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourSvr; Database=yourDB ;Trusted_Connec tion=Yes"
'--or
'--cmd.ActiveConne ction = "Provider=SQLOL EDB; Data
Source=yourSvr; Database=yourDB ;UID=Steve;pass word=tiger;"
cmd.ActiveConne ction.CursorLoc ation = adUseClient
cmd.CommandType = adCmdText
cmd.CommandText = "Select * from yourTbl"
Set RS = cmd.Execute
Do While Not RS.EOF
Debug.Print RS(0)
Loop
cmd.ActiveConne ction.Close
End Sub
Rich
*** Sent via Developersdex http://www.developersdex.com ***
On Wed, 23 Jul 2008 15:12:00 -0700 (PDT), Mark <mm********@gma il.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.....He re is the
code:
Set CaseRst = CurrentDb.OpenR ecordset("SELEC T 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.Clos e
Exit_CaseNumbe r:
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.Descriptio n
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.Descriptio n
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.Descriptio n
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.Descriptio n
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.co mwrote:
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...@gma il.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.OpenR ecordset("SELEC T CaseNumber FROM
tblCaseNumber", dbOpenDynaset)
update tblCaseNumber
set CaseNumber = CaseNumber+1
CaseRst.Close
On Thu, 24 Jul 2008 05:08:54 -0700 (PDT), Mark <mm********@gma il.com>
wrote:
No, you can't open a recordset on an Action query like an Update
query.
Currentdb.Execu te 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...@gma il.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.OpenR ecordset("SELEC T CaseNumber FROM
tblCaseNumber" , dbOpenDynaset)
update tblCaseNumber
set CaseNumber = CaseNumber+1
CaseRst.Clos e
>>
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 with intent
to update, the first one grab the record will create a row lock on the
current row reading, before it update, other user can't access the
same record with intent for update read, but can access with read only
type. so when the first...
|
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 + 64))
Next
Lock(1, 5, 10)
|
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 record. After inserting when I look at the server where the MS access DB resides I also see a *.ldb file also. For some reason I cannot get rid of this *.ldb file even after I close the connection to DB from my asp.net page. As long as this *.ldb file is...
|
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 specific business needs
When such a lock is made for a transaction (this code is in the web service)
and the handhelds are terminated abnormally (cold booted or battery low),
the lock still remains. The Web service's connection to oracle is still...
|
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:
Lock(KEY, SQLCODE)
- to create a lock. Returns 0 on success, 1 if a lock already exists
and -1 on failure with SQLCODE. There is no necessity to use IsLock()
for checking if a lock exists or not, just insert the record and check
for
| | |
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
don't know how to do this.
I am using vb.net and SQL Server.
|
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 "Current" Event of my form, and I now try to Delete
a record, I get ... Run-Time Error 3218 - Could not Update; Currently
Locked. My Access application then effectively freezes forcing me to
shut Access down & re-start.
The record DOES get deleted...
|
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 have split the
database into front end and back end databases and put the front end
on each of the users' computers. All users can open the front end
simultaneously, but once somebody updates data in the form, the back
end locks up.
What 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: drive. The main form that users have
is bound to a query which merges 2 linked tables. It is not an action
query. When the query is executed, everyone still has access to the
form/query, until somebody makes an update. Or so it seems.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
