How To Upsize to Microsoft's SQL Server

Step 1: Open the 20/20 DataShed database in Microsoft Access.

You must have Microsoft Access installed to proceed. If you do not have Microsoft Access, then:

• it's quite likely that you do not need to upsize the database.
• or you should find a knowledgable database administrator or webmaster to perform the following steps for you.

1. Access your web site with an FTP application such as CuteFTP, FTP Explorer, or Windows "My Network Places".
2. Locate and download the .MDB file from your web site. By default this file is called 2020applications.mdb and is located in the parent folder, but you may have renamed and/or relocated it.
3. Open Microsoft Access.
4. Select "File" then "Open..." from the menu. The "Open" file dialogue box will open.
5. Navigate to the folder which contains the database that you have downloaded from your web site and select .MDB file to open.
   
   
   
6. Microsoft Access may prompt you to enter the database password. To learn your password you may login to your customer account at www.2020applications.com, then click "Account Transactions" and look for the License ID.
   
   
   
7. The database will then open and display the default view.
   
   
   

Step 2: Upsize the database using the "Upsizing Wizard" in Microsoft Access.

1. With the 20/20 DataShed database already open in Microsoft Access, select "Tools" then "Database Utilities" then "Upsizing Wizard" from the menu.
   
   
   
   The "Upsizing Wizard" will open.
2. Select the option to "Create new database" then click "Next >".
   
   
   
3. In the next screen of the wizard you will instruct MS Access which SQL server you would like to connect to. There are three important settings:

   Server Name/Address

   This can be either a domain name: perhaps sql.my_website.com?

   This can be an I.P. address.

   Or this can be a computer name if the SQL Server is located on a local network. For example, to build the screen shots for this article I am currently using a Windows XP machine with the MSDE so the database is on my "localhost" and my local computer's name is "EARTH". I can address this computer by typing "EARTH" or "127.0.0.1" or "localhost".

   User Account

   Although it is usually recommended that you not use the built-in "sa" user account it is important that you use a username and password for this process which has sufficient privelages to create a database and tables. You may connect to the SQL Server with any user account which is granted such permissions - if the "sa" account is the only account with that authority then you may either use the "sa" account or create a special user account with only the desired authority.

   Database Name

   Choose a new name for the SQL Server database. You may choose any name.

   Type the server name/address, a user account login and password, and a name for the database.
   
   
   
   Note that you will need to know these settings later! We recommend that you write them down and store them in a safe place.
4. In the next screen of the wizard, select the tables to export. There should be eleven (11) tables to export.
   
   
   
5. The next screen of the wizard displays options to export table attributes. We can leave all the settings at their default values and click "Next >".
   
   
   
6. The next screen of the wizard displays options to create or modify an application to work with the new SQL database. We recommend that you leave the default setting, "No application changes" and click "Next >".
   
   
   
   Note that you may create a new Access project (.ADP file) later if you wish.
7. That is the last step in the wizard. Click "Finish" to complete the process.
   
   
   

Step 3: Modify the _2020applications_connection.asp file to enable 20/20 DataShed to connect to the newly created SQL Server database.

1. Access your web site with an FTP application such as CuteFTP, FTP Explorer, or Windows "My Network Places".
2. Download _2020applications_connection.asp from the parent folder of this application to your local computer.
3. Open "_2020applications.asp" in a text editor such as Notepad. We recommend that you do not use a WYSIWYG editor like Frontpage or Dreamweaver for this purpose.
4. Change the contents as necessary. Each variable is discussed in detail directly in the source code and some additional information is available here). Please ensure to review at least the following variables in that file:
   • intDataProviderOption: (We recommend option 5)
   • strDatabaseFileName
   • strSQLDataSource
   • strSQLAddress
   • strSQLUid
   • strSQLPwd
5. Save the file.
6. Upload the file to your web site using the FTP application and overwrite the existing _2020applications_connection.asp file.
7. The process is now complete. The new settings will be loaded the next time the web server's cache (Application Cache) is refreshed. You can refresh the application cache manually so that your changes are implemented immediately: visit the objApplication=RemoveAll section in _globals.asp.
8. Note: At this point, if you have created a syntax error or have used invalid or inaccurate variables, you will see an error message on any/every page produced by 20/20 DataShed. Fix the error, or undo your changes, or upload the original (working) copy immediately -- then refresh the application cache again.
9. If your settings are accurate and if 20/20 DataShed is able to connect to your SQL database, then you will be able to continue using 20/20 DataShed exactly as you were with MS Access - however you will now enjoy the more robust performance benefits of the SQL Server.