2003 database opening as Exclusive in 2007

[Adrian Jones]

Hi,

I have a database (mdb) which is currently in Access 2003 format, which I
have just started using with Access 2007.

There is also an Excel 2003 spreadsheet which links to several queries in
the database.

If I try to refresh the data in excel, it tells me that the database is
opened exclusively and can't be read.

Whether I double-click on the icon, or select it from the Open dialog, it
always opens as exclusive. And, yes, under Access Options | Advanced |
Advanced, Default Open Mode is set to Shared.

As this needs to be used by more than one person (as well as populating the
spreadsheet), it must be opened as shared. But I can't see a way to force
Access to open it as such.

Has anyone else had experience of this? And hopefully found a solution?

Thanks,

Adrian

 

[Arvin Meyer [MVP]]

Excel has no mechanism to allow multiple people to edit the same file.
Access being a true database, does. The only solution that I can think of is
to import the data into Access instead of linking to the spreadsheet.

 

[Adrian Jones]

I'm not trying to get two users to open an Excel database.

The data is already in the Access database. The Excel file links to the data
in the database.

In 2007, Excel tells me that the database has been opened exclusively and
can't read the data. In 2003, there wasn't a problem, Excel read the data,
Access opened the database in shared mode.

Adrian

 

[Arvin Meyer [MVP]]

I'm not trying to get two users to open an Excel database.

The data is already in the Access database. The Excel file links to the
data
in the database.

Actually, you are. As long as the data is linked, instead of being imported,
it is in Excel, not Access. Access can only lock native Access tables. It
cannot lock anything more in Excel than the entire file. That's the same
behavior that any user would have in Excel.

If you want to lock the data for multiple users, you must import it into
Access, not link it. And there cannot be any link to the Excel spreadsheet.