Q:

MS Access switching connection between test server and production SQL Server

I'm developing a new MS Access 2013 desktop app with an Azure SQL Database backend. I plan to set up two backend servers, one for production, and one for testing.
Let's say I do some development on the front end Access app while connected to the test server, and when it comes to deployment I'd like to connect that front end app to the production server. How do I change the connection? Would I have to re-link all the tables?
Is there a better workflow for doing development on a test server, then deploying an app that is connected to production server?

A:

You can achieve this with DNS-LESS method and a config file for your application.
Logic:

Your Application has an AutoExe macro which fires on application start and checks if its dev or production environment and updates all linked tables/queries.
In your AutoExe you read a config file and retrieve server details. (your development folder will have a config file with development server details and your publishing package will contain production server details)
Ideally generate a FN_GET_CONNECTION_STRING function which will read your config file and build the connection string for you
Loop through all linked tables and update the "connect" connection string with the new server details.
Only update linked tables when the application run for the first time and remember this in either your config file or a local table. Any further runs does not need to update the server details.
Provide your users a way to manually re-sync the tables

some steps:
Private Function FN_REFRESH_CONNECTIONS(Optional iForce As Boolean = False) As Boolean
'read through all linked tabled and update the connectionstring, if force is set as true, update the connection string and connect to the actual server(refreshlink)
For Each tdf In db.TableDefs
        If tdf.connect <> vbNullString Then
                If Not FN_CONNECT_TABLE(tdf, iForce) Then
                    Err.Raise 1, Err.Source, "Driver missing error " & Err.description
                End If
                myCurrCount = myCurrCount + 1
                lbl_count.caption = myCurrCount & " of " & myCount & "- Done : please wait hard linking is in progress"
                DoEvents
            End If
    Next tdf
End Function

Public Function FN_CONNECT_TABLE(ByRef iTdf As dao.TableDef, iConnect As Boolean) As Boolean
    ' This function takes tablename and connects to the backend server
    FN_CONNECT_TABLE = False
    On Error GoTo Final_Error:
    iTdf.connect = GET_CONNECTION_STRING & "TABLE=" & iTdf.name
    If iConnect Then iTdf.RefreshLink 
    FN_CONNECT_TABLE = True

    Exit Function

Final_Error:
    FN_CONNECT_TABLE = False
End Function

get connection string function is where you read your config file and build your connection string. mine is bit long as I have SSL and different server profiles but here is some code how you create and read config.ini.
Option Compare Database
Option Explicit

Declare Function GetPrivateProfileString Lib "kernel32" Alias _
    "GetPrivateProfileStringA" (ByVal lpApplicationName As String, _
    ByVal lpKeyName As Any, ByVal lpDefault As String, _
    ByVal lpReturnedString As String, ByVal nSize As Long, _
    ByVal lpFileName As String) As Long

Declare Function WritePrivateProfileString Lib "kernel32" _
    Alias "WritePrivateProfileStringA" _
    (ByVal sSectionName As String, _
    ByVal sKeyName As String, _
    ByVal sString As String, _
    ByVal sFileName As String) As Long

Private Const mINI_PATH = "Config.ini"
'Read more at http://vbadud.blogspot.com/2008/11/how-to-read-and-write-configuration.html#mQciBJHBBX5cE52E.99

Public Function FN_GET_INI_VALUE(ByVal strSectionName As String, ByVal strEntry As String) As String

    Dim X As Long
    Dim sSection As String, sEntry As String, sDefault As String
    Dim sRetBuf As String, iLenBuf As Integer, sFileName As String
    Dim sValue As String

    On Error GoTo ErrGetSectionentry
    sSection = strSectionName
    sEntry = strEntry
    sDefault = ""
    sRetBuf = Strings.String$(256, 0) '256 null characters
    iLenBuf = Len(sRetBuf$)
    sFileName = FN_GET_BASE_PATH & mINI_PATH
    X = GetPrivateProfileString(sSection, sEntry, _
    "", sRetBuf, iLenBuf, sFileName)
    sValue = Strings.Trim(Strings.Left$(sRetBuf, X))

    If sValue <> "" Then
        FN_GET_INI_VALUE = sValue
    Else
        FN_GET_INI_VALUE = vbNullChar
    End If

ErrGetSectionentry:
    If Err <> 0 Then
    Err.Clear
    Resume Next
    End If

End Function

Public Function FN_SET_INI_VALUE(iSection As String, iItem As String, iValue As String) As Boolean
    On Error Resume Next
    Dim ret As Variant
    Dim mDummy As Variant
    mDummy = FN_GET_INI_VALUE(iSection, iItem)
    ret = WritePrivateProfileString(iSection, iItem, iValue, FN_GET_BASE_PATH & mINI_PATH)
    FN_SET_INI_VALUE = ret > 0
End Function

and then i read server details by reading the config.ini file to build my connectionstring.
 DBNAME = Nz(FN_GET_INI_VALUE(prod-server, "MySQL-DB-Name"), "")

.. dbserver =  Nz(FN_GET_INI_VALUE(prod-server, "MySQL-SERVER"), "")
.. dbpassword i kept this hard-coded for security purpose

my config.ini looks something like this:
[prod-server]
ip=
ip1=
ipv6=
server-name=
web-port=8081
web-server-name=
ftp-port=21
ftp-use-tsl=true
MySQL-SSL-Enabled=
MySQL-Port=3306
MySQL-User=
MySQL-DB-Name=

and i built connection_string like:
CON = "ODBC;DRIVER={" & Driver & "};PORT=" & mPort & ";DATABASE=" & mDatabase & ";SERVER={" & mServer & "};User={" & mUser & "};Password={" & mPassword & "};"
'Where driver is you odbc driver or your custom driver. you can manually set the driver or get it by reading odbc driver section in the registry.

well good luck to you.. :)

