[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