MS Access for the Business Environment: Extend Access with Pass-Through Queries - Page 4
May 3, 2004
15. Click the Machine Data Source tab.
16. Click New.
The Create New Data Source dialog appears.
17. Select the System Data Source radio button.
The Create New Data Source dialog appears as depicted in Illustration 10.
Click for larger image
18. Click Next.
19. Scrolling as necessary in the next Create New Data Source dialog that appears ("Select a driver ..."), select the SQL Server driver, as shown in Illustration 11.
Click for larger image
20. Click Next.
The third Create New Data Source dialog appears, confirming our selections, as depicted in Illustration 12.
21. Click Next.
The Create a New Data Source to SQL Server dialog appears.
22. Type the following in the Name text box of the dialog:
23. Type the following in the Description text box of the dialog:
Pubs DB Back End
24. In the Server selector box, select the server to which you wish to connect (mine is MOTHER in the illustrations).
The Create a New Data Source to SQL Server dialog appears as shown in Illustration 13.
25. Click Next.
26. Enter the appropriate authentication setting for security by clicking the respective radio button in the next dialog. (Mine is Windows NT authentication, as depicted in Illustration 14.) Leave the other settings at default.
27. Click Next.
28. On the next dialog to appear, click the radio button, named 'Change the default database to:' to the left of the top entry, to activate it.
29. Select pubs in the selector box immediately under Change the default database to: , and leave all other settings at default.
The third Create a New Data Source to SQL Server dialog appears as shown in Illustration 15.
30. Click Next.
The next dialog of the series appears. We will leave all setpoints therein at default, as shown in Illustration 16.
31. Click Finish.
The ODBC Microsoft SQL Server Setup dialog appears, confirming our configuration, as depicted in Illustration 17.
Let's test the connection at this point to ascertain correct setup.
32. Click the Test Data Source button on the ODBC Microsoft SQL Server Setup dialog.
The SQL Server ODBC Data Source Test message box appears momentarily, indicating we have tested positive for connectivity, as shown in Illustration 18.
33. Click OK to close the message box.
34. Click OK to close the ODBC Microsoft SQL Server Setup dialog that reappears.
We are returned to the Select Data Source dialog, Machine Data Source tab, where we can see our new data source, Pubs DB, appear among the selections, as shown in Illustration 19.
35. Ensure that the new data source Pubs DB is selected by clicking / highlighting it.
36. Click OK.
The Connection String Builder - Save Password dialog appears, as shown in Illustration 20.
Here we can select Yes if we wish to save the logon / password information within the connection string we are building (as appropriate).
NOTE: Saving logon information in connection strings carries inherent risk. Security of the data source may be compromised by doing so. For more information, consult the MS Access documentation, MSSQL Server 2000 Books Online, or other resources. Not saving the information will result in our being asked for logon / password information each time we attempt to access the back end via MS Access.