 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / February 2006Auto Relinking Tables
Hello All, i know this question has come up alot, and i have read the forumns and looked at the sample code provided, but i have a slightly different request that im hoping some more experienced users can help solve. At startup, Id like to checks for valid links, if present, simply continue to main form, else ask user for directory where all tables are located and relink them, then goto main form Assume the following: 1. There are multiple backend mdb's (each containing some of the tables) 2. All the mdb's are always in the same directory. All of the solutions i have seen seem to mimic Access's built in table manager in that they make you select each table for each link...since all mine are in the same directory, isnt there a way to use the selected directory as a constant in the relink process? Thanks The Dev A code seems to want to make the end user choose each mdb for each table (eccchhh!) you can use the code provided by the MVP Dev Ashish available here: http://www.mvps.org/access/tables/tbl0009.htm you can edit the line from: If MsgBox("Are you sure you want to reconnect all Access tables?", _ to: End If like this: 'must allways connect 'If MsgBox("Are you sure you want to reconnect all Access tables?", _ ' vbQuestion + vbYesNo, "Please confirm...") = vbNo Then Err.Raise cERR_USERCANCEL 'First get all linked tables in a collection Set collTbls = fGetLinkedTables 'now link all of them Set dbCurr = CurrentDb 'strMsg = "Do you wish to specify a different path for the Access Tables?" 'If MsgBox(strMsg, vbQuestion + vbYesNo, "Alternate data source...") = vbYes Then ' strNewPath = fGetMDBName("Please select a new datasource") 'Else 'strNewPath = vbNullString 'End If so it would always try to reconnect the linked table an f if it doesn't find them it will ask you where to find the back end database!! But i don't know how to avoid the message that database shows wnen startup: "could not find <back end database path>"!!!!! and how to launch automaticaly this routine at database's startup!!! Due to your inquiry i'm rewriting part of the code provided to the adress i suggested to you... if you want to do that by yourself is quit easy: just read the soure path of the currentDB using the property .connecct : "CurrentDb.TableDefs("<tableName>").Connect" and then use it as the path for the linked table connection using another time .connect but this time not to retrive source path but to set up it!!! after do this don't forgot to use the .RefreshLink method to update the table's link information!! Double Thanks for the response. Yes, i was aware of Dev's code ( its everywhere on the b oards whenever anyone mentions linking tables) but it was insifficient for my needs, as I didnt want to have to specify each table in the list. My issue is this: the backend consists of many tables in multiple mdb's, but they (the mdb's) will always be in the same directory (whichever one the user selects). So, i wanted to have it check for connections at startup, and if not found to be valid (if they were moved to another machine lets say) then have it say something like "Where is the new data path?" Since all the backend tables would be in various mdb all residing in the same chosen directory, i just wanted the user to supply the path, and then have it relink the tables to that supplied path without having to individually select the tables (I dont want the user to have to relink approx 45 tables manually).... make sense? Im about 80% of the way there...i just need some extra help to make it wok as designed. > Due to your inquiry i'm rewriting part of the code provided to the adress i > suggested to you... [quoted text clipped - 9 lines] > > Double Thanks for the reply. It seems as if anywhere on the boards if you mention 'relinking" then dev a's code is mentioned....it works, but it demands that you select individual mdbs for each table....laborious for my purposes... I have backend tables across multiple mdb's (approx 45) At startup, I would like to check that the current linked path is valid (from an unbound form) , and if not, then ask the user to select a directory path (not an mdb) where the mdb's are. Since all the tables will be in the various mdb's in the same directory, i just wanted to have the user select the directory, and have the relinking occur with that directory as a constant path....letting the code relink the individual tables to the various mdb's.... I used the browse code below to place an unbound field on the form that accepts the results of the browse button...(hooray!), but i can find a way yet to get it to use that path in the field as a constant value during the relink process Dim strFolderName as string me.[MyfieldName] = BrowseFolder("Where is the data now? Choose a folder... ?") What do u think? > Due to your inquiry i'm rewriting part of the code provided to the adress i > suggested to you... [quoted text clipped - 9 lines] > > Double Question: are your back end databases "one table for database"? if so you can use the path of the folder you input and the table name joined together as the path for the linked table in the statement: For i = collTbls.Count To 1 Step -1 next No. the backend consists of many tables strewn across multiple mdb's. Im about 80% of the way there.... I have sample code available if anyone wishes to get a look at it... \ so, you have to write the "connection code" fro each single table!!! something like this: const BackendDatabaseName1 = "<database1 Name>" const BackendDatabaseName2 ="<database2 Name>" strDBPath=<dir where is the database> & "\" & BackendDatabaseName1 CurrentDb.TableDefs(<table1 name>).Connect = ";Database=" & strDBPath CurrentDb.TableDefs(<table1 name>).RefreshLink strDBPath=<dir where is the database> & "\" & BackendDatabaseName2 CurrentDb.TableDefs(<table2 name>).Connect = ";Database=" & strDBPath CurrentDb.TableDefs(<table2 name>).RefreshLink it's the only way.. you have to write some code's line but then it will reconnect automaticaly all table!! -> You can't use a routine!!! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.