Forum Discussion
Linking a frontend to a backend
arnel_gpThanks for your response, arnel. Your advice worked on my testset. Now I will work on the FE with some 20 linked tables.
George_HepworthThanks for your reaction, George! It gives me the chance to further discuss it: I do not understand the merit of the additional tables added with the number attached in stead of replacing the original BE's path and name with the new one, as it was done in earlier versions of Access. Does anybody know Microsoft's idea about how this additional table could be used?
Also, it would be a good help when the expalantions of the linked table manager would include Arnel's solution when you want to relink to an other BE.
It's always been this way. If you add a link to a table with the same name as an existing name, Access adds it as a second table with the incrementing suffix.
I can't imagine the chaos that would result from replacing a link to one table with a link using the same name when adding a new table. Let's say, for example, you have two copies of a back end. One is for development and you are the only one who links to it for maintaining and adding new functions. The other is an accdb with the same set of tables, but it is used for production. You have the same named tables in both development and production, to minimize work required to manage both.
Now, one fine day you decide its time to replace the links from development to production for final testing and deploying. All goes well and you deploy the accdb with the links to production for use by your users.
Then you move on to the next development task, but instead of replacing production links to ALL tables, you miss one and leave it linked to the real, active production table, while relinking all of the other tables in the development back end. Your plan is in place, the names stay the same. so you have no warning that this happened.
Away you go, makes changes and adding and deleting data in what you think is the development back end. All except that one table, which is still linked to production.
And you start getting phone calls from users wondering what in the world happened to their data? You just overwrote production data with test data in development.
I can think of several other disastrous scenarios that could happen if Access did not maintain that practice of adding new links every time it encounters an alias of the same name.
No, you do NOT want to simply link to the same name and have Access silently replace the linked tables with the same name. You want that warning which tells you there are two different links to tables with the same name. That could be the same table in the same accdb, true. But it could be two different tables in two different accdbs which just happen to have the same name.
That wasn't a very good example, I'm afraid.
I want to try again.
The scenario is similar, with a different step.
What happens is this: ADDING a linked table is different from REFRESHING, or RELINKING a table.
If you relink, by definition, you are using the same aliased name in Access to point to a different table, either in the same back end accdb or another back end accdb. That's where the idea of a Production vs Development accdb comes in. You can tell Access to relink one or the other using the same aliased name in the Navigation Pane.
When you ADD a linked table, though, you are telling Access "In addition to all of the existing tables you know about, use this one too." That's when the incrementing suffix comes into play. Access know about
tblOne, which is a link to a table in the production accdb. When you ADD another tblOne, Access is not about to overwrite that production accdb because it's more convenient to re-use and existing alias. It knows better. You said you want to ADD a table, so it adds it. However, it can't use the same name. It has to come up with a new one and it does, "tblOne1". It's up to you to sort it out after that.
If you let Access overwrite existing table names when adding production or development tables, without explicating saying, "Relink this name to a different table", you would never know for sure what mixture of Production and Development tables happened to be in play with examining each table's connection string, one at a time.
George_HepworthMany thanks for your abundant information! I now understand the good reasons for the behavior of the database.
I realized that the problem I hit was not due to the fact that I started to work on Microsoft 365, but that I had converted the databases from mdb to accdb.
Thanks again.
