VC 操作ACCESS的创建数据库,表,添加修改删除记录等

 

VC 操作ACCESS的创建数据库,表,添加\修改\删除记录等

#import "C:\Program Files\Common Files\System\ado\msadox.dll"
#import "C:\Program Files\Common Files\System\ado\msado15.dll" no_namespace\
 rename("EOF","adoEOF")rename("BOF","adoBOF") //导入ADO动态链接库

class ADO 
{
public:
    _ConnectionPtr m_pConnection;     //连接对象指针
 _RecordsetPtr  m_pRecordset;     //记录集对象指针

public:
 ADO();
 virtual ~ADO();
 BOOL CreateDB(LPCTSTR lpszFileName);
 void OnInitADOConn();       //连接数据库
 _RecordsetPtr&  OpenRecordset(CString sql);  //打开记录集
 void CloseRecordset();       //关闭记录集
 void CloseConn();        //关闭数据库连接
 UINT GetRecordCount(_RecordsetPtr pRecordset); //获得记录数 
};

ADO::ADO()
{
 CoInitialize(NULL);
}

ADO::~ADO()
{
 CoUninitialize();
}
BOOL ADO::CreateDB(LPCTSTR lpszFile)
//{
// BOOL CPassportDoc::CreateDB(LPCTSTR lpszFile)
{
 
 _ConnectionPtr tempConnn;
 ADOX::_CatalogPtr pCatalog = NULL;
 ADOX::_TablePtr  pTable = NULL;
 ADOX::_IndexPtr pIndexNew  = NULL;
 ADOX::_IndexPtr pIndex  = NULL;
 _variant_t d;
 HRESULT hr;
 CString strConnect;
 CString strDBPath=lpszFile;
 strConnect.Format(_T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s"),strDBPath);
 COleVariant Connect(strConnect);
 try{
  
  hr = pCatalog.CreateInstance(__uuidof(ADOX::Catalog));
   if (FAILED(hr))
  {
   _com_issue_error(hr);
  // return FALSE;
  }
  if (GetFileAttributes(lpszFile) == INVALID_FILE_ATTRIBUTES)  
  {
   
   hr = pCatalog->Create((LPCTSTR)strConnect);//创建数据库
  }
  tempConnn.CreateInstance(_T("ADODB.Connection"));
  tempConnn->PutCommandTimeout(30);
  tempConnn->PutConnectionTimeout(30);
  tempConnn->put_CursorLocation(adUseClient);
  tempConnn->Open(_bstr_t(strConnect),_bstr_t(),_bstr_t(),adConnectUnspecified);
  pCatalog->PutActiveConnection(_variant_t((IDispatch *) tempConnn));
  pTable.CreateInstance(_T("ADOX.Table"));
  pTable->ParentCatalog =pCatalog;
  pTable->Name="Passport1";
  ADOX::ColumnsPtr pCols =pTable->Columns;
  pCols->Append(_T("RecordID")  ,ADOX::adInteger,0);//自动编号字段
  pCols->Append(_T("Name")   ,ADOX::adWChar,255);//文本字段
  pCols->Append(_T("DateOfBirth")  ,ADOX::adDate,0);//日期字段
  pCols->Append(_T("OtherInfo"),ADOX::adLongVarWChar,0);//备注字段
  pCatalog->Tables->Refresh();
  long lCount=pCols->Count;
  for(long i=0;i<lCount;i++)
  {
   pCols->GetItem(i)->ParentCatalog =pCatalog;//重要！设置Catalog,参见Q201826 PRB: Error 3265 When You Access Properties Collection
   ADOX::PropertiesPtr pProperties=pCols->GetItem(i)->Properties;
   if(pProperties)
   {//这里是用于调试的属性显示代码
    long lp=pProperties->Count;
    TRACE("Properties for Col %s\r\n",(LPCTSTR)pCols->GetItem(i)->Name); 
   }
  }
  //设置说明
  pCols->GetItem(_T("RecordID"))->Properties->GetItem(_T("Description"))->Value=_T("记录编号");//注释
  pCols->GetItem(_T("RecordID"))->Properties->GetItem(_T("AutoIncrement"))->Value=true;//自动编号
  pCols->GetItem(_T("Name"))->Properties->GetItem(_T("Jet OLEDB:Compressed UniCode Strings"))->Value=true;
  pCols->GetItem(_T("Name"))->Properties->GetItem(_T("Description"))->Value=_T("姓名");
  pCols->GetItem(_T("DateOfBirth"))->Properties->GetItem(_T("Description"))->Value=_T("出生日期");
  pCols->GetItem(_T("OtherInfo"))->Properties->GetItem(_T("Jet OLEDB:Compressed UniCode Strings"))->Value=true;
  pCols->GetItem(_T("OtherInfo"))->Properties->GetItem(_T("Description"))->Value=_T("其他信息");
  //--
  pCatalog->Tables->Append(_variant_t ((IDispatch*)pTable));//添加表
  pCatalog->Tables->Refresh();//刷新
  pIndexNew.CreateInstance(_T("ADOX.Index"));
  pIndexNew->Name = "RecordID";//索引名称
  pIndexNew->Columns->Append("RecordID",ADOX::adInteger,0);//索引字段
  pIndexNew->PutPrimaryKey(-1);//主索引
  pIndexNew->PutUnique(-1);//唯一索引
  pTable->Indexes->Append(_variant_t ((IDispatch*)pIndexNew));//创建索引
  pIndexNew=NULL;
  pCatalog->Tables->Refresh();//刷新
  tempConnn->Close();
  return TRUE;
 }
 catch(_com_error &e)
 {
  AfxMessageBox(e.Description()); //弹出错误处理
  return FALSE;
 }
 catch(...)
 {
 }
 return FALSE;
}

 
// return TRUE;
//}

void ADO::OnInitADOConn()
{
 ::CoInitialize(NULL);
 try
 {
       m_pConnection.CreateInstance("ADODB.Connection");  //创建连接对象实例
    _bstr_t strConnect="DRIVER={Microsoft Access Driver (*.mdb)};\
   uid=;pwd=;DBQ=DataBase.mdb;";
    m_pConnection->Open(strConnect,"","",adModeUnknown); //打开数据库
 }
 catch(_com_error e)
 {
  AfxMessageBox(e.Description()); //弹出错误处理
 }
}

_RecordsetPtr&  ADO::OpenRecordset(CString sql)
{
 ASSERT(!sql.IsEmpty());          //SQL语句不能为空
 try
 {
  m_pRecordset.CreateInstance(__uuidof(Recordset)); //创建记录集对象实例
  m_pRecordset->Open(_bstr_t(sql), m_pConnection.GetInterfacePtr(),
   adOpenDynamic, adLockOptimistic, adCmdText);  //执行SQL得到记录集
 }
 catch(_com_error e)           //捕获可能的异常
 {
  AfxMessageBox(e.Description());
 }
 return m_pRecordset;
}

void ADO::CloseRecordset()
{
 if(m_pRecordset->GetState() == adStateOpen) //判断当前的记录集状态
  m_pRecordset->Close();     //关闭记录集
}

void ADO::CloseConn()
{
 m_pConnection->Close();         //关闭数据库连接
 ::CoUninitialize();          //释放COM环境
}

UINT ADO::GetRecordCount(_RecordsetPtr pRecordset)
{
 int nCount = 0;           //声明保存记录数的变量
 try{
  pRecordset->MoveFirst();        //将记录集指针移动到第一条记录
 }
 catch(...)            //捕捉可能出现的错误
 {
  return 0;           //产生错误时返回0
 }
 if(pRecordset->adoEOF)         //判断记录集中是否没有记录
  return 0;           //无记录时返回0
 while (!pRecordset->adoEOF)        //当记录集指针没有指向最后时
 {
  pRecordset->MoveNext();        //将记录集指针移动到下一条记录
  nCount = nCount + 1;        //记录个数的变量加1
 }
 pRecordset->MoveFirst();         //将记录集指针移动到第一条记录
 return nCount;           //返回记录数
}

 

//用例
void CUseAdoDlg::AddToGrid()
{
 ADO m_Ado;
 m_Ado.OnInitADOConn();//连接数据库
 CString SQL = "select * from employees order by 编号 desc"; //设置查询字符串
 m_Ado.m_pRecordset = m_Ado.OpenRecordset(SQL);//打开记录集
 while(!m_Ado.m_pRecordset->adoEOF)
 {
  m_Grid.InsertItem(0,"");
  m_Grid.SetItemText(0,0,(char*)(_bstr_t)m_Ado.m_pRecordset->GetCollect("编号"));
  m_Grid.SetItemText(0,1,(char*)(_bstr_t)m_Ado.m_pRecordset->GetCollect("姓名"));
  m_Grid.SetItemText(0,2,(char*)(_bstr_t)m_Ado.m_pRecordset->GetCollect("学历"));
  m_Ado.m_pRecordset->MoveNext();//将记录集指针移动到下一条记录
 }
 m_Ado.CloseRecordset();
 m_Ado.CloseConn();//断开数据库连接
}

void CUseAdoDlg::OnButadd()
{
 // TODO: Add your control notification handler code here
 UpdateData(TRUE);
 if(m_ID.IsEmpty() || m_Name.IsEmpty() || m_Culture.IsEmpty())
 {
  MessageBox("基础信息不能为空！");
  return;
 }
 ADO m_Ado;
 m_Ado.OnInitADOConn();
 CString sql = "select * from employees";
 m_Ado.m_pRecordset = m_Ado.OpenRecordset(sql);
 try
 {
  m_Ado.m_pRecordset->AddNew(); //添加新行
  m_Ado.m_pRecordset->PutCollect("编号",(_bstr_t)m_ID);
  m_Ado.m_pRecordset->PutCollect("姓名",(_bstr_t)m_Name);
  m_Ado.m_pRecordset->PutCollect("学历",(_bstr_t)m_Culture);
  m_Ado.m_pRecordset->Update(); //更新数据表记录
  m_Ado.CloseRecordset();
  m_Ado.CloseConn();
 }
 catch(...)
 {
  MessageBox("操作失败");
  return;
 }
 MessageBox("添加成功");
 m_Grid.DeleteAllItems(); //删除列表控件
 AddToGrid(); 
}
void CUseAdoDlg::OnButmod()
{
 // TODO: Add your control notification handler code here
 UpdateData(TRUE);
 if(m_ID.IsEmpty() || m_Name.IsEmpty() || m_Culture.IsEmpty())
 {
  MessageBox("基础信息不能为空！");
  return;
 }
 int pos   = m_Grid.GetSelectionMark();
 ADO m_Ado;
 m_Ado.OnInitADOConn();
 CString sql = "select * from employees";
 m_Ado.m_pRecordset = m_Ado.OpenRecordset(sql);
 try
 {
  m_Ado.m_pRecordset->Move((long)pos,vtMissing);
  m_Ado.m_pRecordset->PutCollect("编号",(_bstr_t)m_ID);
  m_Ado.m_pRecordset->PutCollect("姓名",(_bstr_t)m_Name);
  m_Ado.m_pRecordset->PutCollect("学历",(_bstr_t)m_Culture);
  m_Ado.m_pRecordset->Update();
  m_Ado.CloseRecordset();
  m_Ado.CloseConn();
 }
 catch(...)
 {
  MessageBox("操作失败");
  return;
 }
 MessageBox("修改成功");
 m_Grid.DeleteAllItems();
 AddToGrid();
}

void CUseAdoDlg::OnButdel()
{
 // TODO: Add your control notification handler code here
 int pos   = m_Grid.GetSelectionMark();
 ADO m_Ado;
 m_Ado.OnInitADOConn();
 CString sql = "select * from employees";
 m_Ado.m_pRecordset = m_Ado.OpenRecordset(sql);
 try
 {
  m_Ado.m_pRecordset->Move(pos,vtMissing);
  m_Ado.m_pRecordset->Delete(adAffectCurrent);
  m_Ado.m_pRecordset->Update();
  m_Ado.CloseRecordset();
  m_Ado.CloseConn();
 }
 catch(...)
 {
  MessageBox("操作失败");
  return;
 }
 MessageBox("删除成功");
 OnButclear();
 m_Grid.DeleteAllItems();
 AddToGrid();
}