Create and open Access Database Combined with ADO, Adox and MFC documentviewframe architecture

This article describes how to use ADO and ADOX in the document / view / frame architecture of the MFC to create and open the database.

Before reading this article, it is recommended to have a basic understanding of the COM, Database and MFC's document / view / framework. Recommended to read the following articles

MFC technical articles

TN025: Document, View, And Frame Creation Microsoft Knowledge Base Article

Q183606 ActiveX Data Objects (ADO) Frequently Asked Questions Q169496 INFO: Using ActiveX Data Objects (ADO) via #import in VC Q317881 HOW TO: Create an Access Database Using ADOX and Visual C # .NET Q252908 HOWTO: Create a Table with Primary Key Through Adox Q201826 PRB: Error 3265 WHEN YOU Access Properties CollectionOffice VBA Reference

The Creating and Modifying Access Tables steps are installed on the computer to open the VC. First, we use the MFC Application Wizard to create a standard MDI program, here I named this project as Passport, then import Adox # INCLUDE #import "in stdafx.h, C: / Program Files / Common Files / System / ADO / MSADO15.DLL "RENAME (" EOF "," ADOEOF ") Rename (" DattyPeenum "," AdoDataTyPeenum ") #import" c: / program files / common files / system / ado / msadox.dll " Rename ("EOF", "ADOXEOF") Rename ("DattyPeenum", "ADOXDATYPEENUM") #import "c: / program files / common files / system / ado / msjro.dll"

According to the installation path of ADO on your computer, the path here may vary. Declaring database connection adodb :: _ connectionptr m_pconn; and record set adodb :: _ recordsetptr m_pset;, the DelteContents (), ONNewDocument (), and onpendocument () functions are used to disconnect the database, create Databases and tables, and open an existing database.

