refresh link in code or other method 
Author Message
 refresh link in code or other method

i have following files in my c:\test folder


class.mdb contains my forms,reports etc which linked to classdata.mdb tables

i develop my database at home. when i email my program to the users, they
put in f:\apps which cannot link to classdata.mdb properly.

is there anyway to code the program to link properly on the first use? is
there any other good method? i do not have f: drive at home.

thanks for help.

song su

Thu, 08 Jan 2004 01:05:14 GMT  
 refresh link in code or other method
Hi Song,

Here is a sample code to refresh linked table from someone within this
newsgroup. You can also search  the original response in this newsgroup.

Hope this helps!

Simon Liao

I got tired of using the Linked Table Manager to move between development
and production environments especially when the linked tables spanned
multiple ODBC and/or Access databases. So I wrote a little routine to help.

You need an Access table in your application called
DevelopmentProductionNames with 3 fields (DevelopmentName text 255,
ProductionName text 255, and id autonumber primary key). Populate with your
own development and production connection names. You can walk through the
routine before populating and see the current names -- just use a
"debug.print db.tabledefs(i).connect" to see them. Once the table is
populated, run the subroutine, it will prompt you for direction in which
you want to relink. You can easily toggle either direction

Sub ChangeLinkedTableConnection()

On Error GoTo ErrorHandler
    Dim db As DAO.Database, i As Integer, DevToProd As Integer, CurrentName
As String
    Dim NewName As String, rst As DAO.Recordset, Criteria As String,
FieldID As Integer
    Set db = CurrentDb

' SET DevToProd to vbYes if you want to relink tables from development to
production otherwise
' SET DevToProd to vbNo if you want to relink tables from production to
    DevToProd = MsgBox("Do you want to relink from development to
production?", vbYesNo + vbDefaultButton2)
    If DevToProd = vbYes Then
        CurrentName = "DevelopmentName"
        FieldID = 1
        CurrentName = "ProductionName"
        FieldID = 0
    End If

    Set rst = db.OpenRecordset("DevelopmentProductionNames",dbOpenSnapshot)
    For i = 0 To db.TableDefs.Count - 1
        Criteria = CurrentName & " = '" & db.TableDefs(i).Connect & "'"
        rst.FindFirst Criteria
        If rst.NoMatch = True Then
            Debug.Print "Cannot relink "; db.TableDefs(i).Name
            db.TableDefs(i).Connect = rst.Fields(FieldID).Value
        End If
    Exit Sub

    If Err = 3192 Then Resume Next 'This skips tables which are not linked
anywhere else

End Sub

Thu, 08 Jan 2004 12:49:01 GMT  
 [ 2 post ] 

 Relevant Pages 

1. Using VBA code to refresh links

2. Refreshing Link to Excel97 Spreadsheet with Code

3. Data Control Refresh works sometimes, others Not?!

4. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

5. Basic Links - Any others?

6. Linking Access Tables in Different *.mdb Files, Populating an Access Table From Several Others

7. Updating Links in Word 2002, Edit, Links, Select Update Method

8. Print method and others

9. Print method and others

10. Outlook Apointment deleting and still running code to notify others

11. Refresh Links

12. Refresh Link


Powered by phpBB® Forum Software