Select the product you need help with

How To Use CDao for Connections to a Secure Attached Table

Article ID: 186383 - View products that this article applies to.

This article was previously published under Q186383

SUMMARY

This article describes the technique required for the MFC CDao classes to access Microsoft SQL Server data that is password protected through an attached table in Microsoft Access without getting the ODBC password dialog box. The article describes how to programmatically specify the ODBC connection string to the attached table. You must create a CDaoTableDef, set the ODBC connect string, and refresh the TableDef.

The code changed modifications that you need are:

   void CASAuthView::OnInitialUpdate()
   {
      static CDaoDatabase mdb;

      m_pSet = &GetDocument()->m_aSAuthSet;  // Original Code
      mdb.Open(_T("C:\\Temp\\daoSqlTest.mdb"));
      m_pSet->m_pDatabase = &mdb;
      static CDaoTableDef td(&mdb);
      td.Open("[dbo_authors]");
      td.SetConnect("ODBC;database=pubs;
                    uid=billy;pwd=Reuben;dsn=_pubsSecure");
      td.RefreshLink();
      CDaoRecordView::OnInitialUpdate();  // Original Code
   }

MORE INFORMATION

To reproduce the problem and create a solution, do the following:

From SQL Server Enterprise Manager select "manage logins," and add user "billy" with password "Reuben". Select Databases/Pubs/Groups-users/Public, right-click to select Add user. Add billy (with login Billy), refresh the public icon, right-click billy, and then select permissions. Select the authors table, select each field (you will get a green check mark indicating that you have been granted permission), and then click Set.

Create a new DSN to the pubs database. From the ODBC administrator, select Add. Select the SQL Server driver. Call the new DSN _pubsSecure. On the question "How should SQL Server verify the authenticity of the login ID?," change the button to "With SQL Server authentication using a login ID and password entered by the user." Change the Login ID to billy and specify Reubin for the password. Leave the rest of the defaults.

Create an Access database. Select the Table tab, click New, and then click link table. In the "Files of type" list box, select "ODBC Databases()". Select the Machine Data Source tab, and then select "_pubsSecure". You will be prompted for the password. Select the "dbo.authors" table. Save the database as C:\Temp\daoSqlTest.mdb. Close Access, restart access, and then open daoSqlTest.mdb. You will be prompted for a password. This verifies that your security is set up correctly.

Create a MFC/CDao AppWizard Database project. Call it ASAuth (for Access SQL Authorize), select daoSqlTest.mdb, and then select the authors table. Build and run the application. You will get the ODBC "SQL Server Login" dialog box, which requests the password. Enter the password and the program will open the Recordset. This verifies your previous steps.

The current OnInitialUpdate code is as follows:

   CASAuthView::OnInitialUpdate()
      {
         m_pSet = &GetDocument()->m_aSAuthSet;
         CDaoRecordView::OnInitialUpdate();
      }

Change it to:

   void CASAuthView::OnInitialUpdate()
   {
      static CDaoDatabase mdb;

      m_pSet = &GetDocument()->m_aSAuthSet;        // Original Code.
      mdb.Open(_T("C:\\Temp\\daoSqlTest.mdb"));
      m_pSet->m_pDatabase = &mdb;
      static CDaoTableDef td(&mdb);
      td.Open("[dbo_authors]");

      td.SetConnect
         ("ODBC;database=pubs;uid=billy;pwd=Reuben;dsn=_pubsSecure");
      td.RefreshLink();
      CDaoRecordView::OnInitialUpdate();           // Original Code.
   }

Your application will now open the table without prompting you for the password.

Properties

Article ID: 186383 - Last Review: July 13, 2004 - Revision: 2.1

APPLIES TO

Microsoft ODBC Driver for Microsoft SQL Server 2.65
The DAO SDK

kbdatabase kbhowto KB186383

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.