UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> Auto Relinking Fe To Be Tables, Access 2007    
 
   
everettmiller
post Nov 21 2013, 01:29 PM
Post #1

UtterAccess Enthusiast
Posts: 82



Hey Forum!
After spending some time browsing the UA code archive for a solution to relink an FE to 2 BE I came up with what looked like a good solution. Attached is a copy of the module I developed for this. It contains two subs the first of which relinks the tables and the second that prints the current link to the immediate window. The first sub, TableRelinker(), runs without any error and prints a list of the new connection to the immediate window. However, when I run the second sub, CheckTableDefs(), the output shows that the relinking did not take place, or was not saved.
I'm at a loss to understand what is going wrong and I'd really appreciate some guidence.
Thanks!
Everett
Attached File(s)
Attached File  TableRelinker.txt ( 2.38K ) Number of downloads: 9
 
Go to the top of the page
 
+
theDBguy
post Nov 21 2013, 02:41 PM
Post #2

Access Wiki and Forums Moderator
Posts: 58,559
From: SoCal, USA



Hi Everett,
orry, I can't look at your attachment right now because I don't have Access installed on this machine (tablet). But, do you really need to create your own auto-relinker? Have you tried the J-Street auto-relinker utility?
Go to the top of the page
 
+
everettmiller
post Nov 22 2013, 07:50 AM
Post #3

UtterAccess Enthusiast
Posts: 82



Hey DBGuy! Appreciate the reply!
've looked at a number of relinkers from both the UA archive as well as other sites. Altogether, I've reviewed 8 of them, including JStreet, Bob Larson, Tony Toews, Crystal, and more. Almost all of them have several characteristics in common that make them unsuitable for my project:
1. They prompt the user for the location of the back-end files (our users generally don't know and really don't care)
2. They assume the back-end files are located in the same folder as the front-end (we're networked with the BE on the server)
3. They do not support multiple back-ends (currently 2, but will increase to 6 within the next 45-60 days)
And, finally, since I'm not a terribly sophisticated developer, simplicity is golden for me. One part of the update process I'm putting together is to relink the BE tables to the FE once the new FE has been copied to their workstation. The VBA tests the tabledefs for current connect info. If there isn't any, that table is skipped. When connect info is found the connect info is changed to the production BE and the link is refreshed. As of now, the VBA correctly identifies the linked tables and appears to successfully relink them. However, it also appears that the new connect info for each table is not being saved to its tabledef.
Thanks for your help with this!
Everett
Go to the top of the page
 
+
everettmiller
post Nov 22 2013, 08:27 AM
Post #4

UtterAccess Enthusiast
Posts: 82



Incidentally, I looked at the file I uploaded. I don't know if its normal or not but it appears that all of the formatting dissappeared and the file looks like a jumble of test. Here it is again in the hope that the one will be easier to look at.
Attached File(s)
Attached File  TableRelinker.txt ( 2.14K ) Number of downloads: 8
 
Go to the top of the page
 
+
theDBguy
post Nov 22 2013, 11:39 AM
Post #5

Access Wiki and Forums Moderator
Posts: 58,559
From: SoCal, USA



Hi,
nfortunately, I am still not in a position to download your file. However, I believe J-Street's relinker can handle multiple BEs. As for your requirement to not prompt the user for the new location, how will your code know where it is? Do you have a table where the locations are stored?
Go to the top of the page
 
+
everettmiller
post Nov 26 2013, 07:32 AM
Post #6

UtterAccess Enthusiast
Posts: 82



Hey DBGuy....
After looking carefully at both J-Street and the Crystal relinkers, I found that while J-Street does handle multiple BE's Crystal does so without prompting. It can prompt, there is code in the relinker to do so. Instead, the relinker is called with the path passed as a variable (ReLinker c:\database\BE). In testing it ran flawlessly and was exceptionally fast. What was really nice was that I did not have to modify the code at all to get it to work. After working with Crystal along with some "homegrown" VBS scripts the entire process I was putting together now functions as intended. That is, upon FE startup, the version # is checked against the BE. If it doesn't match, the VBS is called that closes the FE, copies down the new FE and then reopens it. Then when the user clicks the appropriate button, the relinker is called to refresh all the links to the BE tables. All in all, a very fast and clean process that takes less than a minute start to finish.
Thanks for your help! If you like I can post the various pieces in case someone else has a similar need.
Everett
Go to the top of the page
 
+
theDBguy
post Nov 26 2013, 11:28 AM
Post #7

Access Wiki and Forums Moderator
Posts: 58,559
From: SoCal, USA



Hi Everett,
Glad to hear you got it all sorted out. Good luck with your project.
Go to the top of the page
 
+

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 1st November 2014 - 05:13 AM