How To Use ADOX from C++ to Copy an Old Jet Database File to a Newer Jet 4.0 Format Database

This article was previously published under Q321328
This article has been archived. It is offered "as is" and will no longer be updated.
This article demonstrates how to use ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) with Visual C++ to create a new Jet database using data from an older formatted Jet database.This article also demonstrates the following techniques that are used in ADOX with Visual C++:
  • How to use ADOX with Visual C++ to create a Jet Database
  • How to obtain the list of tables in a database
  • How to obtain the metadata of a table, including columns and types
  • How to use the Append method of ADOX to create tables and columns
  • How to use Jet OLE DB provider-specific properties to create a linked table
  • How to use ADO in combination with ADOX to copy data from one database to another
  • How to delete a table from a Jet Database
Testing the CompactDatabase method of the Jet Replication Object (JRO) reveals that the method does not efectively convert a table to Jet 4.0 format if the source table is in Access 2.0 or in Access 95 format. If a table that is generated by Access 2.0 or by Access 95 is converted to Jet 4.0 format using the CompactDatabase method of the JRO object, Access 2000 or Access 2002 cannot successfully open the database. This issue is still under investigation at Microsoft. Note that JRO objects were meant to work only with Jet 4.0 and Jet 3.5x format databases.

To convert your old Jet database (earlier than Jet 3.5 format), use ADOX to create a new 4.0 format database and copy all of the tables. The following code demonstrates how to do this. This is a simple sample and does not address several special scenarios such as primary keys, indexes, properties, and relations. The sample only copies tables and does not copy other objects from the source database. For additional information about those techniques, see the ADOX documentation on the MSDN Library Web site.

Save the following code in a C++ project, and then build the project:

NOTE: You do not have to change anything in the code except for the paths on the #import and connection strings.
#import "c:\Program Files\Common Files\system\ado\msado15.dll" #import "c:\Program Files\Common Files\system\ado\msadox.dll"#include <iostream>using namespace std;void main(){	   CoInitialize(NULL);	   //ADO Connection Object pointers for source and destination.   ADODB::_ConnectionPtr SourceCon = NULL;   ADODB::_ConnectionPtr DestCon = NULL;   //ADOX Catalog object pointers for source and destination.   ADOX::_CatalogPtr DestDB = NULL;   ADOX::_CatalogPtr SourceDB = NULL;	   //Connection string.    _bstr_t SrcConnectionString(L""), DestConnectionString(L""), SourceDBLocation(L"");   ADOX::_TablePtr linkedTbl = NULL;   ADOX::_ColumnPtr f = NULL;   ADOX::_TablePtr DestTable = NULL;   try{         SourceDBLocation = (L"C:\\JET30FormatDB.mdb");         //Per Q299484, you must use ODBC Provider to avoid alphabetical ordering.         SrcConnectionString = "Driver={Microsoft Access Driver (*.mdb)};"			"DBQ=" + SourceDBLocation;          DestConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"			  "Data Source=C:\\JET40FormatDB.mdb;"	;         SourceCon.CreateInstance(__uuidof(ADODB::Connection));         SourceCon->Open(SrcConnectionString,"","",-1);         SourceDB.CreateInstance(__uuidof (ADOX::Catalog));         //ADOX catalog must have an active ADO connection          SourceDB->PutActiveConnection(_variant_t((IDispatch *) SourceCon));		         DestDB.CreateInstance(__uuidof (ADOX::Catalog));		         //This line creates a Jet database, and throws an exception if it already exists.         DestDB->Create(DestConnectionString);         DestCon.CreateInstance(__uuidof(ADODB::Connection));         DestCon->Open(DestConnectionString,"","",-1);         DestDB->PutActiveConnection(_variant_t((IDispatch *) DestCon));         //Get the count of tables in Source Database.         long x = SourceDB->Tables->Count;         for (int i = 0; i < x ; i++)         {            // Go through each table that is listed as TABLE and not SYSTEM TABLE or ACCESS SYSTEM TABLE.            if (SourceDB->Tables->Item[(long)i]->Type == _bstr_t(L"TABLE"))            {               cout<<"Now copying "<< (LPCSTR)SourceDB->Tables->Item[(long)i]->Name<<endl;               _bstr_t tblName = SourceDB->Tables->Item[(long)i]->Name;               //Start creating a table.               DestTable.CreateInstance(__uuidof(ADOX::Table));               DestTable->Name = tblName;               //Get number of columns in Source Table.                int cnt = SourceDB->Tables->Item[tblName]->Columns->Count;				               //Use ADOX Append method to append all columns to the destination table.                for ( int col = 0; col < cnt; col++)               {                  f = SourceDB->Tables->Item[tblName]->Columns->Item[(long)col];                 DestTable->Columns->Append(f->Name,f->Type,f->DefinedSize);               }					               //Finally, append destination table.               DestDB->Tables->Append( DestTable.GetInterfacePtr());               //This step is to get the data from source table.                //Create a temporary linked table in Destination Database               //and run "Insert Into mynewTable Select * from LinkedTable".               linkedTbl.CreateInstance(__uuidof(ADOX::Table));               linkedTbl->ParentCatalog = DestDB;               linkedTbl->Name = "TempLink2";               linkedTbl->Properties->Item["Jet OLEDB:Create Link"]->Value = (long)true;               linkedTbl->Properties->Item["Jet OLEDB:Link Datasource"]->Value = SourceDBLocation;                     linkedTbl->Properties->Item["Jet OLEDB:Remote Table Name"]->Value = tblName;				DestDB->Tables->Append(linkedTbl.GetInterfacePtr());               DestCon->Execute("Insert into " + tblName + " Select * From TempLink2", NULL, -1);               //You do not have to keep this linked table around, so you can delete it.                DestDB->Tables->Delete("TempLink2");               DestTable.Release();               linkedTbl.Release();            }            else            {               cout <<"Skipping "<< (LPCSTR)SourceDB->Tables->Item[(long)i]->Name <<endl;            }			        } // End of for loop.				        DestCon->Close();        SourceCon->Close();       }       catch(_com_error &e)       {         _bstr_t bstrSource(e.Source());         _bstr_t bstrDescription(e.Description());         cout<< "Source: "<< (LPCSTR)bstrSource <<"\nDescription : " << (LPCSTR)bstrDescription <<endl;       }}				
For more information, visit the MSDN Library at the following Microsoft Web site:In the Contents tree in the left pane of the MSDN Library, browse to the following Help topic:
  \Data Access    \Microsoft Data Access Components      \ADO        \SDK Documentation          \Microsoft ActiveX Data Objects (ADO)            \ADO Programmer's Guide				
For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:
225048 INFO: Issues Migrating from DAO/Jet to ADO/Jet
304322 ACC2002: How to use ADOX to Import Relationships
230588 How To Link and Refresh Linked Jet Tables Using ADOX
279245 FIX: Default Column Properties Cleared When You Set or Check Values by Using ADOX to an Access Database
304323 ACC2002: How to Use ADOX to Create an SQL Pass-Through Query
275252 ACC2000: How to Create a Table with Jet Data Types via ADOX
252908 How To Create a Table with Primary Key Through ADOX
230501 How To Compact Microsoft Access Database via ADO
ADOX Jet Convert converted create HOWTO JET40 4.0 Properties VC++ VC C++

Article ID: 321328 - Last Review: 02/27/2014 21:12:52 - Revision: 1.4

Microsoft Visual Studio 6.0 Enterprise Edition, Microsoft ActiveX Data Objects 2.7

  • kbnosurvey kbarchive kbfile kbhowto KB321328