Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2011
    Posts
    2

    Angry Unanswered: RefreshLink still opens previous connection.

    Hi all, my first ever forum post...that tells you how stuck I am!

    I have some tables linked to a UAT odbc database, server1, uatdb1.

    I programatically want to switch between this database, and the production one. server2, proddb1.

    My code is as follows:

    Public Function ReconnectDb(dbType As String) As Boolean

    Dim szConnect
    Dim tdf As TableDef
    Dim szDb As String

    Select Case dbType
    Case "UAT"
    szConnect = "ODBC;DRIVER={Sybase ASE ODBC Driver};SERVER=,;NA=;SRVR=server1;DB=uatdb1;"
    Case "PROD"
    szConnect = "ODBC;DRIVER={Sybase ASE ODBC Driver};SERVER=,;NA=;server2;DB=proddb1;"
    End Select

    For Each tdf In CurrentDb.TableDefs
    If Left(LCase(tdf.Name), 4) = "dbo_" And Left(UCase(tdf.Connect), 4) = "ODBC" Then
    tdf.Connect = szConnect
    tdf.RefreshLink
    End If
    Next

    CurrentDb.TableDefs.Refresh

    End Function


    Now, when I switch from UAT to Production, MSysObjects shows the new link, the tooltop pop-up shows the new link, but when I open one of tables I still see my UAT data and not the production data that Access says I'm connected to (and vice-versa).

    When I close access, and reopen it, then the links are correct.

    I don't want my users to have to restart Access each time...why does RefreshLink not do what I want.

    thanks in advance for any help

    trebur

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try Deleting and recreating the tabledefs.
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    2
    That doesn't work either.

    If I recreate the tabledef with the new connection string, MSysObjects shows the new connection string...the tool tip pop-up shows the old string, and the database is still connected to the original database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •