Macro to switch Link table from Test to Prod server

[jgsteeler]

I have an interesting problem. I started a new job two weeks ago. I was shocked to find out that the company I now work for does not have a test environment. I mentioned how necessary a test environment is because of the risk to the integrity of the production data and was tasked with setting up a tst environment. Here is my situation.

Most of the applications here are written in Access 97 or Access XP. They are mdb front ends that stroe the data in a SQL Server. All of the connection to the SQL server have been done by linking to the tables using a DSN. I need to be able to create a Macro or maybe a code module that breaks the connections to the production server and re-connects the linked tables to the test server. I also need to be able to go in reverse. I come from a VB6 background where we connected to the database using a UDL so when we need to switch from test to prod we just changed the udl. I have some experience in Acces but no tmuch. But, i told my boss that I am sure that I can set up the test server just fine, So, I need some help.

 

[Birdy]

This is for ORACLE but may help.

We use this on one of our systems.

Look at the attached file. Hold down the shift key when going in as the AUTOEXEC is still active .

we use the Oracle ODBC Drivers, so it may be different for SQL Server.

 

[jgsteeler]

Thanks for the info Birdy.


This is the function I use. I think that connecting to SQL tables must be a little different:

Function relink()
    Dim objTblDefs As TableDefs
    Dim objTblDef As TableDef
    Dim strOption As String
    Dim bValid As Boolean
    Dim strConnect  As String
    
    DoCmd.Hourglass True
    strOption = InputBox("Please type either 'Dev' or 'Prod'.")
    
    
    bValid = False
    Do Until bValid = True
        If UCase(strOption) <> "DEV" And UCase(strOption) <> "PROD" Then
            strOption = InputBox("You did not type 'Dev' or 'Prod'." & vbCrLf & "Please try again.")
        Else
            bValid = True
            strOption = UCase(strOption)
        End If
    Loop
    
    Set objTblDefs = CurrentDb.TableDefs
    Dim count As Integer
    count = objTblDefs.count
    SysCmd acSysCmdInitMeter, "Re-linking tables...", count
    count = 0
    
    For Each objTblDef In objTblDefs
        count = count + 1
        SysCmd acSysCmdUpdateMeter, count
        With objTblDef
            If Left(.Connect, 4) = "ODBC" Then
                Select Case strOption
                    Case "DEV"
                        strConnect = .Connect
                        strConnect = Replace(strConnect, "Production SQLServer", "Test SQLServer")
                        .Connect = strConnect
                        .RefreshLink
                    Case "PROD"
                        strConnect = .Connect
                        strConnect = Replace(strConnect, "Test SQLServer", "Production SQLServer")
                        .Connect = strConnect
                        .RefreshLink
                End Select
                    
                        
            End If
        End With
    Next
    SysCmd acSysCmdRemoveMeter
    DoCmd.Hourglass False
        
End Function

This code works well because there are several different databases on the sql server that are being used. and different people have linked the tables at different times often using different DSNs. The one constatnt in all of the links is the server name. So if i just flip the server name in the connection strings the table are linked to the correct server.