[AccessD] Swapping Connections to SQL Server Back-end Databases

[McGillivray, Don]

Hello All,

I recently converted our MS Access application back-end to SQL Server.  Our shop has three separate SQL Server environments - Dev, Test, and Prod.  The application front-end is an Access accde whose master copies (one each for Test and Prod) are kept in separate folders on a file share.  Users have desktop icons for each environment, which execute batch files to copy down the latest accde from the appropriate share to the appropriate local folder (one for Test, another for Prod), after which the local copy is executed.  Upon start up, the application determines which environment it was executed from and establishes a connection to the appropriate SQL server back end.  The connection attributes are stored as global constants in a VBA module in the front end.

Typically, before compiling and deploying a new version of the accde to Test and Production, the development copy has been attached to the Dev back end.  Thus, the accde will connect to the Dev back-end at start up by default, but the start-up routine will change it according to the environment the user has executed from.  All works as intended for Test and Prod from the user's perspective.  However, when I re-open the development copy (using the shift-key bypass to prevent the start-up routine), that connection (to the Dev back end) is established as read-only.  It was read-write when I compiled and closed the development copy.  I have a tool in the application that allows me to swap back-ends in development as needed, and when I use that to swap to Test or Prod, I get read-write access to both of those.  If I then use the tool to switch back to Dev, it's read-only again.  The only way I can get read-write to Dev is to swap to Test or Prod, close the dev copy of the application, and re-open it.  However, when I do that I have the same read-only behavior for whichever environment was connected when I last closed the application front end. Both the switching tool and the start-up routine call the same function to do the back-end switching. 

All of this won't really impact the users - until I forget to switch to DEV before deploying, which someday I'm sure to do.  I suspect that there may be something about the way my switching code is re-connecting to the back-end that is at the root of my problem.  Hoping somebody with more expertise than me can shed some light or offer some advice for improvement.

All suggestions and insights are appreciated.

Don

-------------------------------------
Here's the relevant code (watch for line wrap and odd tab behavior):
-------------------------------------

Function HookMeUp(Optional strEnvir As String) As Integer
	'This runs at startup to establish the connection to the BE
    On Error GoTo ErrorHandle
    Dim lngErrNum As Long, strErrDescr As String
    Dim intResult As Integer
    Dim strCurBE As String
    Dim strSQLConn As String
    
    If Len(Trim(strEnvir)) = 0 Then strEnvir = GetEnvir() 'GetEnvir() discovers the execution environment (DEV, TEST, PROD). If unable to determine, TEST is used.
    strSQLConn = GetSQLConn(strEnvir) 'GetSQLConn() Builds a connect string from the environment's connection attributes stored as global constants
    If Len(strSQLConn) > 0 Then
        strCurBE = CurrentDb.TableDefs("tblLOV").Connect
        If strCurBE <> strSQLConn Then intResult = ChangeEnvirSQL(strSQLConn) 'This function is shown below
    Else
        intResult = 1
    End If

FunctionExit:
    On Error Resume Next
    HookMeUp = intResult
    Exit Function
ErrorHandle:
    lngErrNum = Err.Number
    strErrDescr = Err.Description
    Select Case lngErrNum
        Case Else
            LogEvent lngErrNum, strErrDescr, "Function 'HookMeUp' of Module 'basSystemProcesses'"
            MsgBox lngErrNum & " " & strErrDescr & vbCrLf & vbCrLf _
                & "Error in procedure Function 'HookMeUp' of Module 'basSystemProcesses'"
            intResult = 1
            Resume FunctionExit
    End Select
End Function


Function ChangeEnvirSQL(strTargetDB As String) As Integer
    On Error GoTo ErrorHandle
    Dim lngErrNum As Long, strErrDescr As String
    Dim intResult As Integer
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    
    Set db = CurrentDb()
    For Each tdf In db.TableDefs
        With tdf
	  If Len(Trim(.Connect)) > 0 And Left(.Connect, 4) = "ODBC" Then
                .Connect = strTargetDB
                .RefreshLink
            End If
        End With
    Next
    
FunctionExit:
    On Error Resume Next
    Set tdf = Nothing
    Set db = Nothing
    ChangeEnvirSQL = intResult
    Exit Function
ErrorHandle:
    lngErrNum = Err.Number
    strErrDescr = Err.Description
    Select Case lngErrNum
        Case Else
            LogEvent lngErrNum, strErrDescr, "Function 'ChangeEnvirSQL' of Module 'basLinkTables'"
            MsgBox lngErrNum & " " & strErrDescr & vbCrLf & vbCrLf _
                & "Error in procedure Function 'ChangeEnvirSQL' of Module 'basLinkTables'"
            intResult = 1
            Resume FunctionExit
    End Select
End Function