Setting up an ODBC Datasource for use with MS SQL databases Print

  • 0

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 correct "" or 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 requested the SQL Server database from us. Click Client Configuration to bring up the options for connecting to the SQL Server.

Change the networking mode to TCP/IP and enter the correct "" or 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 of mis-configured one of the settings.

Was this answer helpful?

« Back