Linked Table Not working

[chuckcoleman]

Hi. I built an Access database for myself to help me, (Chuck), manage business projects. I gave a copy to a friend of mine, (Ed), to allow him to do the same with his projects. The database uses a frontend and a backend. As with any database, the more you use it the more you see the need to make tweaks to it. I have two icons on my desktop, one for my data and one for his data. The icon's start a .cmd file that starts a .bat file. I'll call the backend file, be_data.accdb. The bat file has the ability to rename the be_data.accdb file to add either my name to it or Ed's. The frontend always expects be_data.accdb as a linked table. This has worked flawlessly for months.

Today, I simply added a new field to both be databases in what I call the Admin Table. It was an Attachment type of field. The field in both be files is identical. Now, when I open the frontend for myself, the Admin Table works fine. It's linked. If I closed the db and open it with Ed's data, I have to relink the Admin Table. If I don't, I get an error that it can't find the table or query. If I relink it, close it and go back to my data, same thing. I have to relink it.

I have created a blank database and imported all tables, queryies, etc into the new blank database. Same thing. No change. I'm going nuts trying to figure out why adding a new field has created this problem. Any ideas?

 

[theDBguy]

Hi. This may not be necessary, but maybe worth a try anyway. When you change the design of a table in the BE, try deleting the linked table and then relink the table again. This is to make sure you get a link to the updated table in the BE.

Just a thought...

 

[chuckcoleman]

Hi. This may not be necessary, but maybe worth a try anyway. When you change the design of a table in the BE, try deleting the linked table and then relink the table again. This is to make sure you get a link to the updated table in the BE.

Just a thought...

Hi DBG, what I tried was I converted the linked table in the FE to a Local Table, renamed it, and then used New Data Source to link the BE table to the FE. No change. Same problem as I described. To me, the fundamental question is by adding a new field why do I have this problem. I also went back to both BE's, deleted the field, Compacted it, closed it, opened the FE, and the problem goes away. Yikes!

 

[theDBguy]

Hi DBG, what I tried was I converted the linked table in the FE to a Local Table, renamed it, and then used New Data Source to link the BE table to the FE. No change. Same problem as I described. To me, the fundamental question is by adding a new field why do I have this problem. I also went back to both BE's, deleted the field, Compacted it, closed it, opened the FE, and the problem goes away. Yikes!

I can't say what that's happening without seeing it. But, just to make sure we're on the same page, is this pretty much your setup?

The FE and BE are in the same folder. You have two separate BE files. You named your BE be_data.accdb and your FE works. You then change the BE's name to Chuck_be_data.accdb and then rename the other BE from Ed_be_data.accdb to be_data.accdb and try to open your FE file again. This time, you're saying it doesn't work, right?

 

[chuckcoleman]

I can't say what that's happening without seeing it. But, just to make sure we're on the same page, is this pretty much your setup?

The FE and BE are in the same folder. You have two separate BE files. You named your BE be_data.accdb and your FE works. You then change the BE's name to Chuck_be_data.accdb and then rename the other BE from Ed_be_data.accdb to be_data.accdb and try to open your FE file again. This time, you're saying it doesn't work, right?

That's right. If I open the FE and it works, great. I then open the FE again with the different BE, (but renamed appropriately), it doesn't work. Still in that FE, if I relink that one table, it then works. If I close the FE and open it again with the other BE, (but rename appropriately), it doesn't work unless I relink the table. When I say, renamed appropriately, I mean the BE is always be_data.accdb. The folder never changes. It work when I add the new attachment field in both BE's.

 

[theDBguy]

It work when I add the new attachment field in both BE's.

Sorry, the above part is confusing. Did you mean to say "It should work?" Otherwise, it sounds like there's no problem right now, since both BEs should be identical.

 

[chuckcoleman]

Sorry, the above part is confusing. Did you mean to say "It should work?" Otherwise, it sounds like there's no problem right now, since both BEs should be identical.

Sorry. Both BE's work as expected if I haven't added the new field to both BE's. Identical field names and types. When I add the new field to both back ends, it only works when I relink the table. Then when I exit that FE, (the same always), and to the renamed BE, (again, the same name that the FE expects), I can't access the table unless I relink it. Each time I open the FE after relinking it with the "other" BE, I have to relink the table.

Let's try this. The FE is always called FE. It never changes. The FE always expects be_data. There is the ED_be and the Chuck_be. If I open the FE where Chuck_be has been renamed be_data, it works. If I then open the FE where be_data has been renamed to Chuck_be and Ed_be has been renamed to be_data, I can only access the Admin Table by relinking it. If I relink it, it works. If I then close the db and want to see my data, the be_data is renamed to ED_be and Chuck_be is renamed to be_data. I cannot access the Admin Table until I relink it. It keeps bouncing back and forth this way. Again, I only have trouble with the Admin Table needing to be relinked when I add the new field. If I remove the new field from both BE's, no problem accessing the Admin Table. I never need to relink.

 

