Access db opens read only when linked Excel file is open

[ffrippy]

Hi,

I have an Excel spreadsheet which extracts and formats info from an Access DB for reporting purposes etc., it refreshes on open and is available to all users. However, when the excel file is open the Access DB opens read-only and the 'Disable Shift&Click' Function fails to work. This means that if anyone opens the spreadsheet and leaves it open no-one can use the DB.

Has anyone else seen this problem and is there a way around it?

Many Thanks in advance ffripp

[twinnyfo]

I am just trying to think of ways around the refreshing Excel Spreadsheet. I know how important "up to the minute" data can be, but in this case, is it required? If not, then it could be refreshed daily. I try to avoid any possible situations in which my DB will be locked.

However, I would try to find a way that instead of using a spreadsheet linked to the DB, that you devise a way that users could download and save a fresh, unlinked copy whenever they needed it.

I have not experienced this issue, as I have not used Excel in this way, I have only linked to spreadsheets that are read only in Access.

Others here may have some better ideas.

[ffrippy]

Thanks for your reply twinnyfo,

having the spreadsheet refresh on open is very convenient, especially as it allows me to provide different individuals with different views of the data as we operate a 'Need to Know'system. I thought that maybe there was a way to refresh on open and then drop the connection, which seems logical but I can't spot an option for it. I also tried dropping the 'Refresh on Open' and 'Enable Background Refresh' which is fine until you do a refresh then it just keeps the connection again.

ffripp

I might have to look at getting access to create and email reports automatically each day.

[zmbd]

This has to do with how the connection was made in EXCEL... it opens the database in exclusive mode by default. This is due to the fact normally the data isn't set up to refresh automatically.


What you need to do (in office 2010):
Close the related database and Access otherwise changes will not take.

Ribbon>Data>Connections

In the dialog box select the connection and then [Properties]

Now in the {Usage} tab I like to select
Enable Background refresh
Refresh every (the time is up to you... be careful though, too often and the end-users start marching with farm implements and tourches)
Refresh data when opening file

{Definition} tab
This is the pain...
The connection string is like the registry... you break it you bought it.
Search thru the string until you find something like this
(if the database says you can't make changes then:)
"Mode=Share Deny Write"
or
(if you are told the database is already in use:)
"Mode=Share Deny Read|Share Deny Write"

Now be careful here...
"Mode=Share Deny None"

Close back to the Connections manager dialog.
There's a refresh button there... click it.
now close the dialog.
Refresh once on the worksheet level (not really needed... I just like to click buttons... elelator-go-up...elelator-go-down wheeeeeeee (@_@)

This will allow you to open the database and make changes to the information that may not show up in the worksheet until the refresh. Depending on how you setup the other options... changes to the workbook may or may not write back to the database.

[ffrippy]

Thanks for that zmbd,

However, I've just found a much simpler answer....in Excel under the 'DATA' tab, rather than use 'Get External Data/from Access', tab use the 'Get External Data/From Other Sources' option, then select 'From Microsoft Query'. Using this option has two major advantages, firstly you get all the same options as the 'From Access' option but it doesn't hold the connection, so problem solved there. Then secondly, you get to be able to select individual fields from the table or query, where the 'From Access option only allows you to select the entire table or query.

Thanks both for your help..

ffripp

[zmbd]

ah yes... however that wasn't your question (^_^)

As for the connection option requiring the full table or query... you design and store the query in Access to restrict the returned data with option you're going to use you design and store the query in excel... same net effect. (^_^)