数据库访问

http://msdn.microsoft.com/en-us/library/cc811599.aspx HomeLibraryLearnDownloadsSupportCommunity Sign in |中国（简体中文） |Preferences MSDN Library Office Development 2007 Microsoft Office System Access 2007 Technical Articles Developing Access 2007 Solutions with Native C or C++Community Content Add code samples and tips to enhance this topic. More...Developing Access 2007 Solutions with Native C or C++ Office 2007Summary: Learn about the architecture of Microsoft Office Access 2007, its new ACE engine and features, and find out what things that you should consider when you choose the most optimal native data access API for your new or legacy database solution. Get started quickly developing C or Microsoft Visual C++ 2008 solutions for Access databases. (11 printed pages) Aleksandar Jakšić, Microsoft Corporation August 2008 Applies to: Microsoft Office Access 2007 Related Technologies: Microsoft Office Access 2007 Runtime, Microsoft Data Access Components (MDAC) 2.8, MDAC 2.8 Service Pack (SP) 1 for Windows XP SP2, MDAC 2.8 SP2 for Windows Server 2003 SP1, Microsoft Windows Data Access Components (Windows DAC) 6.0, Microsoft Visual C++ 2008 Contents Overview What About the Microsoft JET Engine? Code Examples Prerequisites Access 2007 File Formats Overview of Data Access Technologies DAO OLE DB ADO ODBC Performance Results Considerations when Choosing Data Access Technology Deprecated Data Access Methods Conclusion Overview If you are a database developer and you want to know more about the options that are available to you when you develop a native solution for Microsoft Office Access 2007 using C or C++, this article is written for you. The subject of data access technologies is large, and because many APIs, DLLs, and frameworks interact with the new engine in Access 2007, which is known as the Microsoft Access Database Engine (ACE Engine), this guide is directed toward a broad developer audience. This article does not give you detailed descriptions of every data access technology mentioned in this article. Instead, it describes the data access options available to you when you work with Access 2007 solutions and the advantage and disadvantage of each approach. The accompanying code examples are intended to help you get started quickly, even if you have never programmed in C or C++ before. This article answers questions such as the following: What’s the fastest way to programmatically work with the Access databases generally? Is there a way to connect to the ACE engine using the C programming language (ANSI/ISO C)? Can I use my legacy Microsoft Foundation Classes (MFC) Data Access Object (DAO) code to work with Access 2007 databases? What About the Microsoft JET Engine? Access 2007 introduces an architectural change that affects all data access technologies that are available to Access developers. Prior to Access 2007, Access used the Microsoft Joint Engine Technology (JET) engine for its data storage and query processing. Even though JET was generally accepted as part of Access, since Microsoft Windows 2000 it was included as part of the Windows operating system. However, the JET engine is no longer distributed with the latest Microsoft Data Access Components (MDAC). As a result, Access 2007 provides a new engine based on JET, which is known as the Microsoft Access Engine (ACE Engine), instead of using the deprecated Microsoft JET engine. The ACE engine is fully backward-compatible with previous versions of the JET engineso that it reads and writes (.mdb) files from earlier Access versions. Access 2007 also introduces a new default file format (.accdb) that supports many product improvements. Because the Access team now owns its engine, developers can be confident that their Access solutions will continue to work in the future. Because the ACE engine is not part of the Windows operating system, application users must have Access 2007 or download the free Access 2007 Download: Access Runtime. You can also use the 2007 Office System Driver: Data Connectivity Components installed on your computer. Code Examples This article presents five complete programs created by using Microsoft Visual C++ 2008 that are available for download. Source code is compatible with older versions of Visual C++. However, you might have to create additional project files for the Visual C++ version with which you want to compile this code. All programs perform semantically-identical steps by demonstrating syntactical differences in the data access technologies that are used. Moreover, the console output of each program is almost identical. Examples present the basics of data access that include how to connect to a database, and how to work with a recordset on an SQL query. Download sample files: Access 2007 Sample: Developing Access 2007 Solutions with Native C or C++ Here is the list of steps that are performed in each code example: 1.Define the connection string. 2.Connect to the C:/Northwind 2007.accdb database. 3.Verify the connection and display data source name. 4.Build the SQL query. 5.Execute the query and create a recordset. 6.Retrieve and display a schema of the recordset. 7.Retrieve and display the actual data. 8.Display the total number of retrieved rows. 9.Close all handles. 10.Handle the errors and exceptions when it is required. I used similar code to measure an overall speed against these libraries. Additionally, the performance results are published in this article. Security Note: The code examples show a concept and should not be used in applications or Web sites, as they do not show safe coding practices. For example, all of the code examples hard code the default user name (Admin) and a blank password. This is not recommended. Do not use a blank password. Use a strong password. When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access. Prerequisites It is assumed that you are comfortable with native C or C++ programming, the Visual Studio environment, and are familiar with the concept of relational databases and SQL. Access 2007 File Formats This section summarizes the new and legacy file name extensions that are used by Access 2007. Access 2007 introduces a new default file format, .accdb, that supports several improvements in the product that include complex data types such as multivalue fields, the Attachment data type and history tracking in Memo fields. It also offers better integration with Microsoft Office SharePoint Server 2007 and Microsoft Office Outlook 2007, as well as security and encryption improvements. Although Access 2007 continues to provide support for many of the file formats that were used in previous versions (including .mdb databases) for backward-compatibility, the new .accdb file format is set as the future standard. It is recommended that you use the new file formats to take advantage of the many new features and benefits that they provide. Among the other new file formats is .accde, which is the file name extension for Access 2007 files that are compiled into an "execute only" file. This format replaces the .mde file name extension that is used in previous versions of Access. The .accdr file is a file name extension that enables you to open a database in run-time mode and .accdt is the file name extension for Access Database Templates. For more information about the Access file formats, see Developer Considerations for Choosing File Formats in Access 2007. Overview of Data Access Technologies Visual C++ 2008 provides several ways to work with Access databases. The following technologies are traditionally available to the Access developers who are writing their applications in a C or C++ environment: Data Access Object (DAO) OLE DB ActiveX Data Objects (ADO) Open Database Connectivity (ODBC) Although Access engine providers (components such as the ACE version of DAO, the ACE OLE DB provider and the ACE ODBC provider) were originally included with Windows as part of Microsoft Data Access Components (MDAC), they are now distributed with Access (except ADO which is still part of the Microsoft Windows DAC). ODBC, OLE DB, and ADO themselves are all system-level components. The following figure presents the architecture of the ACE engine in the native programming environment. From the data access perspective, you can view Access as the way the ACE engine is exposed graphically to users. Figure 1. Architecture of the ACE engine in the native programming environment. The following table summarizes the native data access methods. Table 1. Native Data Access Methods Provider Data Access Method Header File & Connection Info Supported Language Additional Comments ACE DAO Direct DAO acedao.tlh (generated from acedao.dll); acedao.dll C++ Supports new .accdb format files. This method is recommended for native projects because it exposes many of the new features that are introduced in the new ACE engine. This method is currently available only for the 32-bit version of the Windows operating system. Microsoft Access Engine OLE DB Provider (ACE Provider) ATL OLE DB and ; aceoledb.dll Microsoft.ACE.OLEDB.12.0 C++ Supports new .accdb format files together with limited support for new features such as complex data and security. This method is currently available only for the 32-bit version of the Windows operating system. ADO ADO msado15.tlh (generated from msado15.dll); Installed with MDAC 2.8 or Windows DAC 6.0. C++ Supports new .accdb format files together with limited support for new features such as complex data and security. This method is available for the 64-bit version of the Windows operating system as part of DAC 6.0. It also serves as an alternative to OLE DB provider. Access ODBC Driver Direct ODBC ; aceodbc.dll; Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file C,C++ Supports new .accdb format files. This method is currently available only for the 32-bit version of the Windows operating system. It is recommended only for maintenance of existing applications or when that you need to work with ANSI/ISO C. Access ODBC Driver MFC ODBC ; aceodbc.dll; Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=path to mdb/accdb file C++ Supports new .accdb format files. This data access method is recommended only for maintenance of the existing applications. Forf a list of data access methods that are deprecated with Access 2007 release, see Deprecated Data Access Methods. DAO Previously, Data Access Objects (DAO) was the exclusive data access method for Access developers. There are two ways to connect to an Access 2007 database using the DAO API: MFC DAO. The MFC DAO classes give the user a way to use the legacy Microsoft JET Database Engine. However, they are not supported in Access 2007. DAO 3.6 is the final version of this technology. It is not available on the 64-bit Windows operating system. Visual C++ .NET wizards do not generate code that automatically creates and opens recordsets. Direct DAO. This access method works with Access 2007 databases. It provides the best support for new features that are introduced with Access 2007 because it exposes most of the ACE engine functionality. Direct DAO Example Direct DAO uses acedao.dll. To compile this code, use the #import macro to generate .tlh header by specifying directive similar to the one shown in the following example. VBC#C++F#JScriptCopy#import rename( "EOF", "AdoNSEOF" ) Because we connect directly to the engine, use the database location for the connection information. VBC#C++F#JScriptCopy_bstr_t bstrConnect = "C://Northwind 2007.accdb"; The following example fetches the schema and data. VBC#C++F#JScriptCopy// Create an instance of the engine. DAO::_DBEngine* pEngine = NULL; // The CoCreateInstance helper function provides a convenient shortcut by connecting // to the class object associated with the specified CLSID, creating an // uninitialized instance, and releasing the class object. hr = CoCreateInstance( __uuidof(DAO::DBEngine), NULL, CLSCTX_ALL, IID_IDispatch, (LPVOID*)&pEngine); if (SUCCEEDED(hr) && pEngine) { // COM errors are handled by the C++ try/catch block. try { DAO::DatabasePtr pDbPtr = NULL; pDbPtr = pEngine->OpenDatabase(bstrConnect); if (pDbPtr) { cout< <<": Successfully connected to database. Data source name:/n " < GetName()< OpenRecordset(query, _variant_t(DAO::dbOpenDynaset)); if (pRS && 0 < pRS->RecordCount) { cout< <<": Retrieve schema info for the given result set: "< GetFields(); if (pFields && pFields->Count > 0) { for (short nIndex=0; nIndex < pFields->Count; nIndex++) { cout<<" | "< GetItem(nIndex)->GetName(); } cout< and . The connection information uses the Microsoft.ACE.OLEDB.12.0 data provider implemented in aceoledb.dll. VBC#C++F#JScriptCopyLPCOLESTR lpcOleConnect = L"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C://Northwind 2007.accdb;User Id=admin;Password=;"; The following example fetches the schema and data. VBC#C++F#JScriptCopy// To initialize the connection to a database using an OLE DB provider, // two ATL classes are needed: CDataSource and CSession. CDataSource dbDataSource; CSession dbSession; // Uses ATL's string conversion macros to convert between character encodings. USES_CONVERSION; // Open the connection and initialize the data source specified by the passed // initialization string. hr = dbDataSource.OpenFromInitializationString(lpcOleConnect); if (FAILED(hr)) { cout< <<": Unable to connect to data source "< < * pRS = (CRowset *)&cmd; // Loop through the rows in the result set. while (pRS->MoveNext() == S_OK) { for (int col=1; col <= (int)colCount; col++) { CHAR* szValue = cmd.GetString(col); cout<<" | "< rename( "EOF", "AdoNSEOF" ) For connection information, use the Microsoft.ACE.OLEDB.12.0 data provider implemented in aceoledb.dll. VBC#C++F#JScriptCopy_bstr_t bstrConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C://Northwind 2007.accdb;"; The following example fetches the schema and data. VBC#C++F#JScriptCopy// Shows the Data Access Method used in this sample. const char* DAM = "ADO"; ADODB::_ConnectionPtr pConn("ADODB.Connection"); hr = pConn->Open(bstrConnect, "admin", "", ADODB::adConnectUnspecified); if (SUCCEEDED(hr)) { cout< <<": Successfully connected to database. Data source name:/n " < GetConnectionString()< Open(query, _variant_t((IDispatch *) pConn, true), ADODB::adOpenUnspecified, ADODB::adLockUnspecified, ADODB::adCmdText); if (SUCCEEDED(hr)) { cout< <<": Retrieve schema info for the given result set: "< get_Fields(&pFields); if (SUCCEEDED(hr) && pFields && pFields->GetCount() > 0) { for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++) { cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetName()); } cout< AdoNSEOF) { for (long nIndex=0; nIndex < pFields->GetCount(); nIndex++) { cout<<" | "<<_bstr_t(pFields->GetItem(nIndex)->GetValue()); } cout< MoveNext(); rowCount++; } cout< <<": Total Row Count: "< < Close(); pConn->Close(); cout< <<": Cleanup. Done."< . VBC#C++F#JScriptCopyLPCTSTR lpszConnect = _T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C://Northwind 2007.accdb;"); The following example fetches the schema and data. VBC#C++F#JScriptCopy// Data Access Method used in this sample. const char* DAM = "MFC ODBC"; CDatabase db; TRY { result = db.OpenEx(lpszConnect, CDatabase::openReadOnly | CDatabase::noOdbcDialog); if (FALSE == result) { cout< <<": Unable to connect to data source "< < 0) { for (short nIndex=0; nIndex < sFieldCount; nIndex++) { CODBCFieldInfo fInfo; rs.GetODBCFieldInfo(nIndex, fInfo); cout<<" | "< GetBuffer(var.m_pstring->GetLength())); break; case DBVT_ASTRING: value.Format("%s", var.m_pstringA->GetBuffer(var.m_pstringA->GetLength())); break; case DBVT_WSTRING: value.Format("%s", var.m_pstringW->GetBuffer(var.m_pstringW->GetLength())); break; default: value = ""; } cout<<" | "< Microsoft.JET.OLEDB.4.0 C++ Deprecated. This method does not work with new .accdb format files. You should only use it to maintain existing applications. MFC DAO MFC DAO ; Installed with MDAC; MFC classes that have the prefix CDao... C++ Deprecated. This method does not work with new .accdb format files. You should only use it to maintain existing applications. Access ODBC Driver 4.0 MFC ODBC ; odbcjt32.dll; Driver={Microsoft Access Driver (*.mdb)};DBQ=path to mdb file C/C++ Deprecated. This method does not work with new .accdb format files. You should only use it to maintain existing applications. Conclusion This article describes Access, its ACE engine, and the mechanisms that are used to work with it, regardless of whether you are writing native code. It shows how to develop Access solutions in C/C++, and describes the data stack used by Access and some different components of the engine. By using data access APIs such as DAO, OLE DB, ADO, or ODBC, you can continue to create custom Access solutions for the most complex scenarios. Generally, as the default provider for the ACE engine, the Direct DAO driver provides the most comprehensive native interface to Access databases. It not only integrates well with the ACE Engine, it is also fast, stable and backward-compatible with the earlier file formats. Therefore, DAO is the recommended data access API when you develop your Access solutions. Community ContentAddFAQ© 2010 Microsoft Corporation. All rights reserved.Terms of Use | Trademarks | Privacy Statement| Feedback FeedbackxTell us about your experience... Did the page load quickly? Yes NoDo you like the page design? Yes NoHow useful is this topic? Tell us more Enter description here.