How To Use CDao for Connections to a Secure Attached Table

This article was previously published under Q186383
This article has been archived. It is offered "as is" and will no longer be updated.
This article describes the technique required for the MFC CDao classes toaccess Microsoft SQL Server data that is password protected through anattached table in Microsoft Access without getting the ODBC password dialogbox. The article describes how to programmatically specify the ODBCconnection 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   }				
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 thepublic icon, right-click billy, and then select permissions. Select theauthors table, select each field (you will get a green check markindicating that you have been granted permission), and then click Set.

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

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

Create a MFC/CDao AppWizard Database project. Call it ASAuth (for AccessSQL 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 programwill 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 thepassword.
kbDSupport kbdse kbDAO kbDatabase kbAccess

Article ID: 186383 - Last Review: 01/07/2015 06:59:03 - Revision: 2.1

  • Microsoft ODBC Driver for Microsoft SQL Server 2.65
  • The DAO SDK
  • kbnosurvey kbarchive kbdatabase kbhowto KB186383