What types of links makes a db open in exclusive mode?

[Autoeng]

I have a db that I'm fighting with to get it to open in shared mode. Currently it's a link to an unupdateable Excel spreadsheet that's causing the problem but I have some ODBC links to unupdateable AS400 data. Does anyone know all of the types of data that cause a db to open in exclusive mode?

 

[Pat Hartman]

The linked tables are not what causes the db to open in exclusive mode. The two major culprets are insufficient network permissions that are preventing the creation of the .ldb file and a user making modifications to db objects such as forms and reports.

In order for Jet to manage the sharing of a db, it needs to be able to create a locking file in the same directory as the db. This file has the same name as the db with an extension of .ldb. To create this file, the user needs create permission in the db's directory. If a user without this permission opens the db, Jet will prevent any other user from opening the db.

The other issue is that A2K and newer do not support object changes when the db is opened by more than one user unless you have installed a source code management program such as SourceSafe.

I'm assuming that you have investigated the obvious problem of opening the db and selecting exclusive as part of the open dialog.

 

[Autoeng]

Hi Pat:

Glad you answered because I was using a previous response of yours as my basis.

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=45879&highlight=link

Access opens all non-tables in exclusive mode because it needs to be able to allow you to update them. [\quote]

I am getting the same error message that the Excel file is not available.

Have I misinterpreted what you are saying? In my db I pull from 3 tables that are Access resident, 4 ODBC AS400 linked tables and 1 linked excel spreadsheet. All of these are pulled together in a query which my form is based upon.

The db is set to shared and I am just double clicking the shortcut so I'm really perplexed as to what could be the problem.

I tried to TransferSpreadsheet import but I get error that the "Operation must use updateable query". If I have the owner of the other db do a TransferSpreadsheet export on db close then I could do a TransferSpreadsheet import?

 

[Pat Hartman]

Non-table objects are things like forms and reports. Linking an excel file may be a problem because it is a DOS file and sharing is under the control of the operating system rather than Jet. So if you use this file in a query and it is already in use by someone else, you might get the "updatable query" message. When the other user exports the spreadsheet, Access may not let go of it immediately and that may be what is causing the "updatable query" message. DOS may have the file locked and that is why you cannot access it.

Have you investigated the network permissions issues? Even linking to tables in another db will cause the .ldb file to be created or modified when you access the tables. So, not only do your users need add/change/delete/read permissions to your db's directory, they need the same permissions to the directory of the linked db. Various versions of Windows and Novell handle this differently. You will need to work with your network admin to get this resolved.

 

[Autoeng]

Finally got it!

My problem was that I wasn't deleting the table before I tried to TransferSpreadsheet!