MSDN给出了各种语言访问Access 2010的详细介绍：点击打开链接。下图列出了各种语言访问Access 2010的接口。
我在VS2008下使用MFC下的ODBC相关接口访问Access 2010数据库。
Demo中使用MFC访问数据库accsess2010数据库AccessDemo.accdb下有student表，表中有用户编号、电话号码、姓名、省份四列信息，表中有包含这四列信息的多个记录。
MFC下使用CDatabase、CRecordset、CDBException三个类来访问数据库。其中CDatabase主要用来打开和关闭数据库以及执行SQL指令；CRecordset用来执行查询、删除、添加记录；CDBException用来处理异常信息。
1.使用CDatabase、CRecordset、CDBException需要包含头文件：
#include "afxwin.h"
2.打开数据库
CDatabase db;
BOOL result = TRUE;
LPCTSTR lpszConnect =
_T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ=C:\\AccessDemo.accdb;PWD=asdfg;");
TRY
{
result = db.OpenEx(lpszConnect, CDatabase::openExclusive|CDatabase::noOdbcDialog);
}
catch(CDBException * e){
MessageBox(e->m_strError);
}
3.关闭数据库
TRY
{
db.Close();
}
catch(CDBException * e){
MessageBox(e->m_strError);
}
4.插入一个新的记录，这里使用CDatabase.ExecuteSQL()来执行SQL语句插入记录到表中。
UpdateData(TRUE);
CString insert,temp;
insert=_T("insert into student(用户编号,电话号码,姓名,省份) values(");
temp.Format(_T("%d"),m_idlint);
insert+=temp+_T(",\'")+m_phonenumstr+_T("\',\'")+m_namestr+_T("\',\'")+m_provincestr+_T("\')");
try{
db.ExecuteSQL(insert);
}
catch(CDBException *e){
MessageBox(e->m_strError);
}
5.使用CRecordset查询记录符合条件的记录。
UpdateData(TRUE);
BOOL result = TRUE;
CDBVariant var;
CString value;
CString query =_T("SELECT student.[用户编号], student.[电话号码],student.[姓名], student.[省份] FROM student where student.[电话号码]='");
query+=m_phonenumstr+_T("'");
MessageBox(query);
CRecordset rs(&db);
try{
result = rs.Open(CRecordset::dynaset, query, CRecordset::none);
if(TRUE==result){
CODBCFieldInfo fInfo;
short sFieldCount = rs.GetODBCFieldCount();
int rowCount = 0;
while (!rs.IsEOF())
{
for (short column = 0; column < sFieldCount; column++)
{
rs.GetFieldValue(column, var);
switch (var.m_dwType)
{
case DBVT_STRING:
value.Format(_T("%s"), var.m_pstring->GetBuffer(var.m_pstring->GetLength()));
break;
case DBVT_ASTRING:
value.Format(_T("%s"), var.m_pstringA->GetBuffer(var.m_pstringA->GetLength()));
break;
case DBVT_WSTRING:
value.Format(_T("%s"), var.m_pstringW->GetBuffer(var.m_pstringW->GetLength()));
break;
case DBVT_LONG:
value.Format(_T("%ld"), var.m_lVal);
break;
case DBVT_SHORT:
value.Format(_T("%d"), var.m_iVal);
break;
default:
value = _T("");
}
switch(column){
case 0:
m_idlint=var.m_lVal;
break;
case 1:
m_phonenumstr=*var.m_pstring;
break;
case 2:
m_namestr=*var.m_pstring;
break;
case 3:
m_provincestr=*var.m_pstring;
break;
}
}
rowCount++;
rs.MoveNext();
}
}
}
catch(CDBException * e){
MessageBox(e->m_strError);
}
UpdateData(FALSE);
数据库AccessDemo.accdb中的表student数据如下所示：
Demo主界面如下所示：
打开数据库并查询student表如下所示：
插入一条新的数据如下所示：
插入新的记录后的数据库AccessDemo.accdb中的表student数据如下所示：
主要代码如下所示：
AccessDemoDlg.h：
// AccessDemoDlg.h : header file//#pragma once//数据库操作类的头文件#include <afxdb.h>#include "afxcmn.h"#include "afxwin.h"// CAccessDemoDlg dialogclass CAccessDemoDlg : public CDialog{// Constructionpublic: CAccessDemoDlg(CWnd* pParent = NULL); // standard constructor// Dialog Data enum { IDD = IDD_ACCESSDEMO_DIALOG }; protected: virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support// Implementationprotected: HICON m_hIcon; // Generated message map functions virtual BOOL OnInitDialog(); afx_msg void OnSysCommand(UINT nID, LPARAM lParam); afx_msg void OnPaint(); afx_msg HCURSOR OnQueryDragIcon(); DECLARE_MESSAGE_MAP()public: afx_msg void OnBnClickedOpenButton();private: CDatabase db; CListCtrl m_accesslist; CButton m_openbutton; CButton m_insertbutton; CButton m_querybutton; CEdit m_idedit; CEdit m_phonenumedit; CEdit m_nameedit; CEdit m_provinceedit; CStatic m_idstatic; CStatic m_phonenumstatic; CStatic m_namestatic; CStatic m_provincestatic; long m_idlint; CString m_phonenumstr; CString m_namestr; CString m_provincestr; void InitCtrl(); CRect rect;public: afx_msg void OnDestroy(); afx_msg void OnBnClickedInsertButton(); afx_msg void OnBnClickedQueryButton();};// AccessDemoDlg.cpp : implementation file//#include "stdafx.h"#include "AccessDemo.h"#include "AccessDemoDlg.h"#ifdef _DEBUG#define new DEBUG_NEW#endif// CAboutDlg dialog used for App Aboutclass CAboutDlg : public CDialog{public: CAboutDlg();// Dialog Data enum { IDD = IDD_ABOUTBOX }; protected: virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support// Implementationprotected: DECLARE_MESSAGE_MAP()};CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD){}void CAboutDlg::DoDataExchange(CDataExchange* pDX){ CDialog::DoDataExchange(pDX);}BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)END_MESSAGE_MAP()// CAccessDemoDlg dialogCAccessDemoDlg::CAccessDemoDlg(CWnd* pParent /*=NULL*/) : CDialog(CAccessDemoDlg::IDD, pParent) , m_idlint(0) , m_phonenumstr(_T("")) , m_namestr(_T("")) , m_provincestr(_T("")){ m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);}void CAccessDemoDlg::DoDataExchange(CDataExchange* pDX){ CDialog::DoDataExchange(pDX); DDX_Control(pDX, IDC_ACCESS_LIST, m_accesslist); DDX_Control(pDX, IDC_OPEN_BUTTON, m_openbutton); DDX_Control(pDX, IDC_INSERT_BUTTON, m_insertbutton); DDX_Control(pDX, IDC_QUERY_BUTTON, m_querybutton); DDX_Control(pDX, IDC_ID_EDIT, m_idedit); DDX_Control(pDX, IDC_PHONENUM_EDIT, m_phonenumedit); DDX_Control(pDX, IDC_EDIT3, m_nameedit); DDX_Control(pDX, IDC_PROVINCE_EDIT, m_provinceedit); DDX_Control(pDX, IDC_ID_STATIC, m_idstatic); DDX_Control(pDX, IDC_PHONENUM_STATIC, m_phonenumstatic); DDX_Control(pDX, IDC_NAME_STATIC, m_namestatic); DDX_Control(pDX, IDC_PROVINCE_STATIC, m_provincestatic); DDX_Text(pDX, IDC_ID_EDIT, m_idlint); DDX_Text(pDX, IDC_PHONENUM_EDIT, m_phonenumstr); DDX_Text(pDX, IDC_NAME_EDIT, m_namestr); DDX_Text(pDX, IDC_PROVINCE_EDIT, m_provincestr);}BEGIN_MESSAGE_MAP(CAccessDemoDlg, CDialog) ON_WM_SYSCOMMAND() ON_WM_PAINT() ON_WM_QUERYDRAGICON() //}}AFX_MSG_MAP ON_BN_CLICKED(IDC_OPEN_BUTTON, &CAccessDemoDlg::OnBnClickedOpenButton) ON_WM_DESTROY() ON_BN_CLICKED(IDC_INSERT_BUTTON, &CAccessDemoDlg::OnBnClickedInsertButton) ON_BN_CLICKED(IDC_QUERY_BUTTON, &CAccessDemoDlg::OnBnClickedQueryButton)END_MESSAGE_MAP()// CAccessDemoDlg message handlersBOOL CAccessDemoDlg::OnInitDialog(){ CDialog::OnInitDialog(); // Add "About..." menu item to system menu. // IDM_ABOUTBOX must be in the system command range. ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX); ASSERT(IDM_ABOUTBOX < 0xF000); CMenu* pSysMenu = GetSystemMenu(FALSE); if (pSysMenu != NULL) { CString strAboutMenu; strAboutMenu.LoadString(IDS_ABOUTBOX); if (!strAboutMenu.IsEmpty()) { pSysMenu->AppendMenu(MF_SEPARATOR); pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu); } } // Set the icon for this dialog. The framework does this automatically // when the application's main window is not a dialog SetIcon(m_hIcon, TRUE); // Set big icon SetIcon(m_hIcon, FALSE); // Set small icon // TODO: Add extra initialization here InitCtrl(); return TRUE; // return TRUE unless you set the focus to a control}void CAccessDemoDlg::OnSysCommand(UINT nID, LPARAM lParam){ if ((nID & 0xFFF0) == IDM_ABOUTBOX) { CAboutDlg dlgAbout; dlgAbout.DoModal(); } else { CDialog::OnSysCommand(nID, lParam); }}// If you add a minimize button to your dialog, you will need the code below// to draw the icon. For MFC applications using the document/view model,// this is automatically done for you by the framework.void CAccessDemoDlg::OnPaint(){ if (IsIconic()) { CPaintDC dc(this); // device context for painting SendMessage(WM_ICONERASEBKGND, reinterpret_cast<WPARAM>(dc.GetSafeHdc()), 0); // Center icon in client rectangle int cxIcon = GetSystemMetrics(SM_CXICON); int cyIcon = GetSystemMetrics(SM_CYICON); CRect rect; GetClientRect(&rect); int x = (rect.Width() - cxIcon + 1) / 2; int y = (rect.Height() - cyIcon + 1) / 2; // Draw the icon dc.DrawIcon(x, y, m_hIcon); } else { CDialog::OnPaint(); }}// The system calls this function to obtain the cursor to display while the user drags// the minimized window.HCURSOR CAccessDemoDlg::OnQueryDragIcon(){ return static_cast<HCURSOR>(m_hIcon);}void CAccessDemoDlg::OnBnClickedOpenButton(){ // TODO: Add your control notification handler code here BOOL result = TRUE; CString cmpstr=_T("请打开数据库"),temp; LV_COLUMN lvcolumn; LV_ITEM lvitem; TCHAR szConnect[1000]=_T("Driver={Microsoft Access Driver (*.mdb, *.accdb)};DSN='';DBQ="); TCHAR currentdirectory[MAX_PATH]; ::GetModuleFileNameW(NULL,temp.GetBuffer(MAX_PATH+1),MAX_PATH); temp.ReleaseBuffer(); temp=temp.Left(temp.ReverseFind(_T('\\'))); wcscpy(currentdirectory,temp.GetBuffer(temp.GetLength())); temp.ReleaseBuffer(); wcscat(currentdirectory,_T("\\AccessDemo.accdb;PWD=asdfg;")); wcscat(szConnect,currentdirectory); try{ TCHAR buttonname[255]={_T('0'),}; m_openbutton.GetWindowTextW(buttonname,255); if(cmpstr.Compare(buttonname)==0){ result = db.OpenEx(szConnect,CDatabase::openExclusive|CDatabase::noOdbcDialog);//CDatabase::openReadOnly|CDatabase::noOdbcDialog); if (TRUE == result) { m_openbutton.SetWindowTextW(_T("请关闭数据库")); LPCTSTR query =_T("SELECT student.[用户编号], student.[电话号码],student.[姓名], student.[省份] FROM student ORDER BY student.[用户编号] ASC;"); //DESC;"); CRecordset rs(&db); result = rs.Open(CRecordset::dynaset, query, CRecordset::none); if(TRUE==result){ CODBCFieldInfo fInfo; short sFieldCount = rs.GetODBCFieldCount(); //删除列表控件原来所有行 m_accesslist.DeleteAllItems(); //删除列表控件原来所有列 while(m_accesslist.GetHeaderCtrl()->GetItemCount()>0){ m_accesslist.DeleteColumn(m_accesslist.GetHeaderCtrl()->GetItemCount()-1); } m_accesslist.SetRedraw(TRUE); m_accesslist.Invalidate(); m_accesslist.UpdateWindow(); //获取数据库中表列信息 if (sFieldCount > 0) { for (short column = 0; column < sFieldCount; column++) { CODBCFieldInfo fInfo; rs.GetODBCFieldInfo(column, fInfo); //MessageBox(fInfo.m_strName); lvcolumn.mask=LVCF_FMT|LVCF_TEXT|LVCF_WIDTH; lvcolumn.fmt=LVCFMT_LEFT; lvcolumn.pszText=fInfo.m_strName.GetBuffer(fInfo.m_strName.GetLength()); lvcolumn.iSubItem=column; lvcolumn.cx=rect.Width()/sFieldCount; fInfo.m_strName.ReleaseBuffer(); m_accesslist.InsertColumn(column,&lvcolumn); } } CDBVariant var; CString value; int rowCount = 0; int iactualitem; //获取数据库中表行信息 while (!rs.IsEOF()) { for (short column = 0; column < sFieldCount; column++) { rs.GetFieldValue(column, var); switch (var.m_dwType) { case DBVT_STRING: value.Format(_T("%s"), var.m_pstring->GetBuffer(var.m_pstring->GetLength())); break; case DBVT_ASTRING: value.Format(_T("%s"), var.m_pstringA->GetBuffer(var.m_pstringA->GetLength())); break; case DBVT_WSTRING: value.Format(_T("%s"), var.m_pstringW->GetBuffer(var.m_pstringW->GetLength())); break; case DBVT_LONG: value.Format(_T("%ld"), var.m_lVal); break; case DBVT_SHORT: value.Format(_T("%d"), var.m_iVal); break; default: value = _T(""); } if(column==0){ lvitem.mask=LVIF_TEXT; lvitem.iItem=rowCount; lvitem.iSubItem=column; lvitem.pszText=value.GetBuffer(value.GetLength()); value.ReleaseBuffer(); lvitem.cchTextMax=value.GetLength(); m_accesslist.GetItem(&lvitem); lvitem.iItem=m_accesslist.GetItemCount(); iactualitem=m_accesslist.InsertItem(&lvitem); }else{ lvitem.mask=LVIF_TEXT; lvitem.iItem=rowCount; lvitem.iSubItem=column; lvitem.pszText=value.GetBuffer(value.GetLength()); value.ReleaseBuffer(); lvitem.cchTextMax=value.GetLength(); lvitem.iItem=iactualitem; m_accesslist.SetItem(&lvitem); } } rowCount++; rs.MoveNext(); } } m_insertbutton.EnableWindow(TRUE); m_querybutton.EnableWindow(TRUE); } else{ MessageBox(_T("打开数据库失败")); m_insertbutton.EnableWindow(FALSE); m_querybutton.EnableWindow(FALSE); } } else{ db.Close(); m_openbutton.SetWindowTextW(_T("请打开数据库")); m_insertbutton.EnableWindow(FALSE); m_querybutton.EnableWindow(FALSE); } } catch(CDBException * e){ MessageBox(e->m_strError); }}void CAccessDemoDlg::InitCtrl(){ CFont nFont ,* nOldFont; nFont.CreateFont(20,15,0,0,0,FALSE,FALSE,0,0,0,0,0,0,_TEXT("宋体"));//创建字体 m_accesslist.SetFont(&nFont); m_openbutton.SetFont(&nFont); m_insertbutton.SetFont(&nFont); m_querybutton.SetFont(&nFont); m_idedit.SetFont(&nFont); m_phonenumedit.SetFont(&nFont); m_nameedit.SetFont(&nFont); m_provinceedit.SetFont(&nFont); m_idstatic.SetFont(&nFont); m_phonenumstatic.SetFont(&nFont); m_namestatic.SetFont(&nFont); m_provincestatic.SetFont(&nFont); m_openbutton.SetWindowTextW(_T("请打开数据库")); m_insertbutton.SetWindowTextW(_T("插入记录到数据库")); m_querybutton.SetWindowTextW(_T("根据手机号码查询")); m_idstatic.SetWindowTextW(_T("用户编号")); m_phonenumstatic.SetWindowTextW(_T("电话号码")); m_namestatic.SetWindowTextW(_T("姓名")); m_provincestatic.SetWindowTextW(_T("省份")); m_insertbutton.EnableWindow(FALSE); m_querybutton.EnableWindow(FALSE); DWORD dwstyle=m_accesslist.GetExtendedStyle(); //dwstyle|=(LVS_EX_ONECLICKACTIVATE|LVS_EX_FULLROWSELECT|LVS_EX_GRIDLINES|LVS_EX_TRACKSELECT|LVS_EDITLABELS|LVS_NOLABELWRAP); dwstyle|=LVS_EX_FULLROWSELECT; m_accesslist.SetExtendedStyle(dwstyle); m_accesslist.GetWindowRect(&rect);}void CAccessDemoDlg::OnDestroy(){ CDialog::OnDestroy(); // TODO: Add your message handler code here CString cmpstr=_T("请关闭数据库"); TCHAR buttonname[255]={_T('0'),}; m_openbutton.GetWindowTextW(buttonname,255); if(cmpstr.Compare(buttonname)==0){ db.Close(); }}//插入一个记录void CAccessDemoDlg::OnBnClickedInsertButton(){ // TODO: Add your control notification handler code here UpdateData(TRUE); CString insert,temp; insert=_T("insert into student(用户编号,电话号码,姓名,省份) values("); temp.Format(_T("%d"),m_idlint); insert+=temp+_T(",\'")+m_phonenumstr+_T("\',\'")+m_namestr+_T("\',\'")+m_provincestr+_T("\')"); try{ db.ExecuteSQL(insert); } catch(CDBException *e){ MessageBox(e->m_strError); }}//根据手机号码查询记录void CAccessDemoDlg::OnBnClickedQueryButton(){ // TODO: Add your control notification handler code here UpdateData(TRUE); BOOL result = TRUE; CDBVariant var; CString value; CString query =_T("SELECT student.[用户编号], student.[电话号码],student.[姓名], student.[省份] FROM student where student.[电话号码]='"); query+=m_phonenumstr+_T("'"); MessageBox(query); CRecordset rs(&db); try{ result = rs.Open(CRecordset::dynaset, query, CRecordset::none); if(TRUE==result){ CODBCFieldInfo fInfo; short sFieldCount = rs.GetODBCFieldCount(); int rowCount = 0; while (!rs.IsEOF()) { for (short column = 0; column < sFieldCount; column++) { rs.GetFieldValue(column, var); switch (var.m_dwType) { case DBVT_STRING: value.Format(_T("%s"), var.m_pstring->GetBuffer(var.m_pstring->GetLength())); break; case DBVT_ASTRING: value.Format(_T("%s"), var.m_pstringA->GetBuffer(var.m_pstringA->GetLength())); break; case DBVT_WSTRING: value.Format(_T("%s"), var.m_pstringW->GetBuffer(var.m_pstringW->GetLength())); break; case DBVT_LONG: value.Format(_T("%ld"), var.m_lVal); break; case DBVT_SHORT: value.Format(_T("%d"), var.m_iVal); break; default: value = _T(""); } switch(column){ case 0: m_idlint=var.m_lVal; break; case 1: m_phonenumstr=*var.m_pstring; break; case 2: m_namestr=*var.m_pstring; break; case 3: m_provincestr=*var.m_pstring; break; } } rowCount++; rs.MoveNext(); } } } catch(CDBException * e){ MessageBox(e->m_strError); } UpdateData(FALSE);}联系客服
