Normally I have all my questions answerred with topics from Stack Overflow, but now I ran into some difficulty to which I cannot find me a working answer.
Brief explanation what I am trying to achieve with the following setup:
I have setup a database in MS Access (which basically is a SQL query that links some Excel sources into one large table which i want to use in a pivottable)
I have a pivottable in Excel which I have connected to this MS Database by doing the following, it is working perfectly despite one minor issue I want to have resolved.
In MS Access:
-Import Excel-sheets and Create linked-tables to the data source
-Create query where I have combined the Excel-tables with UNION (not UNION all).
[opening this query works like a charm, i have the Excel sheets correctly combined in one dataset]
In MS Excel:
-Data > Get External Data > From Other Sources > From Microsoft Query > MS Access Database > OK > database.accdb [read-only on/off*] > AccessTable > OKOKOK
-Insert PivotTable > Use external datasource > choose connection > AccessTable
[situation: MS Access is unopened, I refresh PivotTable, Excel uses query of MS Access to reload all data from Excel sheets, it works perfectly when all Excel files are closed = no users have the Excel sources open]
Problem:
-MS Excel: when users have the data source Excels open, upon refreshing the pivot, Excel opens all Read-only files are are open by other users. I do not want this to happen. I want the connection to treat the files as read-only, if opened, just use data that has not yet been saved by user. I do not want Excel to open all the open documents as Read only.
When having an Excel data source open, and I have MS Access open, then i get the following alert. However, this does not limit the query to refresh the data. For me, this currently is no problem, just some other symptom.
-MS Access: "The Microsoft Office Access database engine cannot open or write to the file ''. It is already opened exclusively by another user, or you need permission to view and write its data"
What have i already attempted:
-https://bytes.com/topic/access/answers/957940-access-db-opens-read-only-when-linked-excel-file-open
[this solution does not work in my situation, i have the following error: "no value given for one or more required parameters mode share deny none" or "no value given for one or more required parameters mode read".]
-some vba code that stops opening other read-only files, but this is only patching some symptom instead of solving the root cause.
I am very curious what other possibilities I have to overcome this.