Hi group,



I am writing an app that has data tables in 2 separate mdb databases.

I have some code that prompts for the file location if not found, but

the problem is that it brings up the file open box for every linked table.

Does anyone have any ideas on how to remember where the mdb files are

after being selected once for each different mdb file ??

ie so you only see the file selection dialog twice?



thanks

robert

Hi Robert



I use the code below to reconnect all the linked tables in my DB if the 

connection is lost. You'll need to adapt it to differentiate between 

your two data sources - maybe hard code the table names, rather than 

looping through all the tables in the database.



Public Function RefreshLinks(strFileName As String) As Boolean

' Refresh links to the supplied database. Return True if successful.



    Dim dbs As Database

    Dim tdf As TableDef



    ' Loop through all tables in the database.

    Set dbs =3D CurrentDb

    For Each tdf In dbs.TableDefs

        ' If the table has a connect string, it's a linked table.

        If Len(tdf.Connect) > 0 Then

            tdf.Connect =3D ";DATABASE=3D" & strFileName

            err =3D 0

            On Error Resume Next

            tdf.RefreshLink         ' Relink the table.

            If err <> 0 Then

                RefreshLinks =3D False

                Exit Function

            End If

        End If

    Next tdf



    RefreshLinks =3D True        ' Relinking complete.

   

End Function



-BDS

-----Original Message-----

From: Robert Stein [SMTP:Robert.Stein@B...]

Sent: 30 August 2001 05:23

To: Access

Subject: [access] relinking tables from >1 database



Hi group,



I am writing an app that has data tables in 2 separate mdb databases.

I have some code that prompts for the file location if not found, but

the problem is that it brings up the file open box for every linked 

table.

Does anyone have any ideas on how to remember where the mdb files are

after being selected once for each different mdb file ??

ie so you only see the file selection dialog twice?



thanks

robert

This is from the hip, but maybe you could first save the old, invalid

setting of the .Connect property off to a variable (strBadConnect).  Then

you prompt the user for a new path, & store same in a different var, e.g.



strGoodConnect = ";DATABASE=" & PromptForPath()



Then loop through all the tables & selectively replace the .Connect

properties for the tables linked to the invalid db, e.g.,



If tbl.Connect = strBadConnect Then

   tbl.Connect = strGoodConnect

   tbl.RefreshLink

End If



HTH,



-Roy



-----Original Message-----

From: Robert Stein [mailto:Robert.Stein@B...]

Sent: Wednesday, August 29, 2001 9:23 PM

To: Access

Subject: [access] relinking tables from >1 database





Hi group,



I am writing an app that has data tables in 2 separate mdb databases.

I have some code that prompts for the file location if not found, but

the problem is that it brings up the file open box for every linked table.

Does anyone have any ideas on how to remember where the mdb files are

after being selected once for each different mdb file ??

ie so you only see the file selection dialog twice?



thanks

robert

I have a mechanism that uses some code and four tables, one for the listing

all (connected) tables, one for the datatypes to distinguish between the two

types I'm using, one for connection strings to the source database, one for

the working enviroment.



[tblTables]  lists the Table Name and the Data Types 

[tblDataType]  lists the valid DataTypes 

[tblConnections] connect strings for each data source

[tblConnType] the Connection types, I use 'Live', 'Test', 'Quarantine'



The routine loops through the list of tables ordered by datatype, 

reads the datatype, 

looks up the connect string for that type and performs the reconnect.  



My current system reconnects 49 tables from 2 database (A97 MDB's) across a

100BT Novell network.  I coded my system since I between test and production

enviroments frequently so it's just a click or two for me.  



Timing is ok at @12-20 seconds, but high traffic will adversely impact.  I

believe the Add-In, Linked Table Manager is probably using ADO, and it is

quick @3-4 seconds in my environment.  



The trick to not getting prompted for each connect is to group the connect

request so all tables for the particular connect are together.  Cut and

paste

helps.  But you can end up pretty dizzy.



I use DAO, but believe this can be better(faster) with ADO connection

methodology.  Most of the ideas and code I use came from the Access

Developers

Handbook and Dev Ashish's website.  I can make a copy of my solution

available

if desired.



Tom Hamilton

T_Systems, Inc





>>> Pardee, Roy E 08/30/01 07:43AM >>>

This is from the hip, but maybe you could first save the old, invalid

setting of the .Connect property off to a variable (strBadConnect).  Then

you prompt the user for a new path, & store same in a different var, e.g.



strGoodConnect = ";DATABASE=" & PromptForPath()



Then loop through all the tables & selectively replace the .Connect

properties for the tables linked to the invalid db, e.g.,



If tbl.Connect = strBadConnect Then

   tbl.Connect = strGoodConnect

   tbl.RefreshLink

End If



HTH,



-Roy



-----Original Message-----

From: Robert Stein [mailto:Robert.Stein@B...] 

Sent: Wednesday, August 29, 2001 9:23 PM

To: Access

Subject: [access] relinking tables from >1 database





Hi group,



I am writing an app that has data tables in 2 separate mdb databases.

I have some code that prompts for the file location if not found, but

the problem is that it brings up the file open box for every linked table.

Does anyone have any ideas on how to remember where the mdb files are

after being selected once for each different mdb file ??

ie so you only see the file selection dialog twice?



thanks

robert