Splitting a Database - Sort of

[Paul H]

I have a certain few tables I want to move to a back end, but the Database Splitter takes them all and makes them linked tables. I may want to do this, but not yet. I am still in development and this would cause some recurring testing problems.

So, I create a back end and import the chosen tables. Then when I go to my front end I want to replace my existing table with a new linked table, Access won't let me do so without deleting the original along with all of its relationships, something I don't want to do.

Any ideas?

Paul

[RayMilhon]

Link the table with the access supplied name usually the same name with a 1 For example:

Original table: MyTable
Linked Table: MyTable1

Then with nothing open in the database window under tables rename MyTable to oldmytable and rename linked table to Mytable. Now all of your code, queries, reports are linked to the linked table.

[Paul H]

I still have to delete all my relationships with this table to do this.,but I have found a solution that works for me. Since there are only 3 data tables involved I discovered that the existing relationships in the back end database stick. I can then relink the remaining static tables which don't require referential integrity. This works for me, but I wonder about other larger databases with scores of tables and relationships. That would be a bear.

I'm rethinking the whole selective database splitting process. Are there any good reasons NOT to put all your tables in the back end? Is it seemless?

[RayMilhon]

In my experience I have never found a good reason Not to put all of the tables in the backend. I've never had an issue with doing that. I can't speak for anyone else but that is my opinion. Anybody else feel differently?

[Paul H]

I'm leaning in that direction pending performance testing. I've been told your database will run faster if you put the data tables in the back end and leave the static tables in the front end.

[RayMilhon]

I've never noticed that much of a difference but I've always worked for relatively small companies. The largest was 500 employees and network traffic was never much of an issue.

[Paul H]

I'm going to mark this solved. Thanks for the advice.