Two forms need access to same table

[Guest]

This application consists of a number of tables and forms. Two of the forms access the "products" table. It is necessary to switch between these two forms on a regular basis, but there is no need to keep form1 open when using form2

Form 1 has a command button to switch to form 2. The VB code that runs when this button is clicked simply closes form 1, opens form 2 and makes the "new record" record active

When it gets to the line that opens form 2, the error

"the table "products" is already opened exclusively by another user, or it is already open in the user interface and cannot be manipulated programmatically.

What needs to be done so form 1 releases the "products" table so form 2 can access it?

 

[Allen Browne]

From the Tools menu, choose Options.
On the Advanced tab, set "Default Record Locking" to "No Locks".

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This application consists of a number of tables and forms. Two of the

forms access the "products" table. It is necessary to switch between these
two forms on a regular basis, but there is no need to keep form1 open when
using form2.

Form 1 has a command button to switch to form 2. The VB code that runs

when this button is clicked simply closes form 1, opens form 2 and makes the
"new record" record active.

When it gets to the line that opens form 2, the error:

"the table "products" is already opened exclusively by another user, or it

is already open in the user interface and cannot be manipulated
programmatically."

What needs to be done so form 1 releases the "products" table so form 2

can access it?

 

[Guest]

Default record locking is already set to no locks

Default open mode is shared. Open databases unit record level locking is not checked

Any other ideas?

 

[Allen Browne]

If there are memo fields in this table, they may be the cause of the locking
problem.

Presumably you are ensuring that Form1 is not dirty at the time you open
form 2.

There is an issue solved by Jet 4 Service Patch 8. Locate msjet40.dll
(usually in windows\system32), right-click and check the version. If it is
less than 4.0.8015.0, go to support.microsoft.com and download from the
Service Packs section.

 

[Guest]

Allen

I updated the msjet40.dll as you suggested, but no change in behavior

I am not sure what you mean by "dirty" If this means changes to records that have not been written, then form1 is not dirty. Opening form1 and then immediately clicking the command button on form1 to go to form2 still results in the table already open error

Is there any way I can send you the database to take a look at? It is small, only about 80K zipped

This is Access 2000 build 9.0.3821 SR-1 running under win2K

Mik

----- Allen Browne wrote: ----

If there are memo fields in this table, they may be the cause of the lockin
problem

Presumably you are ensuring that Form1 is not dirty at the time you ope
form 2

There is an issue solved by Jet 4 Service Patch 8. Locate msjet40.dl
(usually in windows\system32), right-click and check the version. If it i
less than 4.0.8015.0, go to support.microsoft.com and download from th
Service Packs section

 

[Allen Browne]

Email me at allenbrowne at mvps dot org.

Please include this thread.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I updated the msjet40.dll as you suggested, but no change in behavior.

I am not sure what you mean by "dirty" If this means changes to records

that have not been written, then form1 is not dirty. Opening form1 and then
immediately clicking the command button on form1 to go to form2 still
results in the table already open error.

 

[msboblinux]

Please let me know if you have found a fix. I am having the same problem and it is driving me nuts. It almost looks like a timing issue to me, like the one form hasn't completely closed the table before the second form tries opening it. Is there a way in VBA to make it "wait" long enough for the table to be closed?