(The author's complaint: 9CBS article center should change, code typography is so troublesome) Void cpassportdoc :: deleteContents () {try {if (m_pset) {esrecordsetclose (m_pset);} if (m_pconn) if (m_pconn) if (m_pconn-> state & ado): : adStateOpen) m_pConn-> Close (); m_pConn = NULL;} catch (_com_error & e) {ESErrPrintProviderError (m_pConn); ESErrPrintComError (e);} CDocument :: DeleteContents ();} BOOL CPassportDoc :: OnNewDocument () {if ( ! Cdocument :: OnNewDocument ()) Return False; cfiledialog dlgfile (false, _t (". Mdb"), null, OFN_HIDEREADONLY | OFN_PATHMUSTEXIST, _T ("Access Database (* .mdb) | * .mdb | All files (*. *) | *. * || "); if (dlgfile.domodal ()! = Idok) Return False; cstring strdbpath = DLGFILE.GETPATHNAME (); if (! Createdb (strdbpath)) Return False; // Create CSTRING strConnect; strConnect.Format (_T ( "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =% s"), strDBPath); COleVariant Connect (strConnect); // TODO: add reinitialization code here // (SDI documents will Reuse this document) Try {m_pconn.createInstance (_T ("adodb.connection")); m_pset.createInstance (_t ("adodb.recordset"); m _pConn-> PutCommandTimeout (30); m_pConn-> PutConnectionTimeout (30); m_pConn-> put_CursorLocation (ADODB :: adUseClient); m_pConn-> Open (_bstr_t (strConnect), _ bstr_t (), _ bstr_t (), ADODB :: adConnectUnspecified) ;: Esrecordsetopen (_T ("Passport"), m_pconn, m_pset; setpathname (strdbpath); return true;} catch (_COM_ERROR & E) {eserrprintProvideRerror (m_pconn); EserrPrintComerror (E);} catch (...) { } m_pConn = NULL; return FALSE;} BOOL CPassportDoc :: OnOpenDocument (LPCTSTR lpszPathName) {if (! CDocument :: OnOpenDocument (lpszPathName)) return FALSE; ADODB :: _ ConnectionPtr tempConnn; CString strConnect; CString strDBPath =

lpszPathName; strConnect.Format (_T ( "Provider = Microsoft.Jet.OLEDB.4.0; Data Source =% s"), strDBPath); COleVariant Connect (strConnect); // TODO: add reinitialization code here // (SDI documents will reuse this document) try {tempConnn.CreateInstance (_T ( "ADODB.Connection")); tempConnn-> PutCommandTimeout (30); tempConnn-> PutConnectionTimeout (30); tempConnn-> put_CursorLocation (ADODB :: adUseClient); tempConnn-> Open (_bstr_t (strConnect), _ bstr_t (), _ bstr_t (), ADODB :: adConnectUnspecified); SetPathName (strDBPath); m_pConn = tempConnn; m_pSet = NULL; m_pSet.CreateInstance (_T ( "ADODB.Recordset")); :: ESRecordsetOpen (_T ( "Passport"), m_pConn, m_pSet); UpdateAllViews (NULL, UpdateHintRefresh); return TRUE;} catch (_com_error & e) {ESErrPrintProviderError (tempConnn); ESErrPrintComError (e);} catch (...) {} Return false;}

Write a helper function for creating databases, tables and indexes BOOL CPassportDoc :: CreateDB (LPCTSTR lpszFile) {if (:: PathFileExists (lpszFile)) {CString strTemp; strTemp.Format (IDS_TARGET_EXISTS, lpszFile); AfxMessageBox (lpszFile); return FALSE;} ADODB :: _ ConnectionPtr tempConnn; ADOX :: _ CatalogPtr pCatalog = NULL; ADOX :: _ TablePtr pTable = NULL; ADOX :: _ IndexPtr pIndexNew = NULL; ADOX :: _ IndexPtr pIndex = NULL; CString strConnect; CString strDBPath = lpszFile; Strconnect.format ("provider = microsoft.jet.Oledb.4.0; data source =% s"), strdbpath); Colevariant Connect (StrConnect); try {pcatalog.createInstance (_t ("adox.catalog"); pCatalog-> create ((LPCTSTR) strConnect); // create a database tempConnn.CreateInstance (_T ( "ADODB.Connection")); tempConnn-> PutCommandTimeout (30); tempConnn-> PutConnectionTimeout (30); tempConnn-> put_CursorLocation ( ADODB :: adUseClient); tempConnn-> Open (_bstr_t (strConnect), _ bstr_t (), _ bstr_t (), ADODB :: adConnectUnspecified); pCatalog-> PutActiveConnection (_variant_t ((IDispatch *) tempConnn)); pTable.CreateInstance (_T ("Adox.table"))); ptable-> parentcatalog = pcatalog; ptable-> name = "passport"; adox :: columnsptr pcols = ptable-> columns; pcols-> append (_T ("RecordID"), adox: : adINteger, 0); // Auto number field pcols-> append (_t ("name"), adox :: adwchar, 255); // text field Pcols-> append (_t ("DateOfbirth"), adox :: Addate, 0); // Date Field Pcols-> Append ("OtherInfo"), Adox :: Adlongvarwchar, 0); // Remarks Field Pcatalog-> Tables-> refresh (); long lcount = pcols-> count For (long i = 0; i getItem (i) -> ParentCatalog = Pcatalog; // Important!

Set Catalog, see Q201826 PRB: Error 3265 When You Access Properties Collection Adox :: PropertiesPtr Pproperties = Pcols-> GetItem (i) -> Properties; if (pproperties) {// This is the property display code LONG LP = Pproperties-> count; trace ("Properties For COL% S / R / N", (LPCTSTR) PCOLS-> GetItem (i) -> Name); for (long (long (long (long j = 0; j GetItem (j) -> name), g_getvalueString (pproperties-> GetItem (j) -> value));}}} Pcols-> GetItem (_T ("RecordID") -> Properties-> GetItem (_t ("description")) -> value = _t ("Record number); // Note Pcols-> GetItem (_t (" recordidID ")) > Properties-> GetItem (_T ("AutoIncrement")) -> value = true; // Auto number Pcols-> GetItem (_t ("name")) -> Properties-> GetItem (_T ("Jet Oledb: compressed unicode Strings ")) -> value = true; pcols-> GetItem (_t (" name ")) -> Properties-> GetItem (_t (" description ")) -> value = _t (" name "); pcols-> GetItem (_T ("DateOfbirth")) -> Properties-> GetItem (_t ("description")) -> value = _t ("Birth Date)); Pcols-> GetItem (_T (" OtherInfo ") -> Properties -> GetItem (_T ("Jet OLEDB: CompRe SSED Unicode strings ") -> value = true; pcols-> getitem (_t (" ffo ")) -> Properties-> GetItem (_T (" description ")) -> value = _t (" other information "); Pcatalog-> Tables-> append (_variant_t (iDispatch *) ptable)); // Add table Pcatalog-> TABLES-> refresh (); // Refresh PINDEXNEW.CREATEINSTANCE (_t ("adox.index"); pindexnew -> name = "recordidID"; // Index Name Pindexnew-> Columns-> Append ("RecordID", Adox :: Adinteger, 0);

// Index field pindexnew-> putprimaryKey (-1); // main index pindexnew-> Putunique (-1); // unique index PTable-> indexes-> append (_variant_t ((idispatch *) pindexnew)); // Create an index pindexnew = null; pcatalog-> Tables-> refresh (); // Refresh return true;} catch (_Error & e) {eserrprintProvideRerror (TempConnn); EserrPrintComerror (E); return false;} catch (...) { } RETURN FALSE;} Auxiliary database function. Since these functions are written for a project before jiangsheng. So naming some strange.

Reference #define _countof MFC part of the code of the class CDaoRecordset (array) (sizeof (array) / sizeof (array [0])) BOOL ESRecordsetOpen (LPCTSTR lpszSQL, ADODB :: _ ConnectionPtr pConnection, ADODB :: _ RecordsetPtr & rst, ADODB :: CursorTypeEnum CursorType // = adOpenDynamic, ADODB :: LockTypeEnum LockType // = ado20 :: adLockOptimistic, long lOptions // = adCmdUnspecified) {_bstr_t bstrQuery; const TCHAR _afxParameters2 [] = _T ( "PARAMETERS"); const TCHAR _afxSelect2 [] = _T ("SELECT"); const tchar _afXTransform2 [] = _t ("transform"); const tchar _afxtable2 [] = _T ("Table"); // Construct the default query stringiff if (_tcsnicmp (lpsnicmp (lpszsql, _afxselect2, _countof) _afxSelect2) -1)! = 0) && (_tcsnicmp (lpszSQL, _afxParameters2, _countof (_afxParameters2) -1)! = 0) && (_tcsnicmp (lpszSQL, _afxTransform2, _countof (_afxTransform2) -1)! = 0) && (_tcsnicmp ! (lpszSQL, _afxTable2, _countof (_afxTable2) -1) = 0)) {CString strTemp; strTemp.Format ( "SELECT * FROM (% s)", lpszSQL); bstrQuery = (LPCTSTR) strTemp;} else bstrQuery = lpszSQL ; If (RST! = Null) {r r T-> CursorLocation = AdoDB :: aduseclient; RST-> Open (BSTRQUERY, _VARIANT_T (PConnection.GetInterfaceptr (), true), cursortype, locktype, loptions;} trace ("Open Recordset:% S / N", LPSZSQL) ; return ESRecordsetIsOpen (rst);} BOOL ESRecordsetIsOpen (const ADODB :: _ RecordsetPtr & rst) {if (! rst = NULL) {return rst-> State & ADODB :: adStateOpen;} return FALSE;} void ESRecordsetClose (ADODB :: _ RecordsetPtr & rst) {IF (RST! = NULL) {if (RST-> State & Adodb :: AdStateOpen) RST-> Close ();}} cstring g_getvaluestring (const _variant_t & val) {cstring strval; _variant_t Vardest (VAL);

IF (! g_varisvalid (val)) {return strval;} if (val.vt == vt_bool) {if (val.boolval == variant_false) {return_t ("no");} else return_t ("Yes") ;} else {} if (varDest.vt = VT_BSTR!) {HRESULT hr = :: VariantChangeType (& varDest, & varDest, VARIANT_NOUSEROVERRIDE | VARIANT_LOCALBOOL, VT_BSTR); if (FAILED (hr)) {return strVal;}} strVal = (LPCTSTR ) _bstr_t (varDest); return strVal;} error handling code void ESErrPrintComError (_com_error & e) {_bstr_t bstrSource (e.Source ()); _bstr_t bstrDescription (e.Description ()); CString strTemp; strTemp.Format (_T ( " 'Error / n / t error code:% 08LX / N / T meanings:% S / N / T from:% S / N / T description:% S / N "), E.ERROR (), E.ErrorMessage ), (LPCSTR) bstrSource, (LPCSTR) bstrDescription); // Print COM errors :: AfxMessageBox (strTemp);. # ifdef _DEBUG AfxDebugBreak (); # endif} void ESErrPrintProviderError (ADODB :: _ ConnectionPtr pConnection) {if (pConnection = = NULL) RETURN; try {// print provides. // Perr is a record Object in The Connection's Error Collection. AdoDB :: Errorptr Perr = NULL; AdoDB :: ErrorSptr Perrors = PConnection-> Errors; if (PERRORS) {IF ((PERRORS-> Count)> 0) {long ncount = perrors-> count; // collection ranges from 0 to ncount -1. For (long i = 0; i GetItem (i); cstring straTemp; startp.format (_T ("/ t error code:% x / t% s"), Perr-> Number, Perr-> description);}}}} catch (_COM_ERROR & E) {ESERRPRINTCOMERROR (E);}} Summary is still difficult to integrate database access in document / view / framework architecture. Microsoft provides a lot of examples of code, most of which is just to remove sample code from other languages ​​to VC. The main job is to understand the MFC's document / view / framework architecture, call these code when appropriate.