X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Relinker: Multi-be, Label Table Description To Show Connect Info, Any Version    
post Jul 12 2008, 03:07 PM

Posts: 20,385
Joined: 10-January 04

The purpose of the ReLinker is to automatically re-link one or multiple back-ends to files in a particular directory. It searches the Connect string for filename and re-links all tables it can find to files in specified directory. If no path specified is specified, the ReLinker uses the FE database path. Supported file formats include Access, Excel, Lotus, Exchange, Text, Paradox, FoxPro, and dBASE (ODBC is ignored except for refresh)
Re-links tables to one or more back-ends
-Handles different file formats
-Skips user intervention if tables are not found (I consider this one of the best features since FEs are usually linked to one BE, often with a bunch of tables, all of which may not (still) be in the BE)
-Works for Access 97 through Access 2007
I wish to thank Brent Spaulding (datAdrenaline) for feedback and contributing code to split a string into an array. Thanks also to Glen Kruger (GlenKruger), Mark Davis (CyberCow), Allan Bunch (RuralGuy), Bill Mosca (wrmosca), and A.D. Tejpal for testing and comments. Thanks to John Mishefske for ideas on how the ReLinker can be enhanced in the future. Thanks to Arvin Meyer, Doug Steele, and Toney Toews for pointing me to sites with connection string examples. You guys are great!

How does this work? The ReLinker loops through all the table definitions and looks at the connect string to determine the filename - then it checks to see if that same filename is in the path it is changing to. If not, it tests to see if the file is still in the old path. All table links are refreshed unless an optional parameter is specified not to do that ... and it reports what was not found or couldn't be refreshed.

FE = front-end
BE = back-end

The main procedure, ReLinker, takes an optional path as a parameter. If a path is not specified, it uses the current database directory. Other optional parameters include: skip MsgBox at end of procedure (default is False -- but the Immediate window will still show everything), refresh all links even if connect string not changed (default is True), skip changing table description to show connect info (default is False)

module name is:

Main procedures:
-ReLinker is a multi-back-end relinking program (also works with one BE)
-ChangeTableDescriptions_ConnectInfo is a linked Table Description Labeler
-DeleteAllTableDescriptions deletes all table descriptions that exist
why would you use this?

If you have a FE database that you move to another directory or drive or computer (assuming the BE location changes too) and don't have handy relinking software: just import the ReLinker module and run the top procedure or type a simple command into the Immediate window -> Relinker. If your BE(s) is not in the same directory as the FE, then either pass the directory or modify the top procedure, which runs the ReLinker

If you have BEs in multiple directories, you can run the ReLinker for each one without having to specify which tables link to which files (suppress MsgBox, Refreshing, and changing table descriptions until the last run)
'skip MsgBox, don't refresh unchanged links, skip table description change
ReLinker "c:path1", True,False,True
ReLinker "c:path2", True,False,True
ReLinker "c:path3", True,False,True
'display MsgBox, refresh all links, change all linked table descriptions
ReLinker "c:path4"
Aside from multiple BEs, the ReLinker is especially handy in situations where there might be a table specified in the FE that is not in your BE. This slows down the Linked Table Manager - and you! ... since user intervention will be required to pick files. When you launch the Linked Table Manager and there is a table specified in the FE that is no longer in the BE, you will be prompted for EACH back-end location even they are all the same! ... but not with the ReLinker - you can just sit back!

Icing on the cake is that the table descriptions now change to reflect connect info (and they keep whatever you wrote) -- and if you re-link with it again, the connect info is replaced in the table description - so handy to throw into the FE of any application with linked tables.

In my test database, there are links to tables in other Access databases, Excel spreadsheets, and Text files; I have back-end files in the same directory as the FE and also tables linked to files in other directories -- one directory of which I renamed so Access can't find the it :evil:

I also changed the filename of one Excel file in the current database directory resulting in a couple linked spreadsheets where Access doesn't recognize the connect info (file is there but not in the filename Access is looking for) - nice that the ReLinker tells you which tables it can't find... and it waits until everything it can do is done!

If the ReLinker can't change connect info and the old info is still good, it leaves the connect string alone. It also leaves ODBC tables alone -- will not refresh if you send False for booRefreshAll... except it does still put connect info in the table descriptions unless you instruct it not to (booSkipTableDescription = True).

THere is how the table description is changed with connect info for a local link:
Book1.xls ~ Sheet1$ ~ C:Data8cli_8TOOLSXL_Re_Linker (Jun-21-08)

