Using MS SQL Databases (optional extra)
What is MS SQL?
Microsoft SQL Server is a high performance database which can be used in place of Access databases for your website. MS SQL Server databases can be used in your ASP/Perl/iHTML scripts through the use of an ODBC data source.Note: You will need a good understanding of MS SQL in order to use an MS SQL database on your website. Although we provide the service, we do not provide consultancy on setting up your database.
Using Enterprise Manager to Manage Your Database
You can connect to our SQL Server though SQL Server Enterprise Manager which is included with MS SQL 7.Open the Enterprise Manager on your local computer and select New SQL Server Registration. Click OK to go on to the next page.You will be presented with a screen similar to the following:
Enter the IP Address of the SQL Server in the Server box as this will be the server we are going to connect to. Switch the connection method to Use SQL Server Authentication and enter the username/password you set on your SQL Server database in the Control Panel. It is left optional to yourself whether or not you have Always Prompt For Login Name and Password enabled. Next click OK.
If you receive an error (for example "Specified SQL Server Not Found") then you will need to switch the protocol you computer is using to connect to the SQL Server. Ensure this mode is set to TCP/IP - this can be changed using the Client Network Utility.
If a successful connection is made, you should be to expand all the databases installed on the SQL Server. The only database you will be able to access is the one you set up. To add a new table to the database, expand the Console Root to display your database and right click Tables.
You will then be asked to specify a name for the new table. After entering the name, you will be put into the design mode of the table.
Setting up an ODBC Data source for use with MS SQL databases
Before you can transfer your existing Access database to MS SQL, you will need to Upsize it. This article describes how to set up an ODBC data source on your local machine to communicate with the MS SQL Server.Open the ODBC Data sources applet in the Control Panel on your local machine. Change the tab to System DSN and click Add. Select the SQL Server driver type and click Finish.
The first page of the wizard that appears after you click Finish will ask you to specify identification information about your data source and the SQL server you wish to connect to. Choose a name and a description for your data source. Enter the IP address of the SQL Server in the "Which SQL Server do you want to connect to?" box. Click Next.
Switch the authentication method to "SQL Server Authentication" and check the box "Connect to SQL Server to obtain default settings for the additional configuration options". Enter the Login ID and password to the ones you have set-up when you added the SQL Server database in the Web Control Panel. Click Client Configuration to bring up the options for connecting to the SQL Server.
Change the networking mode to TCP/IP and enter the IP address of the SQL Server in the Computer Name box. Ensure port 1433 is entered in the Port Number box. Click OK to close this window.
Change the default database to your SQL Server database which should appear in the list. Click Next, and then click Finish.
Finally, you can click Test Data Source to verify you can connect to the SQL Server. If all tests pass you will get a Tests Completed Successfully message. If you don't then you may have mis-configured one of the settings.
Upsizing an Access 2000 database
After you have designed your database in Access 2000, initiate the Upsizing Wizard by selecting it from the Tools/Database Utilities menu in Access.
![Microsoft Access - [db1 : Database]](1.gif)
Select the Use Existing Database option from the wizard as we will be using a pre-defined ODBC data source to connect to the SQL Server.
Switch to the Machine Data Source tab and select the ODBC data source which you have pre-defined to connect to the SQL Server and click OK to continue.
Enter the username and password you set up on your SQL Server database and click OK to continue.
Now select the tables you wish to export to the SQL Server database and click Next to continue.
The last few steps in the wizard can be set to your own preference although it is recommended to leave them as the default settings. Access will transfer your database to the SQL Server and finally give a status report after the operation. Upsizing has been completed.
More details can be found in our knowledge base.
|
