Macro to switch Link table from Test to Prod server 1

[jgsteeldev]

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.



Rock Chalk KU!!!!

 

[PWise]

set up 2 servers and all you have to do is change the .connect and refresh the link

Function relink()
Dim Tds As TableDefs
Dim Td As TableDef
Set Tds = CurrentDb.TableDefs
For Each Td In Tds
Debug.Print Td.connect
td.connect = "newconnect"
Td.RefreshLink
End If

Next
End Function

 

[jgsteeldev]

Thanks pwise you have helped get me in the right direction. The only problem now is that it seems like every time a new link is created they have used a new connection string. So i think i will mix your code example with a replace command to change the server names in the string.



Here we go Royals! Here we go!!!!!

 

[jgsteeldev]

I got it working It works perfectly with XP

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, "192.168.0.23", "SQL-BETA")
                        .Connect = strConnect
                        .RefreshLink
                    Case "PROD"
                        strConnect = .Connect
                        strConnect = Replace(strConnect, "SQL-BETA", "192.168.0.23")
                        .Connect = strConnect
                        .RefreshLink
                End Select
                    
                        
            End If
        End With
    Next
    SysCmd acSysCmdRemoveMeter
    DoCmd.Hourglass False
        
End Function

Here we go Royals! Here we go!!!!!

 

[jgsteeldev]

I have a new problem when I copied my code to a 97 database it blew up when I got to the Replace function. Apparently it was not available in 97. Anybody have any idea how to replace text in a string in access 97?


Here we go Royals! Here we go!!!!!

 

[PWise]

no there is no replace in a97
is there that good reason a new connection string connection string was used every time?
if not create 2 constants each with the whole connection string and change the replace with the correct constants.

 

[PHV]

Or play with the InStr, Left and Mid functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jgsteeldev]

I am using the replace becuse I want the module to work on every DB that has been created here. The one constant thing among all of the linked table on all of the access application is the server that it is linked to. This way the server changes to the correct server no matter how the original connection string was created.


Here we go Royals! Here we go!!!!!

 

[jgsteeldev]

I wrote a replace function that works for me in 97

Thx for all the help!!!!

Here we go Royals! Here we go!!!!!

 

[PWise]

my question was why do you have to make a diffrent connection string for each linked table you can use the same connection string for all the linked tables
something like this

const DEV="ODBC;DRIVER=SQL Server;SERVER=SQL-BETA;APP=Microsoft Open Database Connectivity;WSID=SERVER;DATABASE=xxx;Network=DBMSSOCN;Address=SERVER,1433;Trusted_Connection=Yes
"
const PROD="ODBC;DRIVER=SQL Server;SERVER=192.168.0.23;APP=Microsoft Open Database Connectivity;WSID=SERVER;DATABASE=xxx;Network=DBMSSOCN;Address=SERVER,1433;Trusted_Connection=Yes
"
Select Case strOption
Case "DEV"
strConnect = .Connect
strConnect = DEV
.Connect = strConnect
.RefreshLink
Case "PROD"
strConnect = .Connect
strConnect = PROD
.Connect = strConnect
.RefreshLink
End Select

i think that this will be faster then looping truh the whole connection string

 

[jgsteeldev]

Because some of the linked tables are from different databases on the server. So instead of having to figure out which db the string is pointing to I just use the existing connection and flip the server.

Here we go Royals! Here we go!!!!!