You are currently offline, waiting for your internet to reconnect

BUG: Database Compaction with SQLConfigDataSource Fails when Access Database is Password Protected

This article has been archived. It is offered "as is" and will no longer be updated.
When a Microsoft Access database is password protected, attempts to compact the database using the SQLConfigDataSource ODBC API might fail with the following error message:
"Not a valid password"
The error message occurs even if you specify the password information in the Attributes list as follows:
SQLConfigDataSource(NULL,ODBC_ADD_DSN,(LPSTR)"Microsoft Access Driver (*.mdb)",                    "COMPACT_DB=C:\\MDB\\Sample.mdb C:\\MDB\\CompatedSample.mdb General\0"                    "PWD=WSG\0DataDirectory=C:\\MDB\0MaxScanRows=20\0\0"); 
To work around this problem, use the Microsoft OLE DB Provider for Jet or Data Access Objects (DAO) directly and bypass the Microsoft Access ODBC Driver.

Samples follow that show how to achieve this functionality:

  • Microsoft Visual Basic sample using the Microsoft OLE DB Provider for Jet and Microsoft Jet and Replication Objects (JRO):
    Private Sub Command1_Click()    Dim myJRO As New jro.JetEngine    myJRO.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\mymdb\\sample.mdb;Jet OLEDB:Database Password=YourPassword;", _   "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\mymdb\\compactedSample.mdb;Jet OLEDB:Engine Type=5; Jet OLEDB:Database Password=YourPassword;"End Sub

  • Microsoft Visual C++ sample using Microsoft Foundation Classes (MFC) DAO classes:

    This is a standard Microsoft Visual C++ application that supports MFC.
    #ifdef _DEBUG#define new DEBUG_NEW#undef THIS_FILEstatic char THIS_FILE[] = __FILE__;#endif///////////////////////////////////////////////////////////////////////////// // The one and only application objectCWinApp theApp;using namespace std;int _tmain(int argc, TCHAR* argv[], TCHAR* envp[]){	int nRetCode = 0;	// initialize MFC and print and error on failure	if (!AfxWinInit(::GetModuleHandle(NULL), NULL, ::GetCommandLine(), 0))	{	    // TODO: change error code to suit your needs	    cerr << _T("Fatal Error: MFC initialization failed") << endl;	    nRetCode = 1;	}	else	{	   CDaoDatabase db;	   try{	        db.m_pWorkspace->CompactDatabase(_T("C:\\MyMDB\\Sample.mdb"),                                                  _T("C:\\MyMDB\\CompactedSample.mdb"),dbLangGeneral,dbVersion30,";pwd=YourPassword");	   }	   catch (CDaoException *e)	   {	        AfxMessageBox (e->m_pErrorInfo->m_strDescription);	        e->Delete();   	   }	   return nRetCode;	}}
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.
More information
For additional information about the problem, and for information about a fix, click the article number below to view the article in the Microsoft Knowledge Base:
271177 Unable To Repair Password Protected Database Via ODBC Control Panel
For additional information about compacting Microsoft Access databases, click the article numbers below to view the articles in the Microsoft Knowledge Base:
230496 HOWTO: Compacting Microsoft Access Database Through OLE DB
230501 HOWTO: Compacting Microsoft Access Database via ADO
security passworded

Article ID: 270638 - Last Review: 01/10/2015 12:46:10 - Revision: 4.0

  • kbnosurvey kbarchive kbbug kbjet kbmdacnosweep kbpending KB270638