MS Query to Excel file sources: Read Only Error

chrism216

Board Regular
Joined
Aug 6, 2013
Messages
211
Hello everyone.

I've been trying to set up a series of workbooks for different departments to dump data into. I then consolidate them into a file that I use for reporting purposes. All files are in the same directory in a network folder.

I can't use Access for several reasons. Instead, I'm using MS Query for this, using SELECT and UNION ALL statements, with a few sub-queries here and there.

The source workbooks have to be able to remain open so people are able to edit data. I also have to be able to query it at any given time from the master workbook.
It works fine at first. I test my queries and they return all the data I need. Updates in source workbooks are passed through to the consolidated report once people save them. But after I save all workbooks, close and open everything, the query in the master workbook fails to update, with the error: "Cannot update. Database or object is read-only".

I've researched this for two days now... can't find anything. The best I've gotten is that it might have something to do with database locks. It also might have something to do with how complicated the query is (subqueries and stuff). It might have something to do with the query wizard or the connection string (already tried to change ReadOnly property to 0 in connection string... Nothing).

The point is, I'm completely lost here, and for the first time in a long time, the internet isn't helping me at all (only confusing me, actually!).

If anyone has encountered this issue, I'd be really greateful for a bit of help.

Cheers,

Chris

EDIT: Please don't suggest alternative ways of doing this... I'm trying to make this work as it is right now... This is a tool I have to build for demonstration purposes and if it works, I'll get a budget for something better.
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Forum statistics

Threads
1,176,239
Messages
5,902,096
Members
434,940
Latest member
Suresh877

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top