[theDBguy]

Sorry. Both BE's work as expected if I haven't added the new field to both BE's. Identical field names and types. When I add the new field to both back ends, it only works when I relink the table. Then when I exit that FE, (the same always), and to the renamed BE, (again, the same name that the FE expects), I can't access the table unless I relink it. Each time I open the FE after relinking it with the "other" BE, I have to relink the table.

Let's try this. The FE is always called FE. It never changes. The FE always expects be_data. There is the ED_be and the Chuck_be. If I open the FE where Chuck_be has been renamed be_data, it works. If I then open the FE where be_data has been renamed to Chuck_be and Ed_be has been renamed to be_data, I can only access the Admin Table by relinking it. If I relink it, it works. If I then close the db and want to see my data, the be_data is renamed to ED_be and Chuck_be is renamed to be_data. I cannot access the Admin Table until I relink it. It keeps bouncing back and forth this way. Again, I only have trouble with the Admin Table needing to be relinked when I add the new field. If I remove the new field from both BE's, no problem accessing the Admin Table. I never need to relink.

Hi. Thanks for the clarification. I understand you're only having problems with the Admin table, because it's the one you modified to add a new field.

Now, to make sure we are using the same terminology, the Linked Table Manager is only used to refresh the links for existing linked tables. You cannot use it to create new linked tables. To do that, you'll have to go to the External Data tab and Import the new table as a linked table.

So, having explained that, I believe you already tried my initial suggestion of deleting the linked Admin table and then creating a new linked Admin table using the External Data tab, correct?

 

[chuckcoleman]

Hi. Thanks for the clarification. I understand you're only having problems with the Admin table, because it's the one you modified to add a new field.

Now, to make sure we are using the same terminology, the Linked Table Manager is only used to refresh the links for existing linked tables. You cannot use it to create new linked tables. To do that, you'll have to go to the External Data tab and Import the new table as a linked table.

So, having explained that, I believe you already tried my initial suggestion of deleting the linked Admin table and then creating a new linked Admin table using the External Data tab, correct?

Here is what I did. I opened the FE, and deleted the linked table Admin. I closed the db, went to the FE for the other BE file, and since they both use the same FE, the Admin table was gone. I then closed the FE. I then opened each BE separately and added the field to the Admin Table. I then when to the FE and went to External Data, New Data Source, From Database, Access and selected the BE, and linked it. I then closed the FE and reopened it. I could access the Admin table. I closed the FE, and opened it again with the different BE but I still could not access the Admin Table. If I right-click on the Admin table and then click on Refresh Link, I can access it. If I close the FE and open it again with the other BE, I cannot access the Admin Table unless I right-click on it and Refresh Link. Yikes!

The only thing I haven't done is to open a blank db, and import all of the BE tables from one into it, and then do the same for the other BE.

 

[theDBguy]

Here is what I did. I opened the FE, and deleted the linked table Admin. I closed the db, went to the FE for the other BE file, and since they both use the same FE, the Admin table was gone.

Hi. Sorry, this doesn't make sense to me. It sounded like you actually have two separate files for the FEs. When you said "went to the FE for the other BE file," that gives the impression it's a different FE file than the one you first opened, deleted a linked table, and then closed.

Obviously, since we cannot see exactly the actions you're doing, although you have explained them clearly, I can't figure out what to suggest to you. Creating a blank db and moving everything over sounds like a good option right now.

Otherwise, I still can't say why you would be having any issues using two different BE files (with the same name). I just gave it a try, and it worked for me.

 

[chuckcoleman]

Hi. Sorry, this doesn't make sense to me. It sounded like you actually have two separate files for the FEs. When you said "went to the FE for the other BE file," that gives the impression it's a different FE file than the one you first opened, deleted a linked table, and then closed.

Obviously, since we cannot see exactly the actions you're doing, although you have explained them clearly, I can't figure out what to suggest to you. Creating a blank db and moving everything over sounds like a good option right now.

Otherwise, I still can't say why you would be having any issues using two different BE files (with the same name). I just gave it a try, and it worked for me.

Hi, Sorry on the FE. There is only one FE. Based on the icon I click on it makes sure by renaming the BE if needed, that the BE is always the same in the same folder. I just created two new blank databases and imported into each their respective BE data, and renamed the file to what it's supposed to be. No change. Same behavior. When I open the FE with the BE that will need to be relinked, if I open the Navigation Pane and double click on the Admin Table, I get, "The Microsoft Access database engine cannot find the input table or query. Make sure it exists and that its name is spelled correctly."

As a workaround, is there fairly simple code to relink that table every time the db opens?

I'm really sorry this is such a pain. I believe I've done everything correctly, I just can't figure this out. I guess potentially there could be a bug. I just looked at the About info and I'm on Microsoft Access for Microsoft 365 MSO (16.0.14228.20216) 64-Bit. I thought all of the Office applications were 32 bit???

 

