Linked Table Not working

[chuckcoleman]

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.

Again. I am the single user. I gave a copy of what I developed to Ed, both FE and BE. He lives a block away. Whatever he does on his computer never touches anything on mine. He has a non networked computer. I will occasionally get a copy of his BE so I can see what he’s doing. When I want to see my data, I click on the Chuck icon that runs the chuck.bat file and evaluates if it sees Ed_BE.accdb, then wood_be.accdb must be Chucks data and the batch file just opens the FE. If the batch file sees Chuck_BE, then it renames wood.accdb to Ed_BE.accdb and it then renames Chuck_BE.accdb to wood.accdb. The reverse logic works if I click on the Ed icon that runs the Ed.bat file. All I’m doing is changing a file name based on the icon I click on. I don’t think that should be the focus. It should be what is causing the front end to not reliably link to one table.

Note, I even deleted every linked table in the front end, compacted the front end, added back the linked tables and the problem still exists. I’m just saying I’ve never had this issue before.

 

[chuckcoleman]

Can you maybe post what's in your scripts?

Do you mean the two .bat files? One to access my data and one to access Ed’s data?

 

[Pat Hartman]

Let me try again. Are BOTH BE's in the same folder? If they are, how are you managing the renaming? Do you leave your BE as always named Chuck and rename it from Chuck to common? How does it get back to being named Chuck? If you leave it as Common and Ed is in the same folder, you will get an error when you try to rename Ed to Common.

Then your renaming method won't work at all because the FE starts out linked to FolderA/Common and even if you change the name of Ed to common, that doesn't move it to FolderA. It is still in FolderB so your FE is actually linked to the Chuck BE because that is the one in FolderA..

Please consider normalizing your use of multiple BE's. It really isn't hard at all. Every single database I create for other people has to be able to swap BE's. When I am developing, I really can't be linked to the production BE that the users are working in so I make a copy and move it to my local drive. I don't change the name but the path changes and so the tables all need to be relinked. You can do this quite easily using the linked tables manager OR you can create your own custom form as I do to relink when I need to.

 

[chuckcoleman]

Let me try again. Are BOTH BE's in the same folder? If they are, how are you managing the renaming? Do you leave your BE as always named Chuck and rename it from Chuck to common? How does it get back to being named Chuck? If you leave it as Common and Ed is in the same folder, you will get an error when you try to rename Ed to Common.

Then your renaming method won't work at all because the FE starts out linked to FolderA/Common and even if you change the name of Ed to common, that doesn't move it to FolderA. It is still in FolderB so your FE is actually linked to the Chuck BE because that is the one in FolderA..

Please consider normalizing your use of multiple BE's. It really isn't hard at all. Every single database I create for other people has to be able to swap BE's. When I am developing, I really can't be linked to the production BE that the users are working in so I make a copy and move it to my local drive. I don't change the name but the path changes and so the tables all need to be relinked. You can do this quite easily using the linked tables manager OR you can create your own custom form as I do to relink when I need to.

I apologize, I know this is getting old for you and me. Both BE's are in the same folder. Let's say you developed a FE & BE db. You gave me a copy of both files. I used it. When you were doing development work on your FE, you asked me to give you a copy of my BE. I gave it to you. You renamed your BE to FRED. Then you placed the BE that I gave you in the same folder. It is the same name as the original one you renamed to FRED. You opened your FE and everything worked. That's what's going on. What changed is that I added a new field to the Admin Table in both my BE and the other one. Depending on which dataset I want to see, determines which one gets the wood_be.accdb file name, (what the FE is expecting), and the other one gets renamed to either Ed or Chuck

 

[theDBguy]

Do you mean the two .bat files? One to access my data and one to access Ed’s data?

Yes, that's what I meant. And just to reiterate, I know you are having problems with your setup; but just to remind you, I tried to duplicate your setup and didn't run into any issues.

 

[chuckcoleman]

I apologize, I know this is getting old for you and me. Both BE's are in the same folder. Let's say you developed a FE & BE db. You gave me a copy of both files. I used it. When you were doing development work on your FE, you asked me to give you a copy of my BE. I gave it to you. You renamed your BE to FRED. Then you placed the BE that I gave you in the same folder. It is the same name as the original one you renamed to FRED. You opened your FE and everything worked. That's what's going on. What changed is that I added a new field to the Admin Table in both my BE and the other one. Depending on which dataset I want to see, determines which one gets the wood_be.accdb file name, (what the FE is expecting), and the other one gets renamed to either Ed or Chuck

Yes, that's what I meant. And just to reiterate, I know you are having problems with your setup; but just to remind you, I tried to duplicate your setup and didn't run into any issues.

Thank you dbg. I'm done for the day. I got it working, but I still don't know why I've had the problem. On my Switchboard form which is started with the db starts, I added to the OnOpen event:

Dim db As Database
Dim tbflinked As TableDef
Set db = CurrentDb
CurrentDb.TableDefs("Admin Table").RefreshLink

It works. I attached the two .bat files. This site won't allow .bat files so I renamed them to .txt files. They are started from two different icons. The .cmd file that is used to run the start_chuck.bat file contains: start /min start_Chuck.bat ^& exit

 

[Pat Hartman]

I gave it to you. You renamed your BE to FRED.

