Recordset code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have read quite a few posts regarding recordset coding but I could use some
advise. I have an Access database for multiple users. I have a form that
has a couple of dozen recordsets that delete records in tables for that user
and updates the tables/queries. At certain times during the day, my users
encounter run-time errors but when I resume through the code, they are just
fine. My questions are:

1. Should I be using the recordset type: table, dynaset or snapshot. I
tried using table and it seems to have a negative impact. I could also use
some guidance on the recordset options and lockedits (dbPessimistic is the
default).

2. I am wondering whether I should use dbEngine(0) (0) or CurrentDb. I
know dbEngine is faster and since I have many users, I think maybe it would
help.

Private Sub Final()
Dim dbFinal As DAO.Database
Dim RsFinal As DAO.Recordset
Dim RsECOA As DAO.Recordset

'Set dbFinal = DBEngine(0)(0)
Set dbFinal = CurrentDb

dbFinal.Execute ("Delete * from tblFinal where strMachineName = '" &
mstrCompName & "' ")
Set RsFinal = dbFinal.OpenRecordset("Select * from tblFinalLetter",
dbOpenDynaset)
'Set RsFinal = dbFinal.OpenRecordset("tblFinalLetter", dbOpenTable,
dbOptimistic)

RsFinal.AddNew

'here is where I build the recordset

RsFinal.Update
RsFinal.Requery
RsFinal.Close
Set RsFinal = Nothing
dbFinal.Close
Set dbFinal = Nothing
End Sub
 
Bartman said:
I have read quite a few posts regarding recordset coding but I could use some
advise. I have an Access database for multiple users. I have a form that
has a couple of dozen recordsets that delete records in tables for that user
and updates the tables/queries. At certain times during the day, my users
encounter run-time errors but when I resume through the code, they are just
fine. My questions are:

1. Should I be using the recordset type: table, dynaset or snapshot. I
tried using table and it seems to have a negative impact. I could also use
some guidance on the recordset options and lockedits (dbPessimistic is the
default).

2. I am wondering whether I should use dbEngine(0) (0) or CurrentDb. I
know dbEngine is faster and since I have many users, I think maybe it would
help.

Private Sub Final()
Dim dbFinal As DAO.Database
Dim RsFinal As DAO.Recordset
Dim RsECOA As DAO.Recordset

'Set dbFinal = DBEngine(0)(0)
Set dbFinal = CurrentDb

dbFinal.Execute ("Delete * from tblFinal where strMachineName = '" &
mstrCompName & "' ")
Set RsFinal = dbFinal.OpenRecordset("Select * from tblFinal",
dbOpenDynaset)
'Set RsFinal = dbFinal.OpenRecordset("tblFinal", dbOpenTable,
dbOptimistic)

RsFinal.AddNew

'here is where I build the recordset

RsFinal.Update
RsFinal.Requery
RsFinal.Close
Set RsFinal = Nothing
dbFinal.Close
Set dbFinal = Nothing
End Sub
Click to expand...

02/04/2005 UPDATE:
I was reading in the Access 2000 Developer's Handbook by Ken Getz that
DBEngine(0)(0) CurrentDb is always up to date while DBEngine(0)(0) requires
the Refresh method which obviates any speed gains with DBEngine(0)(0). What
do the MVP's think about this?

Also, I modified my set code as follows:
Set RsFinal = dbFinal.OpenRecordset("Select Top 1 * from tblFinal",
dbOpenDynaset, dbAppendOnly And dbInconsistent, dbOptimistic)

Any suggestions?
 
Bartman said:
02/04/2005 UPDATE:
I was reading in the Access 2000 Developer's Handbook by Ken Getz that
DBEngine(0)(0) CurrentDb is always up to date while DBEngine(0)(0) requires
the Refresh method which obviates any speed gains with DBEngine(0)(0). What
do the MVP's think about this?

Also, I modified my set code as follows:
Set RsFinal = dbFinal.OpenRecordset("Select Top 1 * from tblFinal",
dbOpenDynaset, dbAppendOnly And dbInconsistent, dbOptimistic)
Click to expand...


The Refresh issue is certainly something to keep in mind.
But(?), you might want to think about balancing that with
how often you modify the things(?) that need to be
refreshed. OTOH, if you're not setting a db object in a
loop, who cares about saving a millisecond?

Sorry, but I can't clarify the locking issue other than to
say that if you're updating the data, you would normally use
a Dynaset, not a snapshot.

Also, you have an error in the expression:
dbAppendOnly AND dbInconsistent
The And operator will result in the value zero, you should
use the OR operator to combine multiple bit values.
 
Is your database split, with a copy of the front end installed on each
workstation and a shared back end on the server?
 
02/07/05:
John,

It is an mde on a shared network drive and each user has a shortcut on their
desktop to the program. I only experience the problem with multiple users on
the shared network drive. Our PCS department is pursing it as a network
bandwith issue but they are not confident that it will help. I just want to
make sure it is not a code problem. I changed all my various recordset code
as noted below hoping the additional properties would help but it didn't seem
to make a difference:

