[AccessD] Access97 and recordlocking

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




More information about the AccessD mailing list