Auto Relinking Tables

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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