Jim Dettman
jimdettman at earthlink.net
Mon Jun 9 13:47:39 CDT 2003
Jeanine,
<<Is there another better way to do it?>>
Simply do:
Set AddRS = db.OpenRecordset("tblInstallPeople")
<<would you be able to look at the code below and tell me where
yours' is better?>>
a. Your try the operation again without waiting.
b. You do nothing to try and clear any locks that may be outstanding by
the current process.
With those two things, I'm not surprised your getting concurrency issues.
Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jeanine Scott
Sent: Monday, June 09, 2003 1:15 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access97 and recordlocking
Here's the code I am using - I open an empty recordset to perform the add.
Is there another better way to do it?
stradd = "Select * From tblInstallPeople where 1=0"
Set AddRS = db.OpenRecordset(stradd)
AddRS.AddNew
AddRS("PeopleID") = Me![cbpeople].Value
AddRS("InstallID") = Me![txtInstallID].Value
AddRS("EffDt") = Date
AddRS("EnteredBy") = varUser
AddRS.Update
AddRS.Close
SET ADDRS = nothing
Also, your locking code looks great. We have something similar whenever we
do inserts - would you be able to look at the code below and tell me where
yours' is better? I think it is - I just don't know enough about it to tell
why or how and I don't want to change what we are doing if I'm not sure it
will provide additional functionality.
This function is called whenever we run an action query. Thank you so much!
Public Function GetRSWrite(ByVal sql As String)
'***************************************************************************
**************************************
'CREATED BY:
'CREATED DATE: 09/10/01
'PURPOSE: gets and keeps db name, used for updateable recordsets such as
deletes, updates, etc. Rollsback on error
'---------------------------------------------------------------------------
--------------------------------------
'MODIFIED BY:
'MODIFIED DATE: 10/28/02
'MODIFICATION DESC: Added error numbers 3260 and 3186
'---------------------------------------------------------------------------
--------------------------------------
'MODIFIED BY:
'MODIFIED DATE: 5-28-03
'MODIFICATION DESC:
'***************************************************************************
**************************************
On Error Resume Next
Dim rs As Recordset
Dim i As Integer
i = 0
If gdb Is Nothing Then
Set gdb = DBEngine(0)(0)
End If
gdb.Execute sql, dbFailOnError
GetRSWrite = gdb.RecordsAffected
Do Until Err = 0 Or i = 10
'MsgBox Err
'MsgBox Err.Description
i = i + 1
Select Case Err
Case 3051, 3075, 3186, 3187, 3197, 3218, 3260, 3264 'Error
multi-user issues
Err = 0 'Reset Error and try again
gdb.Execute sql, dbFailOnError
Case Else
GoTo errorhandler
Exit Do
End Select
Loop
If i = 10 Then
GoTo errorhandler
End If
Exit Function
errorhandler:
Dim strErrorDescription As String
strErrorDescription = Err.Description & " SQL=" & sql
gstrSource = "basCommon"
gstrActiveControl = "GetRSWrite"
Call ErrorTrapping(strErrorDescription, Err.Number, gstrSource,
gstrActiveControl)
End Function
Jeanine Scott
Sr. Systems Analyst
Spindustry Systems
515-669-2074
jscott at spindustry.com
CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is
for the sole use of the intended recipient(s) and may contain confidential
information. Any unauthorized review, use, disclosure, or distribution is
prohibited. If you are not the intended recipient, please contact the sender
by reply e-mail and destroy all copies of the original message including any
attachments.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman
Sent: Monday, June 09, 2003 11:51 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access97 and recordlocking
Jeanine,
You certainly don't need to even bother with generating an empty recordset
if all you want to do is add. I would suggest placing the add in a
transaction and adding appropriate error trapping. Below is an example of
some error trapping that makes 5 attempts to do an operation, then quits if
it still won't go.
HTH,
Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net
GetRecordKeyError:
'Table locked by another user?
If Err = CNT_ERR_RESERVED Or Err = CNT_ERR_COULDNT_UPDATE Or Err =
CNT_ERR_OTHER Then
intLockCount = intLockCount + 1
If intLockCount > 5 Then
GetRecordKey = Null
Resume GetRecordKeyExit
Else
DoEvents
DBEngine.Idle DB_FREELOCKS
lngWait = intLockCount ^ 2 * Int(Rnd * 20 + 5)
For lngX = 1 To lngWait
DoEvents
Next lngX
Resume
End If
Else
UnexpectedError ModuleName, Routine, Version, Err, Error$
GetRecordKey = Null
Resume GetRecordKeyExit
End If
'Error constants
Const CNT_ERR_RESERVED = 3000
Const CNT_ERR_COULDNT_UPDATE = 3260
Const CNT_ERR_OTHER = 3262
Const CNT_ERR_NO_CURRENT_ROW = 3021
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jeanine Scott
Sent: Monday, June 09, 2003 12:40 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access97 and recordlocking
Yes, that's exactly what I need since I want to add a record to the table.
However, I seem to be getting a lot of recordlocking errors when attempting
to add the record to the table.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust
Sent: Monday, June 09, 2003 11:42 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Access97 and recordlocking
That SQL will return an empty recordset. Is that what you intended?
Charlotte Foust
-----Original Message-----
From: Jeanine Scott [mailto:jscott at mchsi.com]
Sent: Monday, June 09, 2003 8:30 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Access97 and recordlocking
If I pull a recordset like this:
"Select * from tblTest where 1=0"
how does the recordlocking work?
I understand Access97's disadvantage of page locking rather than single
record locking; however, I'm having a lot of issues where users are
getting kicked out because of record locking errors but I can't see how
this above SQL cause the table to be locked.
I'm using a recordset pull rather than a direct SQL insert because I
have to insert several records to the same table at one time. I do not
want to do a separate db call for each of the records because the LAN
speed is just horrible between the FE and the BE.
Thanks!!
Jeanine
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte
Foust
Sent: Monday, June 09, 2003 11:28 AM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Static Function Tutorial (was Like Operator
SQLSyntax)
It would be very unusual for me to want ALL the variables in a routine
to be static. That's why I never use static functions. I prefer to use
static variables in standard functions when I need a static value.
Charlotte Foust
-----Original Message-----
From: Gustav Brock [mailto:gustav at cactus.dk]
Sent: Sunday, June 08, 2003 11:34 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Static Function Tutorial (was Like Operator
SQLSyntax)
Hi Arthur
> .. By default all variables in a static function are static variables.
> You could declare a static variable in a non-static function, but I
> don't know why you would.
Well, to free you from resetting those variables which are not to be
used as static variables ...
/gustav
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com