Go to Top

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.

MS Access Report Generator

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.

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.


*UPDATE* Code for ChangeAppTitle follows:


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

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.

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

  • Giorgio
    June 27, 2018 - 5:28 pm Reply

    Are you sure the Function BetaTesting() is coded correctly? For example line 10 already gives me a syntax error.

  • Giorgio Rovelli
    April 23, 2012 - 4:22 pm Reply

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

    • Juan Soto
      April 23, 2012 - 8:16 pm Reply

      I have updated the post to include the code you requested.

Leave a Reply

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


Contact Us
close slider
  • This field is for validation purposes and should be left unchanged.