VC ++ operations on the Access Database (query, insert, update, delete, etc)

MicrosoftOfficeAccess is a relational database management system released by Microsoft. Access databases are often used in small software systems, such as production management, sales management, inventory management and other types of enterprise management software. Their biggest advantages are: easy to learn and flexible to use. The following example is used to describe in detail in VCMFC.

Microsoft Office Access is a relational database management system released by Microsoft. Access databases are often used in small software systems, such as production management, sales management, inventory management and other types of enterprise management software. Their biggest advantages are: easy to learn and flexible to use. The following example is used to describe in detail.

Microsoft Office Access is released by Microsoft.DatabaseManagement System. AccessDatabaseIt is often used in small software systems, such as production management, sales management, inventory management and other types of enterprise management software. Its biggest advantage is that it is easy to learn and flexible to use.

The following describes in detail how to use AccessDatabaseHow to store data in filesDatabaseTheQuery,Insert,UpdateAndDeleteAnd so onOperation.

(The instance can be downloaded from my CSDN resources: http://download.csdn.net/detail/margin1988/8235865)

First, how to create an AccessDatabaseAnd inDatabaseCreate a data table?

Step 1: Open the Microsoft Office Access software and click "blank"Database";

Step 2: Set the pre-creation BlankDatabaseFile name and type (File Name: point32.mdb, file type: Microsoft Office Access 2000Database(*. Mdb ));

Step 3: "CREATE" BlankDatabase;

Step 4:Database"SettingsDatabasePassword "(in this example, the password is set to 1234 );

Step 5:DatabaseCreate a table, such as TestTab (number, name, gender, age );

Step 6: Save and close the table after it is created.DatabaseAnd thenDatabaseFile (point32.mdb) is cut to your VC ++ program debug or release directory, the preparation is complete.

Next, writeDatabaseTestTab table for DataQuery,Insert,Update,DeleteAnd so onOperationMethod:

(1) Access using ado only after importDatabaseRequired DLL

# Import "C: \ Program Files \ Common Files \ system \ ado \ msado15.dll" no_namespace rename ("EOF", "adoEOF") // ado ACCESSDatabaseRequired

(2) initialize OLE in the entry function of the program to support the application

AfxOleInit();

(3) obtain the path of the application (EXE)

CString path; // path of the application: char filepath [256]; char * pPath; GetModuleFileName (AfxGetInstanceHandle (), filepath, 256); pPath = strrchr (filepath, '\'); * pPath = 0; path = filepath;

(4) Create DatabaseAccess connection string

Char * PtConnectStr ;//DatabaseConnection string CString connstr = "Provider = Microsoft. jet. OLEDB.4.0; Data Source = "; connstr + = path; connstr + =" \ point32.mdb "; connstr + ="; Jet OLEDB: Database Password = '000000 '"; ptConnectStr = connstr. getBuffer (0 );

(5) QueryImplementation of the data method in the TestTab table

//QueryThe table data is displayed in the List Control void CPoint32Dlg: ReadUserInfo () {// selectm_list.DeleteAllItems (); // clear the List _ ConnectionPtr m_pConnection; _ RecordsetPtr m_pRecordset; try {m_pConnection.CreateInstance (_ uuidof (Connection); m_pConnection-> Open (PtConnectStr, "", ", adModeUnknown);} catch (_ com_error e) {CString errormessage; errormessage. format ("DatabaseConnection Failed. \ r error message: % s ", e. errorMessage (); // AfxMessageBox (errormessage); MessageBox (errormessage, "connection failed", MB_ICONEXCLAMATION); if (m_pConnection-> State) m_pConnection-> Close (); return;} try {// obtain data and place it in the dataset CString cmd; cmd. format ("SELECT * FROM TestTab"); m_pRecordset.CreateInstance ("ADODB. recordset "); m_pRecordset-> Open (cmd. getBuffer (), _ variant_t (IDispatch *) m_pConnection, true), adOpenStatic, adLockOptimistic, ad1_text ); // Process the data and display _ variant_t varbuffer; long index = 0; // Note: The value must be of the long TYPE int countItem = 0; CString str; while (! M_pRecordset-> adoEOF) {index = 0; // read ID: varbuffer = m_pRecordset-> GetCollect (_ variant_t (index); if (varbuffer. vt! = VT_NULL) {str. format ("% d", varbuffer. lVal); m_list.InsertItem (countItem, str. getBuffer ();} // read other information while (index <3) {index ++; varbuffer = m_pRecordset-> GetCollect (_ variant_t (index )); if (varbuffer. vt! = VT_NULL) {str = (LPCTSTR) (_ bstr_t) varbuffer; m_list.SetItemText (countItem, index, str. getBuffer () ;}m_precordset-> MoveNext (); countItem ++ ;}} catch (_ com_error & e) {// AfxMessageBox (e. description (); MessageBox (e. description (),"DatabaseOperationFailed. ", MB_ICONEXCLAMATION); if (m_pRecordset-> State) m_pRecordset-> Close (); if (m_pConnection-> State) m_pConnection-> Close (); return ;} if (m_pRecordset-> State) m_pRecordset-> Close (); if (m_pConnection-> State) m_pConnection-> Close ();}

(6) to the TestTab table InsertData method implementation

// To the tableInsertData, andUpdateVoid CPoint32Dlg: OnBnClickedButton1 () {// insert_ConnectionPtr m_pConnection; _ variant_t success; try {m_pConnection.CreateInstance (_ uuidof (Connection )); m_pConnection-> Open (PtConnectStr, "", "", adModeUnknown);} catch (_ com_error e) {CString errormessage; errormessage. format ("DatabaseConnection Failed. \ r error message: % s ", e. errorMessage (); MessageBox (errormessage, "failed to add", MB_ICONEXCLAMATION); return ;}try {CString strCmd = "insert into TestTab (UName, UGender, UAge) VALUES ('tester', 'male', '30') "; for (int I = 0; I <5; I ++) {m_pConnection-> Execute (strCmd. allocSysString (), & RecordsAffected, ad1_text) ;}} catch (_ com_error & e) {// AfxMessageBox (e. description (); MessageBox (e. description (), "failed to add", MB_ICONEXCLAMATION); if (m_pConnecti On-> State) m_pConnection-> Close (); return;} if (m_pConnection-> State) m_pConnection-> Close (); // MessageBox ("added successfully! "," Message "); m_update.EnableWindow (TRUE); m_delete.EnableWindow (TRUE); ReadUserInfo ();}

(7) UpdateImplementation of the data method in the TestTab table

//UpdateTable data, andUpdateDisplay void CPoint32Dlg: OnBnClickedButton3 () {// update_ConnectionPtr m_pConnection; _ variant_t RecordsAffected; try {m_pConnection.CreateInstance (_ uuidof (Connection )); m_pConnection-> Open (PtConnectStr, "", "", adModeUnknown);} catch (_ com_error e) {CString errormessage; errormessage. format ("DatabaseConnection Failed. \ r error message: % s ", e. errorMessage (); MessageBox (errormessage, "failed to modify", MB_ICONEXCLAMATION); return ;}try {CString strCmd = "UPDATE TestTab SET [UGender] = 'femal ', [UAge] = '20' WHERE [UName] = 'tester' "; m_pConnection-> Execute (strCmd. allocSysString (), & RecordsAffected, ad1_text);} catch (_ com_error & e) {// AfxMessageBox (e. description (); MessageBox (e. description (), "failed to modify", MB_ICONEXCLAMATION); if (m_pConnection-> State) m_pConnect Ion-> Close (); return;} if (m_pConnection-> State) m_pConnection-> Close (); // MessageBox ("modified successfully! "," Message "); ReadUserInfo ();}

(8) DeleteHow to reset data in the TestTab table and automatically numbered primary key (key) in the table

//DeleteData in the table, reset the automatic number (starting from 1), andUpdateThe List Control displays void CPoint32Dlg: accept () {// delete_ConnectionPtr m_pConnection; _ variant_t accept; try {m_pConnection.CreateInstance (_ uuidof (Connection); m_pConnection-> Open (PtConnectStr, "", "", adModeUnknown);} catch (_ com_error e) {CString errormessage; errormessage. format ("connectionDatabaseFailed! \ R error message: % s ", e. ErrorMessage (); MessageBox (errormessage ,"DeleteFailed ", MB_ICONEXCLAMATION); return;} try {//DeleteAll data in the table is CString strCmd = "delete from TestTab"; m_pConnection-> Execute (strCmd. allocSysString (), & RecordsAffected, ad1_text); // reset the automatic ID of the table to increase it from 1.DeleteAll data in the TABLE) strCmd = "alter table TestTab alter column id counter ()"; m_pConnection-> Execute (strCmd. allocSysString (), & RecordsAffected, ad1_text);} catch (_ com_error & e) {// AfxMessageBox (e. description (); MessageBox (e. description (),"DeleteFailed ", MB_ICONEXCLAMATION); if (m_pConnection-> State) m_pConnection-> Close (); return;} if (m_pConnection-> State) m_pConnection-> Close (); // MessageBox ("DeleteSuccessful! "," Completed "); m_insert.EnableWindow (TRUE); m_update.EnableWindow (FALSE); m_delete.EnableWindow (FALSE); ReadUserInfo ();}