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

SYMPTOMS

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");

RESOLUTION

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_FILE
static char THIS_FILE[] = __FILE__;
#endif

///////////////////////////////////////////////////////////////////////////// 
// The one and only application object

CWinApp 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;
	}
}

REFERENCES

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

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

SYMPTOMS

RESOLUTION

STATUS

MORE INFORMATION

REFERENCES