Set RsFinal = dbFinal.OpenRecordset("Select Top 1 * from tblFinal",
dbOpenDynaset, dbAppendOnly Or dbInconsistent, dbOptimistic)

Again, resuming through the code always works but it is causing significant
delays. I would appreciate any advise.

Bart
 
Bart,

This kind of problem is much more likely to occur if you have multiple
users opening a single mdb file across a network. So are more serious
issues of database corruption.

Before worrying about your code and timing issues, you should split the
database and install a copy of the front end on each workstation, using
linked tables to access the data in a shared back end file on the
server. This will also impose somewhat less load on the network.
 
John,

I appreciate your help. My users are using a mde rather than a mdb. I
would rather not split the database and place the forms and reports on their
desktops because I make regular changes and in is not feasible for me to
update the desktops all the time. That is why I are having them use the
program on the network drive.

After reading the following:

http://msdn.microsoft.com/library/d...0/html/acconAvoidingDataLockingConflictsS.asp

I am wondering if we could update these properties to reduce the locking
problem we have when out 60+ users are trying to update the tables with their
various recordsets. They usually experience problems with the delete or set
code. At peek times during the day, sometimes it takes 30 minutes for the
the records to not be locked. Any advise on what values I should be using in
the following properties would be appreciated:

REFRESH INTERVAL (sec)
UPDATE RETRY INTERVAL (msec)
NUMBER OF UPDATE RETRIES
ODBC REFRESH INTERVAL (sec)

I experimented with changing the NUMBER OF UPDATE RETRIES to 10 but it
didn't seem to help.

Bart
 
Don't mean to harp on it since it isn't the solution you are looking for,
but you could automate that distribution to allow you to seperate the front
and back ends.

I currently distribute my front ends via login script. The login script
checks network directory A for updates and then
checks/creates/copies/whatever action the files to the users PC upon login.
So, after I make changes the only distribution I do is to copy it to network
directory A.
 
John,

I am also wondering if I am using the correct settings for locking:
Tools.Options.Advanced
Default Open Mode: Exclusive
Default record locking: All Records
Open databases isng record-level locking IS checked

I can't get information on the pros and cons of each option in a multi-user
environment. Thanks.

Bart
 
How complicated is it to do?

Bartman said:
John,

I appreciate your help. My users are using a mde rather than a mdb. I
would rather not split the database and place the forms and reports on their
desktops because I make regular changes and in is not feasible for me to
update the desktops all the time. That is why I are having them use the
program on the network drive.

After reading the following:

http://msdn.microsoft.com/library/d...0/html/acconAvoidingDataLockingConflictsS.asp

I am wondering if we could update these properties to reduce the locking
problem we have when out 60+ users are trying to update the tables with their
various recordsets. They usually experience problems with the delete or set
code. At peek times during the day, sometimes it takes 30 minutes for the
the records to not be locked. Any advise on what values I should be using in
the following properties would be appreciated:

REFRESH INTERVAL (sec)
UPDATE RETRY INTERVAL (msec)
NUMBER OF UPDATE RETRIES
ODBC REFRESH INTERVAL (sec)

I experimented with changing the NUMBER OF UPDATE RETRIES to 10 but it
didn't seem to help.

Bart
Click to expand...
 
There are various ways of automatically distributing front ends; see
e.g. Tony Toews's Auto FE Updater at www.granite.ab.ca. I urge you not
to share a single mde (or mdb) file across the network.

Even if you do that, however, if you have 60 simultaneous users actively
entering or editing data, you are probably hitting the limits of what
the Jet database engine and/or your network can support. Access/Jet can
support light use from quite large numbers of users, but heavy appends
or updates by more than a handful of simultaneous users do slow it down
a lot.

Tuning the locking parameters (something on which I'm not qualified to
advise) won't make a massive difference. For a serious increase in
performance you will need to move to a client/server structure, with a
server database engine such as SQL Server (powerful but expensive) or
MySQL (fewer features but no licence fees).
 
I am also wondering if I am using the correct settings for locking:
Tools.Options.Advanced
Click to expand...

The What's-This help on the Tools Options dialog explains these.
Default Open Mode: Exclusive
Click to expand...

This defaults to opening every data base in Exclusive mode, so no one
else can open it. IOW exactly what you don't want for shared use. But
maybe the desktop shortcut you're using overrides this.
Default record locking: All Records
Click to expand...

This will prevent any other user editing any record in the table or
datasheet's recordset. Probably not what you want; on the other hand
locking only the record currently being edited may not provide enough
protection.
Open databases isng record-level locking IS checked
Click to expand...

I'm not an expert on this at all, but AFAIK this reduces the chance of
conflicts by reducing the number of records locked, but imposes some
performance penalty.
I can't get information on the pros and cons of each
option in a multi-user environment.
Click to expand...

Record locking is only relevant in a multi-user environment. The idea is
to prevent multiple users making simultaneous incompatible updates to
the same data.

It's probably worth seeking help in microsoft.public.access.multiuser.
 
Back
Top