ODBC connection to SQL Server

The upsizing wizards in FoxPro and Access both need a connection to be able to connect to the SQL Server database. The easiest way to do this is to create a Data Source before you start the wizard. This allows you to work slowly and to test that the connection works before giving over control to the wizard. The wizard doesn't always let you do what you want to do.

Open the Windows Control Panel and select Data Sources(ODBC) from Administrative Tools. There are several types of data source visible as tabs across the top of the form. A User DSN will only be visible to a single user so click the System DSN tab to see the data sources available to all users on this PC.

Click Add... to see the list of drivers available. These are shown alphabetically and you will probably have to scroll down to see the SQL entries. Select SQL Native Client from the list and click Finish.

Give your new datasource a name and a description on the next dialog that appears. If necessary, select the instance of SQL Server that you want to use.

Click Next to move on to the next step. This is where you decide on the security mode that you are going to use. The easiest option here is to select 'With Windows NT authentication using the network login ID'. This lets SQL Server use the Windows login ID to control access to the database and gives you a low level of security; anyone with a valid login will be able to use the database. The alternative is to select 'SQL Server authentication'. This does give you more control but involves a lot of work in plannning and maintaining the users' passwords and levels of access.

Click Next again to move on to the next step.

This is where you specify the database that the datasource will be connected to. By default, the dailog appears with 'master' selected. Click 'Change the default database to:' and pick the database that you created earlier.

Click Next again to move on to the next step. If you are working in English and you don't need encrypted data then you can leave everything on this dialog at its default settings.

Finally, click Finish and you will see a summary showing the choices you've made. Click the 'Test Data Source ... ' button. If you see something like this:

then you have successfully created a new data source.

If the test fails then you will have to go back through the process and check that you've entered the correct information. Most of the settings are made as selections from lists so it's unlikely that these will be wrong. The most common mistakes that I make are:

• Forgetting to start the SQL service.
• Being blocked by the firewall.
• Being too clever with passwords and locking myself out.