Lock a Table

[Guest]

Hi All,

Is there a convenient way to programatically allow the current user to have
full access to a table in the current db, while preventing other users from
accessing it until the program releases it?

 

[Guest]

There are millions of ways of doing this, mac.
One way is to note that the OpenRecordset command has a dbDenyWrite option
that will allow you to prevent others from modifying or adding records.
Simply open the recordset with a set rstA = CurrentdB.OpenRecordSet(
"tablename", dbOpenTable, dbDenyWrite)
Make sure that your recordset is in a global module.
Once you are ready to allow the users to use the table, simply do a
rstA.Close command.

 

[Guest]

Thanks Larry,

I should probably be more specific. The table is used for temp storage of
records that will be accessed by another application (MsWord via merge
mailing). A form in the database is bound to the table. When the form opens
it invokes a public procedure that deletes all records in the table and
rebuilds it from scratch. After that proc returns, the form requiries the
table to get the latest data. The user can further modify records if
desired. The form also calls other procs to carry out a variety of utility
functions on the table while the form is open. When the form is closed, the
table is ready to be used in the mail merge.

I want to make sure that no other user can manipulate data in the table
while the current user has it. But I do not want the bound form to lock it
in a way that would prevent the various procs that it calls from opening and
using the table. If the form is opened specifying all records locking, the
public procedure can't open a recordset from the table.

The procs I wrote use ADO to access the table. Perhaps this complicates the
approach I will have to use.

Thanks again,
Mac

 

[Guest]

Are you using a "split" database where users access all the tables in a
separate mdb file?
If not, then consider doing so (Tools --> Database Utilities --> Database
Splitter), and place a copy of the front-end on each user's drive. Then
create a local table that other users do not have access to.
After you are done manipulating it, you can use the TransferDatabase method
or an Update query to update the actual table that Word will be using.

 

[Guest]

Good idea. I'm still adding features to this application, but do intend to
split it as it nears final form, so I appreciate the suggestion. Many Thanks.

-Mac