Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles

MS Access

Posted Jun 7, 2004

MS Access for the Business Environment: Stored Procedures from the MS Access Client - Page 5

By William Pearson

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.

Click for larger image

Illustration 14: Select the Appropriate Authentication Setting

27.  Click Next.

28.  On the next dialog to appear, click the radio button to the left of the top entry, Change the default database to: , 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.

Illustration 15: Dialog with Settings

30.  Click Next.

The next dialog of the series appears. We will leave all setpoints therein at default, as shown in Illustration 16.

Illustration 16: Fourth Dialog, Create a New Data Source to SQL Server Series

31.  Click Finish.

The ODBC Microsoft SQL Server Setup dialog appears, confirming our configuration, as depicted in Illustration 17.

Illustration 17: ODBC Microsoft SQL Server Setup Dialog

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, in short order, indicating we have tested positive for connectivity, as shown in Illustration 18.

Illustration 18: SQL Server ODBC Data Source Test Message Box - Connectivity Confirmed

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.

Illustration 19: Select Data Source Dialog - Machine Data Source Tab, with Our New Source

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.

Illustration 20: Connection String Builder - Save Password Dialog

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.

Since mine is a development environment, I will save the information, but you may do as you see fit for your own environment.

37.  Answer the dialog by selecting Yes or No, as appropriate to your environment.

We are returned to the Query Properties page, where we see the new string specification appear in the ODBC Connect Str box, as shown in Illustration 21.

Illustration 21: Query Properties Page - New Connection Information in Place

Another property that is of significance within our intended use of the Pass-Through query channel is the Returns Records setting. We will leave this setting at its default of Yes (as indicated in Illustration 21) for the Stored Procedure we will execute in our first example. This is because the procedure we will use is designed to return records, in a report-like manner, for viewing at the client level. In cases where the procedure is intended to perform an action other than the simple return of a recordset, we change the Return Records setting to No. If we leave it at the default of Yes in a case where records are not returned, we receive an error message.

38.  Close the Query Properties page.

We are now ready to work with a Stored Procedure.

MS Access Archives

Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM