Part 4: Switching from Production to Beta backends in SQL Server with ease.

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.

About

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

Posted in Access Help
2 comments on “Part 4: Switching from Production to Beta backends in SQL Server with ease.
  1. Giorgio Rovelli says:

    Hi Juan, what’s the code for the ChangeAppTitle procedure?

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Free email subscription

Enter your email address:


Facebook

Twitter

Blog Archives

ITImpact
%d bloggers like this: