How to use a Query in another DB as a Form's RecordSource?

Question

I'm trying to split a database into two pieces -- a backend that updates automatically, and a front-end that allows searching and adding/editing comments. The data in the source database is pulled together from multiple tables into a pair of queries, and I want to use these queries as the source of the current database.

Access 2007 supports splitting a database into multiple pieces, but not in the way I'm looking for. It keeps the tables in the source database and puts all the forms, queries, reports, and macros into the new database. The tables and queries are already in the back-end, and this new database should just provide a good GUI to the end-user.

Access 2007 also supports linked tables, but these can only use a table as a source, not a query object.

I was thinking that the best way to do this would be to do a SQL query along the lines of

SELECT * FROM SourceQuery IN "C:\Path\To\ExternalDB.accdb";

Is what I'm working towards even possible, and would this be the best way to do it?

Since its still relatively early in the project, rearchitecting the database isn't out of the question, but is something I'd prefer to avoid.

Answer 1

Hope I'm understanding you correctly, but the most sensible solution would be to link the tables in the backend DB and copy the queries to the UI database. Those queries would still be able to access the uderlying tables (via the linked tables) without issues and would be accessible through normal means to your forms and VBA code.

Is there a particular reason you don't want the queries in the UI database?

Answer 2

You described the usual Access BE-FE division correctly: only tables in the back-end. I'm aware not all DB programs do it that way, but this is Access and my approach would be to honor the usual division. (And you hardly have a choice in that you can't "link to a query" in Access.)

Reviewing your comment ('There is a specific reason ...'), I think this would possibly mean

1. adding a few more tables to the back-end, essentially buckets (import-data in ready form; export 1; export 2) that allow all users to get to consistent processed data;
2. making a small admin FE that sits next to the BE and stores your modules, queries for export, and export routines; and
3. having some redundant queries on the user FE. This is vexing in my own work. I just try to design sturdy stable "building block" queries in those roles, and keep their number to a minimum.