ODBC connection to SQL Server

The upsizing wizards in FoxPro and Access both need to be able to find an ODBC connection so that they can communicate with the databases managed by SQL Server.

The easiest way to be sure of this is to create an ODBC Data Source manually before you start the wizard. This means that you will be able to test that the connection is communicating properly with the correct database before you give control over to the wizard. The wizard is a useful facility but its weakness is that it will want to take you on a preplanned route through the process. It will not always let you divert from that path if you suddenly need to fix some unexpected problem with the connection. Get the comnnection working and tested before starting the wizard.

Open the Windows Control Panel from the Start Menu and select Data Sources(ODBC) from the list of Administrative Tools. There will be 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 that will be available to all users on this PC.

Click Add... to see the list of ODBC drivers available. These are shown alphabetically and you will probably have to scroll a long way down the list 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 goes give you more control but it involves a lot of immediate work in planning and a lot of long term work 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 dialog appears with 'master' selected as the database. 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 final screenshot then you will 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 at all.

Being blocked by the firewall.

Being too clever with the passwords and locking myself out of the database.