用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());
}
}