用VC++操作ACESS数据库(创建数据库、建立新表、连接、增删查改)

首先在 StdAfx.h 中包含如下头文件 #import "C:\Program Files\Common Files\system\ado\msadox.dll" // 创建数据库必用 #import"C:\Program Files\Common Files\System\ADO\msado15.dll" named_guids rename("EOF","adoEOF"), rename("BOF","adoBOF") msado

首先在StdAfx.h中包含如下头文件
#import "C:\Program Files\Common Files\system\ado\msadox.dll"  // 创建数据库必用
#import"C:\Program Files\Common Files\System\ADO\msado15.dll" named_guids rename("EOF","adoEOF"), rename("BOF","adoBOF")

msadox.dll msado15.dll根据自己电脑上的位置进行加载，备注：两个#import位置不能调换，调换链接出错

1.      创建数据库
// 创建数据库
void CTestDlg::OnCreateAdoMdb() 
{
       HRESULT hr = S_OK;  CString filename="c:\\test.mdb";
       //Set ActiveConnection of Catalog to this string 
       CString strcnn(_T("Provider=Microsoft.JET.OLEDB.4.0;Data source="+filename));
       try
       {
             ADOX::_CatalogPtr m_pCatalog = NULL;
              hr = m_pCatalog.CreateInstance(__uuidof (ADOX::Catalog));
              if(FAILED(hr))
              {
                    _com_issue_error(hr);
              }
              else
              {
                     m_pCatalog->Create(_bstr_t(strcnn));
                     //Create MDB
              }
              AfxMessageBox(_T("ok"));
       }
       catch(_com_error &e)
       {
              // Notify the user of errors if any.
              AfxMessageBox(_T("error"));
       }
}

2、  打开数据库
BOOL CTestApp::InitInstance()
{
       AfxEnableControlContainer();
       CoInitialize(NULL);
         .......
       
       return TRUE;
}

