Database development and training for Birmingham and the midlands
Specialists in Microsoft Access and Visual Foxpro database training and development
I am happy to hear from existing clients but I am taking no new calls.
March 2015
I have now retired and am taking on no new work. I'd like to thank all our customers over the past 20 years. It really has been a most enjoyable time.
ODBC connection to SQL Server
![[ODBC Data Source Administrator]](../images/odbcdatasource.gif "System DSN ODBC Data Sources")
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.
![[ODBC Data Source for SQL Server]](../images/odbcsqlsource.gif "ODBC Data Source for SQL Server")
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 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 .
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 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 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 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.
![[ODBC default database in SQL Server]](../images/odbcsqldefault.gif "Setting the default database in SQL Server")
Click 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 and pick the database that you created earlier.
Click 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 button. If you see something like this final screenshot then you will have successfully created a new data source.
![[ODBC test of connection to SQL Server]](../images/odbcdatasourcetest.gif "ODBC Data Source Test")
Silly mistakes
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.
