SOLVED

Linking a frontend to a backend

Copper Contributor

In older versions of msaccess I could link my tables in the backend to the tables in the fronteend. In Microsoft 365 I will get links to tables with a number attached to it. All my VBA, queries and forms look to the unnumbered tablenames.

Please help me to connect to the original tablenames

 

7 Replies
just maybe there is Already an existing linked/local table with same name that is why when you create a New Linked table it Renumber it.
make a copy of your FE.
on the Copy (FE), remove all linked table and try to Create new link.
best response confirmed by VicInUtrecht (Copper Contributor)
Solution

@arnel_gpThanks for your response, arnel. Your advice worked on my testset. Now I will work on the FE with some 20 linked tables.

Keep in mind that the "linked tables" you see in the Navigation Pane are not the actual tables. They are the names for the connections set up to those actual tables in the back end accdb.

Therefore, the names you see there are aliases for the real tables. You can rename them at will in the Navigation Pane and not have any effect on the names of those real tables where they reside in the back end accdb.
In this case, there was an existing linked table alias, e.g. "tblProduct" or something. When you added a second link to the same back end table, Access used the same alias, but appended the suffix "1", indicating it is a second copy of something with the same name. Both aliases, though, link to the same table in the back end accdb.

@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.

@VicInUtrecht 

 

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.

 

 

@George Hepworth 

 

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.

1 best response

Accepted Solutions
best response confirmed by VicInUtrecht (Copper Contributor)
Solution

@arnel_gpThanks for your response, arnel. Your advice worked on my testset. Now I will work on the FE with some 20 linked tables.

View solution in original post