Union Queries when Splitting Database

[JayPoppin]

Hi,

I am trying to split a fairly complicated database (~10 tables, with ~10 enforced relationships, etcetera). A big part of the design is a union query that mediates between two tables, into a third.

I need to split the database so that I can distribute the front end around to other users. In order to split, I believe I have to delete the union query. Part of me thinks I shouldn't have to delete it, however, when I try to split the database I get the following error:

"The Microsoft Jet Database engine could not find the object 'project assignments'. Make sure the object exists and that you spell its name and the path name correctly."

So... is (a) there a way to split the database without deleting the union query, or (b), if not, should I just go back and remake the union query after the split ? How exactly does that work? It only need be in the front end?

Thanks,

Jay

 

[DCrake]

Make a copy of the database in the first instance.

Next create a blank database which will become the back end.

Next open the back end and import all the tables from the original database

And also make sure you impor the relationships as well as this is where they should reside.

Next reopen the original database and delete all the relationships and then the tables - leave the union query alone for now as is.

Then using the external data option link the tables from the new back end to the new front end.

You should now have a split FE - BE setup

Try and open the union query it should be ok.

 

[boblarson]

I almost think that it is bad that the Database Splitter is in Access to begin with. It only seems to complicate things when the steps that David have given are so, so easy to accomplish.

 

[JayPoppin]

So did what you suggested.

There are two problems:

(A) I wasn't given the option (or don't know how) to import relationships to the new front end. Though they appear to have been imported. EXCEPT that the table/query produced by the union query no longer shows up in design view, and the relationships it has with other tables no longer appear as well.

(B) Though I imported the union query, when I try to open it on the new front end, I am given the following error:

"The Microsoft Jet database engine cannot find the input table or query 'Project Assignments' [name of union query]. Make sure it exists and that its name is spelled correctly."

Do you guys have any other ideas?

Thanks so much,

Jay

 

[JayPoppin]

Sorry, I didn't do exactly as you said. Give me a second.

 

[JayPoppin]

What is meant by "import relationships"? Is this a separate operation?

 

[JayPoppin]

Nevermind, thanks so much this was perfect.