Linked Table Not working

[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

 

[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!

 

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

 

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

 

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

 

[Lucky33]

Thank you MajP for your solution, it is absolutely great. Just out of curiosity, what if the BE files are protected with a password? any work around?

 

[MajP]

There is another commonly referenced relimker that handles protected files. I will look for the link.

 

[MajP]

Google JStreet Relimker. I have not used it, but it is well reviewed.

 

[Lucky33]

Thanks for all the efforts and support you always give