Author note: This is the final post on a four post series on DSN-less tables in Access.

You can review part one here.

Take a look at part 2 here.

See part 3 here.

How can you test changes in the cloud with your Beta users?

Once you’ve rolled out your Access masterpiece using SQL Server in the cloud, your client’s will love your work so much they are going to request additional changes. (Hint: If you don’t get upgrade requests then it wasn’t a masterpiece to begin with). It’s easy to test changes on a local SQL Server database, but more often than not you will need to have a select group of your app users try the changes before rolling them out to everyone, and that means testing using the cloud.

Use a SQL Server Beta database to test changes before going live

SQL Server makes it easy to create a Beta database, just copy the live database or restore a full backup and give it a different name. Typically, we give ours the same name with “Beta” suffix. We usually use the production server as our Beta server in order to test the system under real world conditions but you don’t have to. Once you have a Beta database up and running use the code below to easily switch from Beta to production and back.

How to change to Beta: Just rename your Access file

In our apps, to switch to our SQL Server Beta database we just add the word Beta to our Access file. Having to rename the file and not change any code makes life easier and allows us to experiment with data without impacting operations and ensures that both beta and production are functionally identical. Our client also uses the technique for training new users on the system.
Public Function BetaTesting() As Boolean
10 If CurrentProject.Name = ReadGV("ProjectFileName", 1) Then
20 BetaTesting = False
30 TempVars.Add "BetaTesting", False
'Change from Beta to production?
40 If InStr(1, CurrentDb.TableDefs(ReadGV("BetaTableTest", strText)).Connect, ReadGV("BetaCatalog", strText)) > 0 Then
'Relink to production database
50 ChangeAppTitle ReadGV("ProjectTitle", strText)
60 RelinkAllTablesADOX
70 End If
80 Else
90 BetaTesting = True
100 TempVars.Add "BetaTesting", True
110 ChangeAppTitle "++++++++ BETA " & ReadGV("ProjectTitle", strText) & " BETA +++++++++"
120 If InStr(1, CurrentDb.TableDefs(ReadGV("BetaTableTest", strText)).Connect, ReadGV("BetaCatalog", strText)) < 1 Then
'Relink to Beta database
130 RelinkAllTablesADOX
140 End If
150 End If
End Function
In line 10 we test if the current file name is the production version stored in our tblProgramOptions table, otherwise we are dealing with a Beta version of the system. (Click ReadGV to see a post on using ReadGV).

If it’s a production database then in line 30 we set a global variable for use later in the app. In line 40 we test if the table links are pointing to Beta and if so relink all tables to the production database in line 60.

If it’s a Beta database then in line 90 we define a global variable, change the app title and relink all tables to the Beta database in line 130. (Click on RelinkAllTablesADOX to view a post on how to relink all of your tables.)

Since we name our SQL Server database by just adding Beta to the name we can use the following lines in RelinkAllTablesADOX to switch to Beta.
If TempVars!IsBeta Then
strCatalog = conCatalog & "Beta"
Else
strCatalog = conCatalog
End If
 

*UPDATE* Code for ChangeAppTitle follows:
Public Sub ChangeAppTitle(strTitle As String)
Dim proTitle As Property

On Error Resume Next
With CurrentDb
Set proTitle = .CreateProperty("AppTitle", dbText, strTitle)
Call .Properties.Append(proTitle)
.Properties("AppTitle").Value = strTitle
End With
Call Application.RefreshTitleBar
End Sub
 

Series Conclusion

I hope all of these posts will help you take advantage of DSN-less tables in your app, let me know in the comments if you do use the code and how it’s helped you.