Except that there is no way I would ever do that since I know how to use the linked tables manager and in many apps, I have a custom relinking form. That lets me keep the BE's original name and never accidentally clobber my production BE because I forgot to rename it. When you work in a production environment, you have to be able to switch BE's without losing track of which one you are linked to. For one of my clients ( a government contractor that makes Marine 1), I have 8 levels of testing to pass through before any change ever hits the production environment. For a hobbyist, you can do whatever floats your boat.

At least you got your process working. It is a poor method but as long as you never make a mistake with your renaming, it will serve your purpose.

 

[Lucky33]

Have you tried to create two FE files, naming one ED and the other Chuck and link each FE to its corresponding BE.

 

[chuckcoleman]

I hi Lucky. I can try that later. Im
sure it will work after I relink the BE. I’ll provide feedback later today. Thank you.

 

[theDBguy]

I hi Lucky. I can try that later. Im
sure it will work after I relink the BE. I’ll provide feedback later today. Thank you.

Hi. Just FYI, I downloaded your scripts yesterday and plan on playing with them today.

 

[chuckcoleman]

Thank you as always DBG!

 

[Pat Hartman]

Creating two FE's is not the way to go. You will end up accidentally changing the wrong one. The proper method is ONE FE that can easily switch BE's.

 

[chuckcoleman]

Creating two FE's is not the way to go. You will end up accidentally changing the wrong one. The proper method is ONE FE that can easily switch BE's.

I do agree. Can you point me to a method of easily changing the BE’s?

 

[theDBguy]

Thank you as always DBG!

Hi. I finally had a chance to play with your setup using your scripts and can confirm the behavior you're seeing. However, I can tell you it's not a bug and have an explanation for what's happening.

The root of your issue was the use of the Attachment field. Perhaps you weren't aware, when you create an Attachment field, Access creates a hidden system table to store the files you put into that Attachment field. This hidden system table has a "name" assigned to it.

So, when you created or added the Attachment field to each BE, you were basically creating two different system tables. When you open the FE without refreshing the link, you won't be able to use the new field, because the link to that system table hasn't been established yet.

When you issue the Refresh Link command, the FE then associate the hidden system table from that copy of the BE to the linked table in the FE.

So, when you open the FE again, but connect it to the other BE, that hidden system table link is broken. You'll have to refresh the link again to associate the system table from that BE, which would then break it for the other BE when you open it again.

If, on the other hand, you created the Attachment field to a single BE and then made a copy of that BE file and give it a different name, this problem will not happen even if you switch the FE between those two copies of the same BE, because the system table names are going to be the same.

Hope that makes sense...

 

[Pat Hartman]

Can you point me to a method of easily changing the BE’s?

Since this will never be done by anyone but yourself, just use the linked tables manager. You don't need a special form although I can upload one if you really want it. I built the form years ago when it was difficult to relink when you had multiple BE's. But now the switchboard manager includes the ability to easily do that by grouping all the linked tables so I only include the relink form if I want the user to be able to relink which is almost NEVER.

 

[chuckcoleman]

Hi. I finally had a chance to play with your setup using your scripts and can confirm the behavior you're seeing. However, I can tell you it's not a bug and have an explanation for what's happening.

The root of your issue was the use of the Attachment field. Perhaps you weren't aware, when you create an Attachment field, Access creates a hidden system table to store the files you put into that Attachment field. This hidden system table has a "name" assigned to it.

So, when you created or added the Attachment field to each BE, you were basically creating two different system tables. When you open the FE without refreshing the link, you won't be able to use the new field, because the link to that system table hasn't been established yet.

When you issue the Refresh Link command, the FE then associate the hidden system table from that copy of the BE to the linked table in the FE.

So, when you open the FE again, but connect it to the other BE, that hidden system table link is broken. You'll have to refresh the link again to associate the system table from that BE, which would then break it for the other BE when you open it again.

If, on the other hand, you created the Attachment field to a single BE and then made a copy of that BE file and give it a different name, this problem will not happen even if you switch the FE between those two copies of the same BE, because the system table names are going to be the same.

Hope that makes sense...

WOW! Thank you for your research on this!!! I now understand. When I added the Attachment table, that’s when the problem began. Thank you again!

 

[chuckcoleman]

Since this will never be done by anyone but yourself, just use the linked tables manager. You don't need a special form although I can upload one if you really want it. I built the form years ago when it was difficult to relink when you had multiple BE's. But now the switchboard manager includes the ability to easily do that by grouping all the linked tables so I only include the relink form if I want the user to be able to relink which is almost NEVER.

Pat, thank you. Can you please explain how the Switchboard Manager makes delinking easier? I know how to go to External Data, Database, etc to relink/refresh the tables, but I’m not sure what that has to do with the Switchboard Manager.

 

[Pat Hartman]

Great detective work dbGuy
Chuck, I don't think I mentioned the Switchboard manager. I can't review the thread right now.

 

[MajP]

Can you please explain how the Switchboard Manager makes delinking easier? I know how to go to External Data, Database, etc to relink/refresh the tables, but I’m not sure what that has to do with the Switchboard Manager.

If interested here is an automatic relinker / delinker. When opening the FE it will search for the BE and if not found give you a filedialog. There is a button to then switch between the two provided backends. But as Pat said in many cases this should only be available to an admin since most users should not have the ability to link/delink.
For the types of DBs I make the users are mostly power users with just a couple of users. So I would provide this feature.

 

[theDBguy]

WOW! Thank you for your research on this!!! I now understand. When I added the Attachment table, that’s when the problem began. Thank you again!

You're welcome. We're all happy to assist. Good luck.