or, if it can't find something:
FAILED REFRESH ~ SomeDatabase.mdb ~ tblGone ~ C:Data8cli_8008_06_MM (Jun-21-08)

this table is not in the BE anymore since it found the file, otherwise the table description will say:
FILE NOT FOUND ~ SomeDatabase.mdb ~ tblGone ~ C:Data8cli_8008_06_MM (Jun-21-08)

- chose to put the filename first in the Table Description so, if you are sorting by description (Access 2003 and below -- how I miss that in 2007...), all BE tables in the same place will be grouped together.

Here is the message box (no user intervention required!) at the end of the Relinker routine after moving the FE (of a totally unrealistic database) to another directory (basically just renamed the directory -- simulating transferring a FE from one location to ano(btw, I pressed CTRL-C when the MsgBox popped up then CTRL-V to a NotePad document to get this ... one of my kids told me about that nifty trick)

the reason it only tells you the path to the new links, ie:
4 Table Links changed to -> C:Data8cli_8TOOLSXL_ReLinker
is because the tables may be in different back-ends -- the table descriptions tell you filenames and more.

The ReLinker tests file existence for each non-ODBC link even if it doesn't change the Connect info; hence how it found pathfilename was no good for BadTable1 and BadTable2.

By default, it refreshes all table links and tells you what it can't find. You can set an optional parameter to skip refreshing links if the connect information does not change -- if the table is linked to a local file, the path and filename will be checked for existence anyway.

Some table links do not need to change and that is fine because they are not in the specified directory (default=CurrentProject.Path) - simulating centrally located tables that don't move and are always accessible and need to be left alone.

Lets say you are developing a FE and switching it back and forth ... as soon as you move the front-end file, running this routine updates all the links and skips the ones that fail so you don't have to pick the files one-by-one ... a royal pain you will no longer have to endure!

If you need to change links for multiple paths, as long as the filenames are the same as when you linked them, simply call the ReLinker for each path without need to specify tables and files for each path -- the ReLinker will link to the files that it finds since it looks at the Connect string for each TableDef ...

If you HAVE changed BE filenames, the ReLinker will tell you what needs to be re-linked and then you can use the Linked Table Manager (Tools, Database Utilities...) on those tables ...possible future enhancement.

Most often, there will not be more than one back-end and of course, the ReLinker is still very convenient.
Going to a more realistic example: Open a front-end without the ReLinker code.

1. Press Alt-F11 for VBE window
2. import the ReLinker module (File, Import...)
3. Compile (if DAO is referenced, there should be no errors)
4. Save (it already has a name so no need to think of something other than, for instance, Module1)
5. use Project Explorer to switch to the ReLinker
6. copy the following from the comments at the top (or this message) into the Immediate Window (Ctrl-G)
7. press ENTER to run
Voila! here is a sample MsgBox for a practical database with just one back-end in the same directory as the FE:In this case, every new table link was successful.
if the ReLinker is run again without moving the BE, since everything is already properly connected, this is the MsgBox: Changing table descriptions takes the most time - it whizzes if you skip this

You can also press CTRL-G to see all the goodies in the debug (Immediate) window when the program is done.
Whew! this might seem like a lot of information -- takes more time to write it than it does to do it, that's for sure!

Read the comments at the top of the ReLinker module for usage suggestions

Update Table Descriptions only:

If you do not want to change links, but do want to label table descriptions with Connect information, run ChangeTableDescriptions_ConnectInfo. It is independent of what else is there, except it does use a couple common procedures, AddTextToTableDescription which calls IsPropertyDefined.

Delete Table Descriptions:
Another independent procedure in this module is DeleteAllTableDescriptions, which, as its name implies, deletes all table descriptions.

To import the code, unzip the attachment, go to a VBE window (ALT-F11) and choose File, Import, then, navigate to BAS file, import it, read comments, reference DAO if necessary, compile, then Save.
Theoretically, all the local file formats should work, but you know how that goes! Extensive testing was done but not every format was available to test. if you have any problems with the ReLinker not handling a particular standard format, please let me know.
why did I write this?
I get a lot of FE/BE databases from others and always have to re-link. I have a program to update all tables to ONE back-end but it does not handle multiple BEs or change table descriptions to show Connect info... this does - and without stopping if any links fail!
Attached File(s)
Attached File  bas_Crystal_ReLinker_97-2007.zip ( 6.52K )Number of downloads: 1196
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd February 2019 - 08:37 AM