Linked table on a split database? (1 Viewer)

stell

Registered User.
Local time
Today, 00:59
Joined
Jun 14, 2017
Messages
15
Not sure if this is the best section to post this in, but I'm hoping someone here can help me out.

I have a split database that I have implemented, have about 3-4 people using it. One of the tables in this database is a linked table with excel. The problem I am having is that it only allows one person to run queries involving this excel table.

I'm getting the following error message.

"The Microsoft Access database engine cannot open or write to file ********.XLSX. It is already opened exclusively by another user, or you need permission to view and write it's data"

Is there any way to work around this? I would like every user to be able to run these reports/queries.

Thank you.
 

Minty

AWF VIP
Local time
Today, 07:59
Joined
Jul 26, 2013
Messages
10,355
Not easily - as soon as the spreadsheet is opened by Access you can't open it again in Access.

Have you considered importing the data in it?
 

stell

Registered User.
Local time
Today, 00:59
Joined
Jun 14, 2017
Messages
15
Not easily - as soon as the spreadsheet is opened by Access you can't open it again in Access.

Have you considered importing the data in it?

I have not. Would that involve VBA? I'm pretty novice in that area. This excel spreadsheet is being constantly updated so I would need the access table to update with it.
 

Minty

AWF VIP
Local time
Today, 07:59
Joined
Jul 26, 2013
Messages
10,355
To do it regularly without users getting their hands dirty would involve VBA, but depending on the data and what you need to do with it, it needn't be overly complicated.

You could simply link to it and run a background process that updated a table copy of it to then run your queries against that table? Then the spreadsheet would only be locked periodically when the update was being run.
 

stell

Registered User.
Local time
Today, 00:59
Joined
Jun 14, 2017
Messages
15
You could simply link to it and run a background process that updated a table copy of it to then run your queries against that table? Then the spreadsheet would only be locked periodically when the update was being run.

Could you elaborate a little on that? I had the idea to just run the 'ImportExportSpreadsheet' macro and turn it into a local table, but haven't been able to quite get it.

Really appreciate the help.
 

Users who are viewing this thread

Top Bottom