Microsoft KB Archive/321328

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

Article ID: 321328

Article Last Modified on 8/15/2005

-

APPLIES TO


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

-



This article was previously published under Q321328



SUMMARY
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



MORE INFORMATION
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.


 * 1) import &quot;c:\Program Files\Common Files\system\ado\msado15.dll&quot;
 * 2) import &quot;c:\Program Files\Common Files\system\ado\msadox.dll&quot;

using namespace std;
 * 1) include

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&quot;&quot;), DestConnectionString(L&quot;&quot;), SourceDBLocation(L&quot;&quot;);

ADOX::_TablePtr linkedTbl = NULL; ADOX::_ColumnPtr f = NULL; ADOX::_TablePtr DestTable = NULL;

try{

SourceDBLocation = (L&quot;C:\\JET30FormatDB.mdb&quot;); //Per Q299484, you must use ODBC Provider to avoid alphabetical ordering. SrcConnectionString = &quot;Driver={Microsoft Access Driver (*.mdb)};&quot; &quot;DBQ=&quot; + SourceDBLocation;

DestConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; &quot;Data Source=C:\\JET40FormatDB.mdb;&quot; ;

SourceCon.CreateInstance(__uuidof(ADODB::Connection)); SourceCon->Open(SrcConnectionString,&quot;&quot;,&quot;&quot;,-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,&quot;&quot;,&quot;&quot;,-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&quot;TABLE&quot;)) {              cout<<&quot;Now copying &quot;<< (LPCSTR)SourceDB->Tables->Item[(long)i]->Name<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 &quot;Insert Into mynewTable Select * from LinkedTable&quot;. linkedTbl.CreateInstance(__uuidof(ADOX::Table)); linkedTbl->ParentCatalog = DestDB; linkedTbl->Name = &quot;TempLink2&quot;; linkedTbl->Properties->Item[&quot;Jet OLEDB:Create Link&quot;]->Value = (long)true; linkedTbl->Properties->Item[&quot;Jet OLEDB:Link Datasource&quot;]->Value = SourceDBLocation; linkedTbl->Properties->Item[&quot;Jet OLEDB:Remote Table Name&quot;]->Value = tblName; DestDB->Tables->Append(linkedTbl.GetInterfacePtr);

DestCon->Execute(&quot;Insert into &quot; + tblName + &quot; Select * From TempLink2&quot;, NULL, -1); //You do not have to keep this linked table around, so you can delete it. DestDB->Tables->Delete(&quot;TempLink2&quot;); DestTable.Release; linkedTbl.Release; }           else {              cout <<&quot;Skipping &quot;<< (LPCSTR)SourceDB->Tables->Item[(long)i]->Name <Close; SourceCon->Close; }      catch(_com_error &e) {        _bstr_t bstrSource(e.Source); _bstr_t bstrDescription(e.Description); cout<< &quot;Source: &quot;<< (LPCSTR)bstrSource <<&quot;\nDescription : &quot; << (LPCSTR)bstrDescription <