JStreet Access Relinker with two different BE table. (1 Viewer)

[Geirr]

Hi all.

I'm a very big fan of the JStreetAccessRelinker, and up to now have worked perfectly for my projects.

But, I've seen that my org. needs to splitt the back end in two different databases, one database for common company recource info, and one database for each project.

When using the function jstCheckTableLinks_Prompt, and select the project database, it fails on the tables in the common database, and vice versa.
I'm absolutly not the best expert in vba, but as I see it, the function tries to relink all linked table with selected database, and therefore fails due to tables 'missing' in the selected table.

Is there anyone who have tried this and have 'seen' a solution?
What i'm thinking of - how to alter the function to only check/relink tables found in the selected databasefile?

Best regards,
Geirr.

 

[theDBguy]

Hi. Although I haven't used it on multiple BEs, I thought the function was supposed to be able to handle multiple BEs. What was happening with your database? I didn't think the function needs to be modified.

 

[Geirr]

Hi, and thank you for your reply.

I believe I've found something. Let me explain;
Common database = CommData.accdb
Project database 1 = ProjectData1.accdb
Project database 2 = ProjectData2.accdb

When jstCheckTableLinks_Prompt runs, the filepicker opens, and the databased CommData.accdb are shown as 'default file' in the file-name field in picker. If I click OK, and then select selct project database 2 in the second filepicker, everything works fine.
But, If I remove the default filename (commdata.accdb) and replace it with one of the projectdata databases, it fails before a second filepicker opens.

I've changed the tableneames in the common databasefile, so the table list in navigation pane where sorted the way like the table in project data came first. Then, the first filepicker have the the project databasefile name (projectdata1.accdb) as default name in the file name field in filepicker.

That could work, but every time this program will start for the first time, I need to pick/select where the common datafile relies, before the same function should be used to select which project to use.

Now, when I understand how this work, I could make a clear 'how to use' to my collegues, but as I understand you - this might be a case that was not ment to be... And it might underline why I initially would like the function to check/relink only tables in selected database, but I understand this might be in conflict with the function call used in auoexec macro, regardless if this use the parameter 'full'.

Best regards,
Geirr.

 

[theDBguy]

Hi, and thank you for your reply.

I believe I've found something. Let me explain;
Common database = CommData.accdb
Project database 1 = ProjectData1.accdb
Project database 2 = ProjectData2.accdb

When jstCheckTableLinks_Prompt runs, the filepicker opens, and the databased CommData.accdb are shown as 'default file' in the file-name field in picker. If I click OK, and then select selct project database 2 in the second filepicker, everything works fine.
But, If I remove the default filename (commdata.accdb) and replace it with one of the projectdata databases, it fails before a second filepicker opens.

I've changed the tableneames in the common databasefile, so the table list in navigation pane where sorted the way like the table in project data came first. Then, the first filepicker have the the project databasefile name (projectdata1.accdb) as default name in the file name field in filepicker.

That could work, but every time this program will start for the first time, I need to pick/select where the common datafile relies, before the same function should be used to select which project to use.

Now, when I understand how this work, I could make a clear 'how to use' to my collegues, but as I understand you - this might be a case that was not ment to be... And it might underline why I initially would like the function to check/relink only tables in selected database, but I understand this might be in conflict with the function call used in auoexec macro, regardless if this use the parameter 'full'.

Best regards,
Geirr.

Hi. You may have misunderstood what I said. I am pretty confident the makes of the function at JStreet would have worked out all the scenarios to make their function work on multi-BE database.

I am not sure I follow your description, because I can't see what's actually happening when you run the function. Are you saying that if you have three separate BE files, that the function only works if you always select the common BE on the first file picker, but it always fail if you select a different BE file first?

If so, I don't have it in front of me right now, but doesn't the file picker prompts the user to select the BE file that contains a specific table name? I mean, when the code reads the names of the tables to relink, and it cannot find the original BE file for a table, doesn't it prompt you with the name of the table, so you can point to the BE file that contains it. If you point to a different BE file that doesn't contain the table in the prompt, then you are making the code fail on purpose.

To make sure we are on the same page, you might consider posting some screenshots along the way until you get the error.

 

[Geirr]

Hi.

At first, I believed there was an issue using the function on two BE's. But as I wrote in second email, I found it works - and works very well! - but as I wrote, I found a issue which I found to be little odd, and could easily lead to error messages.

Those five tables belongs to two BE's; CommonData DB does include CableType, LangDiag and UserReg -> table 1, 2 and 5.
ProjectData DB includes Project and ULog -> table 3 and 4. (This is only a start on a complete rewrite of the project).

What causes the error messages mentioned in the first message was that when runinng jstCheckTableLinks_Prompt for selecting a another ProjectData DB, I did'nt see that the filpicker opens with the CommonData DB as defalt filename. I clicked on a ProjectData db, and error messages are shown.
This because I should click OK first to accept the CommonData, and then on the second filepicker select a another ProjectData DB.
I found out that the first filepicker have the CommonData db as default because this DB have the first table in table list in it, as shown on image above. When I changed the table names to xCabletype and xLangDiag, then the ProjectData was the default filename on the first filpicker next time i ran jstCheckTableLinks_Prompt.

So, as I wrote in the second message, this can be done by making a clear instruction to my collegues. Or, only tables in selected DB in the filepicker should be updated, regardless how many BE's are in use. But I understand this could also lead to issues...

Hope this clear out the issue, you know - english is not my native language, so the explanation my lack due to that...

Best regards,
Geirr.

 

[theDBguy]

Hi.

At first, I believed there was an issue using the function on two BE's. But as I wrote in second email, I found it works - and works very well! - but as I wrote, I found a issue which I found to be little odd, and could easily lead to error messages.
View attachment 87274
Those five tables belongs to two BE's; CommonData DB does include CableType, LangDiag and UserReg -> table 1, 2 and 5.
ProjectData DB includes Project and ULog -> table 3 and 4. (This is only a start on a complete rewrite of the project).

What causes the error messages mentioned in the first message was that when runinng jstCheckTableLinks_Prompt for selecting a another ProjectData DB, I did'nt see that the filpicker opens with the CommonData DB as defalt filename. I clicked on a ProjectData db, and error messages are shown.
This because I should click OK first to accept the CommonData, and then on the second filepicker select a another ProjectData DB.
I found out that the first filepicker have the CommonData db as default because this DB have the first table in table list in it, as shown on image above. When I changed the table names to xCabletype and xLangDiag, then the ProjectData was the default filename on the first filpicker next time i ran jstCheckTableLinks_Prompt.

So, as I wrote in the second message, this can be done by making a clear instruction to my collegues. Or, only tables in selected DB in the filepicker should be updated, regardless how many BE's are in use. But I understand this could also lead to issues...

Hope this clear out the issue, you know - english is not my native language, so the explanation my lack due to that...

Best regards,
Geirr.

Hi. Sounds like you understand how the function works now, so you should be able to provide the clear instructions to your users. I would just tell them to pay attention and make sure to select the correct BE file when prompted.

 

[Pat Hartman]

and one database for each project.

This is almost certainly a bad idea.

However, the form I use for relinking does support multiple databases so it might help you.