Is there a utility to switch from dev to test databases? 1

[cjowsey]

I have just started using Access in anger in a development and production scenario with SQL Server. I have inherited an incomplete system that needs tidying. Normally when I develop systems, I have four separate environments (development, test, production and production backup). It seems an obvious thing to me, but I can't find any mention anywhere of any sort of utility to reset database links when an application is migrated into test or production.

Is there some simple magic trick that I can learn rather than relinking each time the application is migrated? Preferably, has anybody written a tool to do this rather than make manual adjustments in a production environment? In recent years I have become more and more convinced that developers should not be allowed to make unscripted and uncontrolled changes in production and I would really like to automate this if possible.

I am a bit concerned that I haven't seen this question asked before which makes me think I am missing soemthing very simple here.

 

[allanon]

I made a new form for developer use only and placed two buttons on it. Set to Test. Set to Production.

this assume ODBC connection to sql server.

1) place this code in a module:

Function GetLinkedDBName(TableName As String)
Dim db As DAO.Database, Ret
On Error GoTo DBNameErr
Set db = CurrentDb()
Ret = db.TableDefs(TableName).Connect
GetLinkedDBName = Ret
Exit Function
DBNameErr:
GetLinkedDBName = 0
End Function

Function CreateODBCLinkedTables(strTblName As String, strDataBase As String, _
strDSN As String, strUID As String, strPWD As String)
Dim strConn As String
Dim tbl As TableDef
Dim db As Database

On Error GoTo CreateODBCLinkedTables_Err
Set db = CurrentDb()
strConn = "ODBC;"
strConn = strConn & "DSN=" & strDSN & ";"
strConn = strConn & "APP=Microsoft® Access;"
strConn = strConn & "DATABASE=" & strDataBase & ";"
strConn = strConn & "UID=" & strUID & ";"
strConn = strConn & "PWD=" & strPWD & ";"
strConn = strConn & "QueryLog_On=No;StatsLog_On=No;Regional=Yes"
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
CreateODBCLinkedTables_End:
CreateODBCLinkedTables = 0
Exit Function
CreateODBCLinkedTables_Err:
MsgBox Err.description, vbCritical, "DB Connection"
Resume CreateODBCLinkedTables_End
End Function

2) Place this code in the click event for your set test:

Private Sub Command4_Click()
Dim varItem As Variant
Dim dummy As Variant

For Each varItem In CurrentDb.TableDefs
If InStr(GetLinkedDBName(varItem.name), "MyTableNames&quot Then
dummy = CreateODBCLinkedTables(varItem.name, "MyTestDatabaseName", "MyTestDSNName", "MyTestUserID", "MyTestPassword&quot
End If
Next varItem
cmdClose.SetFocus
End Sub

3) Place this code in the click event for your set production:

Private Sub Command3_Click()
Dim varItem As Variant
Dim dummy As Variant

For Each varItem In CurrentDb.TableDefs
If InStr(GetLinkedDBName(varItem.name), "MyTableNames&quot Then
dummy = CreateODBCLinkedTables(varItem.name, "ProductionDatabase", "ProductionDSN", "ProductionUserID", "ProductionPassword&quot
End If
Next varItem
cmdClose.SetFocus
End Sub

NOTE: The "MyTableName" is something that you put in each table name to identify tables that will change for the dsn you are setting. For example, I have three databases in my app BUT only one has updatable tables so that is the only one that needs to change DSN therefor each of those tables start with "cma_". That way only they get changed. You can create your own system.

 

[cjowsey]

Thanks for the prompt and thorough feedback. This looks like it will do the job vey well. I'll wrap some security around it and stick it in my release process.

This will be a two stage process. First I'll put it in the app to prove it, then I'll take it out and put it in the security application we use to lock down the production adp. I don't like having backdoor or developer specific code in production, so I will keep it safely off to the side.

Thanks again, you're a champ.

Cheers,
Clive