3．  创建新表
void CTestDlg::OnMainCreate() 
{
       try
       {
             _ConnectionPtr pConn;pConn.CreateInstance(__uuidof(Connection));
             _RecordsetPtr  pRs;  pRs.CreateInstance(__uuidof(Recordset));
             _CommandPtr    Cmd;  Cmd.CreateInstance( __uuidof( Command ) );
             try                 
              {     
                     CString dd;  CString file="c:\\NXYH.mdb";
                     //CString dd;  CString file="NXYH.mdb";
                     dd.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s",file);
                    pConn->Open((_bstr_t)dd,"","",adModeUnknown); // 打开本地Access库Demo.mdb
              }
             catch(_com_error e)
              {
                    AfxMessageBox("数据库连接失败，确认数据库NXYH.mdb是否在当前路径下!");
              } 
              // 如果本表不存在时,可以创建本表,存在时无法创建.
              try
              {
                    _variant_t RecordsAffected;  CString command1,command2,myfilename="yours";
                     command1.Format("CREATE TABLE %s(ID INTEGER,username TEXT(5),old INTEGER,birthday

DATETIME)",myfilename);
                    pConn->Execute(_bstr_t(command1),&RecordsAffected,adCmdText);
                     command2.Format("INSERT INTO %s(ID,username,old,birthday) VALUES

(1,'washton',26,'1970/1/1')",myfilename);
                     pConn->Execute(_bstr_t(command2),&RecordsAffected,adCmdText);
                    AfxMessageBox("created.");
              }
             catch(...)
              {
                   AfxMessageBox("table  have already created.");
             }
       }
       catch(...)
       {
     }
}

4．  删除记录

void CTestDlg::OnMainDel() 
{
      try
       {
             _ConnectionPtr pConn;
             _RecordsetPtr  pRs;  
             pConn.CreateInstance(__uuidof(Connection));
             pRs.CreateInstance(__uuidof(Recordset));
             try                 
              {     
                     CString dd;  CString file="c:\\NXYH.mdb";
                     dd.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s",file);
                    pConn->Open((_bstr_t)dd,"","",adModeUnknown); // 打开本地Access库Demo.mdb
              }
             catch(_com_error e)
              {
                    AfxMessageBox("数据库连接失败，确认数据库NXYH.mdb是否在当前路径下!");
              } 
             try
              {
                    pRs->Open("SELECT * FROM coordinate", // 查询DemoTable表中所有字段
                           pConn.GetInterfacePtr(),      // 获取库接库的IDispatch指针
                           adOpenDynamic,
                           adLockOptimistic,
                           adCmdText );
              }
             catch(_com_error *e)
              {
                    AfxMessageBox(e->ErrorMessage());
              }
              _variant_t var;
             while (!pRs->adoEOF)   
              {    
                    pRs->MoveFirst();
                     pRs->Delete(adAffectCurrent); //删除当前记录
                     pRs->MoveNext();   
              }     
             MessageBox("delete--over");  pRs->Update(); 
             pRs->Close(); pConn->Close(); pRs=NULL; pConn=NULL;
       }   
      catch (_com_error &e )  
    {    
             printf("Error:\n");   
             printf("Code = %08lx\n", e.Error());    
             printf("Meaning = %s\n", e.ErrorMessage());  
             printf("Source = %s\n", (LPCSTR) e.Source());    
      }
}
 
5．  增加记录

void CTestDlg::OnMainAdd() 
{
      try
       {
             _ConnectionPtr pConn;
             _RecordsetPtr  pRs;  
             pConn.CreateInstance(__uuidof(Connection));
             pRs.CreateInstance(__uuidof(Recordset));
             try                 
              {     
                    // 打开本地Access库Demo.mdb
                    pConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NXYH.mdb","","",adModeUnknown);
              }
             catch(_com_error e)
              {
                    AfxMessageBox("数据库连接失败，确认数据库NXYH.mdb是否在当前路径下!");
              } 
             try
              {
                    pRs->Open("SELECT * FROM coordinate", // 查询DemoTable表中所有字段
                           pConn.GetInterfacePtr(),      // 获取库接库的IDispatch指针
                           adOpenDynamic,
                           adLockOptimistic,
                           adCmdText );
              }
             catch(_com_error *e)
              {
                    AfxMessageBox(e->ErrorMessage());
              }
               // 增加新元素
              XX=123.345; YY=222.434; HH=1445;
              for(int i=0;i              {
                     m_Name.Format("D%d",i+1);
                    pRs->AddNew();
                    //pRs->PutCollect("ID",_variant_t((long)(i+10)));
                    pRs->PutCollect("Name", _variant_t(m_Name));
                    pRs->PutCollect("X",_variant_t((double)(XX)));
                    pRs->PutCollect("Y",_variant_t((double)(YY)));
                    pRs->PutCollect("H",_variant_t((double)(HH)));
              }
             pRs->Update();  MessageBox("add--over");
             pRs->Close(); pConn->Close(); pRs=NULL; pConn=NULL;
       }   
      catch (_com_error &e )  
    {    
             printf("Error:\n");   
             printf("Code = %08lx\n", e.Error());    
             printf("Meaning = %s\n", e.ErrorMessage());  
             printf("Source = %s\n", (LPCSTR) e.Source());    
       }
}

6．  修改记录
void CTestDlg::OnMainChange() 
{
      try
       {
             _ConnectionPtr pConn;
             _RecordsetPtr  pRs;  
             pConn.CreateInstance(__uuidof(Connection));
             pRs.CreateInstance(__uuidof(Recordset));
             try                 
              {     
                    // 打开本地Access库Demo.mdb
                    pConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=NXYH.mdb","","",adModeUnknown);
              }
             catch(_com_error e)
              {
                    AfxMessageBox("数据库连接失败，确认数据库NXYH.mdb是否在当前路径下!");
              } 
             try
              {
                    pRs->Open("SELECT * FROM coordinate", // 查询DemoTable表中所有字段
                           pConn.GetInterfacePtr(),      // 获取库接库的IDispatch指针
                           adOpenDynamic,
                           adLockOptimistic,
                           adCmdText );
              }
             catch(_com_error *e)
              {
                    AfxMessageBox(e->ErrorMessage());
              }
             // 修改数据
             _variant_t var;
             while (!pRs->adoEOF)   
             {    
                    //var = pRs->GetCollect("Name");
                     //if(var.vt != VT_NULL) m_Name = (LPCSTR)_bstr_t(var);
                    //MessageBox(m_Name); //m_Name=m_Name.Left(2)+"*";
                    //m_Name+="main";
                     var = pRs->GetCollect("X"); 
                     if(var.vt != VT_NULL) m_X = (LPCSTR)_bstr_t(var);
                    double XX=100.789; //XX+=atof(m_X);
                    pRs->PutCollect("X",_variant_t((double)(XX)));
                   //pRs->PutCollect("Name", _variant_t(m_Name));
                    pRs->MoveNext();   
              }     
             MessageBox("change--over");
              pRs->Update(); 
             pRs->Close(); pConn->Close(); pRs=NULL; pConn=NULL;
       }   
      catch (_com_error &e )  
    {    
             printf("Error:\n");   
             printf("Code = %08lx\n", e.Error());    
             printf("Meaning = %s\n", e.ErrorMessage());  
             printf("Source = %s\n", (LPCSTR) e.Source());    
      }
}