[theDBguy]

Hi, Sorry on the FE. There is only one FE. Based on the icon I click on it makes sure by renaming the BE if needed, that the BE is always the same in the same folder. I just created two new blank databases and imported into each their respective BE data, and renamed the file to what it's supposed to be. No change. Same behavior. When I open the FE with the BE that will need to be relinked, if I open the Navigation Pane and double click on the Admin Table, I get, "The Microsoft Access database engine cannot find the input table or query. Make sure it exists and that its name is spelled correctly."

As a workaround, is there fairly simple code to relink that table every time the db opens?

I'm really sorry this is such a pain. I believe I've done everything correctly, I just can't figure this out. I guess potentially there could be a bug. I just looked at the About info and I'm on Microsoft Access for Microsoft 365 MSO (16.0.14228.20216) 64-Bit. I thought all of the Office applications were 32 bit???

Is there any way you can pare down your BE and FE with non-sensitive data and share them with us for examination? Like I said, I just did my own little experiment, and I didn't have any problems, so I am not sure if this could be a bug.

As for the workaround you requested, yes, any auto-relink code available should be applicable to your question. Here's one example:

With CurrentDb.TableDefs("Admin")
    .RefreshLink
End With

(untested)
Hope that helps...

 

[Pat Hartman]

Are you certain that you have only a single copy of the FE? Just to be sure, Open the FE from one of the shortcuts and add a big red label to the opening form. Close and open with the other shortcut.

 

[CJ_London]

Just speculation but you said you added an attachment field - these are actually referencing a hidden table. So wondering if the field type is the problem.

as an experiment remove the attachment field and add a text field instead. Then in the fe, delete the linked table then create a new linked table

 

[chuckcoleman]

Are you certain that you have only a single copy of the FE? Just to be sure, Open the FE from one of the shortcuts and add a big red label to the opening form. Close and open with the other shortcut.

Hi Pat. I added a BIG RED BOX to the startup form. It shows up after I click on each/both shortcuts. There is only one FE.

 

[chuckcoleman]

Just speculation but you said you added an attachment field - these are actually referencing a hidden table. So wondering if the field type is the problem.

as an experiment remove the attachment field and add a text field instead. Then in the fe, delete the linked table then create a new linked table

Thanks CJ. Ok, I removed the attachment field in both BE's. I added a new short text field. I then started the FE using one of the icons. I deleted the linked table. I then went to External Data, New Data Source, From Database, Access and navigated to the BE and picked the linked option. I was able to open the Admin Table. I then closed the db and started it again with the other icon. Same problem, I cannot access the linked Admin Table.

Remember, I have one FE and two BE's and I rename the BE that I want to use with the FE. I created a new blank db. I went through the External Data/New Data Source above and I added the Admin Table from one of the BE's and added the Admin Table from the other BE. WOW, I had no problem opening either of the Admin Tables in the two BE's. That tells me there is something wrong in the FE. Again, earlier I created a blank db and I imported all tables, queries, forms, reports, etc. into it and the problem still exists.

Any other ideas?

 

[Pat Hartman]

That is comforting and eliminates one possibility.

I would not use the method you are using to swap databases. I would use a single shortcut to the FE. In the opening form, I would ask who is logging in and relink based on that. If Ed never links to your tables, then I would always link to Ed's tables if he logs in but if it is you, I would ask.

Your method seems dangerous to me because it relies on making a copy of the BE. What happens to any changes made to that BE?

 

[chuckcoleman]

That is comforting and eliminates one possibility.

I would not use the method you are using to swap databases. I would use a single shortcut to the FE. In the opening form, I would ask who is logging in and relink based on that. If Ed never links to your tables, then I would always link to Ed's tables if he logs in but if it is you, I would ask.

Your method seems dangerous to me because it relies on making a copy of the BE. What happens to any changes made to that BE?

I'm really not making a copy of the BE. I'm simply changing it's name to be what the FE expects, what the FE was linked to. When I change the name, there is nothing that changes in the FE. I believe, the FE doesn't even know that it's linked to a different BE. The FE knows it is always linked to wood_be.accdb. I've done this for years with other db's and never had a problem.

 

[theDBguy]

Hi Pat. I added a BIG RED BOX to the startup form. It shows up after I click on each/both shortcuts. There is only one FE.

Can you maybe post what's in your scripts?

 

[Pat Hartman]

OK. You have two files in the same folder. Chuck_BE.accdb and Ed_BE.accdb. Your code renames Chuck to Common_BE.accdb. Your app opens and you do something. What happens when the app closes? Do you manually rename it back to Chuck? Even worse, if the files are in a shared folder, what happens if Ed opens his FE and it tries to rename his FE to Common?

Your method is NOT anything approaching standard methodology. So, rather than trying to figure out what is wrong, I think the time would be better spent to standardize your methodology.