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.
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:
