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.

Preparatory reading

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 Collection

Office VBA Reference

Creating and modifying access tables

step

Install MDAC2.5 or later on your 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" based on your computer's installation path, 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;} prepare 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 (_T ("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-> column; pcols-> append (_t ("rordid), 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: Compressed Unicode Strings")) -> Value = true; pcols-> GetItem (_T ("OtherInfo")) -> 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 = "recordid"; // Index Name Pindexnew-> Columns-> Append ("RecordID", Adox :: Adinteger, 0); // Index field PINDEXNEW-> PUTPRIMARYKEY (-1); / / Master 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 (_COM_ERROR & E) {EserrprintProvideRerror (TempConnn); EserrPrintComerror (E); return false;} catch (...) {} return false;} Assist 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);}}

Integrated database access overall in the document / view / frame architecture is still difficult. 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.