Coping Access tables

Phil_Tremper 

I'm sorry, I thought I explained that this design (the one you have apparently acquired as an example?) is the standard approach.

All tables are in one and only one accdb (or mdb). The common term for this is "Back End".

All other objects, i.e. forms, reports, VBA, etc. are in another accdb. The common term for this is "Front End".

What can you do to prevent this from happening again? Don't try to make "copies" of tables in the Front End. You know you are looking at Linked Tables in a Front End in the two ways I previously illustrated in a screenshot. If you see the little arrow to the left of the table name, you are looking at a LINKED table, i.e. the physical table is in a different accdb. If you hover the cursor over the table name, the pop up shows you where that accdb is and what its name is. 

Go to that accdb to make any changes to the tables, such as copying one of them. Do not try to copy the linked tables. All you are doing is creating a new link to the same table.

If you are the only user and all you need is a single accdb with all of the tables and all of the interface objects, you can IMPORT the tables into a new accdb, and then IMPORT all of the forms, etc. into that same new accdb. 

I can't pass up the comment about going against logic. I'm sorry, but I don't quite understand what you mean by that, and by the reference to off-line backup files. It will make sense once you have gained enough experience with relational database applications in general, and Access in particular. I suggest, as previously, that you search out and study resources that explain and discuss the concept of a "split database" for Access.

An analogy I've used in the past is that you have something analogous to a small dry cleaners.

Customers come in to the front of the store where there is a counter, maybe some nice plants and a clerk to receive your dry cleaning.

The actual cleaning, though, happens in the back of the store where there are large tubs of cleaning fluids and machines to press clothes, and racks of clothing that has been previously cleaned. 

These two parts of the business are separated for efficiency. All of the customers clothes end up in the back during processing, but they come into and out of the business one at a time through the front.

Front and Back. Lots of other situations have analogous designs. A nice interface through which users interact, but a large, loud, noisy back room where the work gets done.

Or maybe a better analogy is a fast food place where there are multiple order takers, each interacting with one customer at a time, but all orders go back in the kitchen where all of the cooking, wrapping and assembly are done. One back end with multiple front ends.

You have two mdbs (and they could be accdbs). One is the back end where the data is. The other is the front end where the interface is. You can make all of the copies of the front end you want, but all of those copies point back to that one, single, back end.

If you need to change something IN THE BACK END, i.e. you want to make a copy of a real table and change the records in it, you can't do that in the front end. 

Phil_Tremper 

"The data in that table was also deleted."

Actually, if I am right, and I am reasonably sure I am now, that WAS THE ONLY DATA. The other "tables" you have in the Front End, called Horses.mdb and the copy of it you called Horses_Old.mdb were only "placeholders" that pointed to that real table with the data.

The way it works is that there is a table (or set of tables) in a file. The older format was mdb, but the current format is accdb. There are significant differences, but they are very similar.

Anyway, there is one table (or set of tables) in the "_be.mdb" The interface to that data, which is the forms, reports and VBA, are NOT in the same mdb. They are moved into a separate mdb, which is called the Front End, or FE. However, in a well designed relational database application, that data must be shared among multiple users. So each user gets a copy of the FE containing forms, reports, queries, VBA, etc. And each copy of the FE has links, not real tables, but links to the real tables.

Each of your users gets a copy of that FE, and they all link to that single set of tables in the BE, so they can all share and use it.

In order to make that scheme work, there has to be a way for the FE to link to the data in the tables. We call these "linked tables". They have the same names as the real tables in the BE. The only thing they contain "for reals" though is the information on how to get to the real tables and their data. They contain NO data themselves. Every copy of the FE, on each person's computer, has the same links, of course, all pointing to the real tables in the BE. 

The thing of importance to what happened here is that the linked tablees can have ANY name you want to use. You made copies with different names, but all of those copies point to the one real table in the BE. When you delete data using any one of those linked tables, you are deleting the real data from the real table in the BE. All the linked table does is reflect that change. 

So you thought you were deleting records in one table, but you were deleting records in a different table, the real table in the BE unfortunately.