The following instructions will allow you to upsize your Close Support data to SQL Server version 6.5 or above. This facility is available in the Advanced Desktop and Enterprise Edition.
It is assumed that you have a reasonable level of knowledge about SQL Server.
Preliminaries:
You need to have Microsoft Access version 2000 or above. Check also that you have the latest version of Close Support - currently 2.95.02t. You can tell which version you have by clicking on Help, About Close Support. It is very important to have the latest version of Close Support AND HAVE OPENED IT TO EDIT A CALL before attempting to upsize. This will ensure that you have the latest table design before the tables are transferred to SQL Server.
Ensure that no users are in Close Support.
Take a backup copy of the current CloseSupportBE.mdb
Step one:
Create a database on SQL Server - name it "CloseSupportSQL". Ensure that the security settings on this database are suitable for your organisation's security and use of Close Support. In general this will mean providing Select, Update, Insert and Delete rights to Close Support users on all tables in the database. Additionally, if you are using the Close Support Web Module you will need to ensure that your IIS user (IUSR_
Step two:
Create an ODBC Data Source on your PC as a System DSN - name it "CloseSupportSQL"
The connection options and security types are down to you. If you are not sure then talk to your SQL Server database administrator. What you must do is ensure that the DSN is pointing at the CloseSupportSQL database created in the previous step. The following screenshots show a typical sequence for creating the required Data Source.
Step three:
Upsize the database to SQL Server.
Using MS Access, open CloseSupportBE.mdb. If you have been using Close Support and it has live data, have a quick look in the CS_CALLS table to make sure that you are in your "live" copy of the backend database.
Depending on the version of MS Access:
2000 - Click on Tools | Add-Ins | Upsize to SQL Server
2003 - Click on Tools | Database Utilities | Upsizing Wizard
2007 - Click on Database Tools, SQL Server (in the Move Data section)
The Wizard will lead you through the process of creating the database and transferring the data. The specific screens vary for each version of MS Access but the principles remain the same.
Click on Next
Select your data source as previously defined and click on OK
You want to export all the tables so click on the double right-pointing arrows. Click on Next.
Set options as shown and click on Next.
Choose "Create upsizing report" and click on Finish.
If you get a message warning about locks then:
- if you have SQL Server 7 or above ignore it (click on OK) - locks are dynamically managed by SQL Server
- if you have SQL Server 6.5 seek the advice of your DBA on increasing the locks available
The Wizard will show progress as it upsizes the tables - the more data in them the longer this will take. But in most cases the process will be over in a few minutes.
After the process has ended you will be presented with a 20 - 30 page report. You may wish to print it out for reference.
Step four:
Making sure that the new tables meet the standard design.
In your Close Support installation folder (C:\Program Files\Close Support\) you will find a script, 2.95.02i.sql, that needs to be run against he CloseSupportSQL database. You can use SQL Server's Query Analyzer to do this.
Step five:
Linking the Close Support front end to the SQL Server tables.
- Launch Close Support
- Click on Options, Database settings
- Click on the SQL Server "Connect" button
For each additional PC installation of Close Support, repeat steps two and five only.
Step Six:
If you are using the Close Support Web Module, you will need to change the CloseSupportWeb ODBC DSN on your web server so that it points at the SQL Server database. You will also need to edit the file functions.inc so that line 5 in the file reads:
strMode="SQL Server"
Upsizing Complete