如何通过ADO获得Access数据库中的所有用户表和表信息

本文介绍一种通过ADO列举Access文件中所用用户表和表信息的方法，仅供参考，源代码在VC6.0 ＋XP环境通过测试。

 

首先，需要引入msado15.dll文件，在stdafx.h中添加：

#import "c:/program files/common files/system/ado/msado15.dll" no_namespace rename("EOF","adoEOF")

 

其次，将下面的代码复制到cpp文件中：

CString GetType(int nType); void GetTableInfo(_ConnectionPtr& pConnection, CString strTableName); BOOL GetDatabaseInfo(CString strFile); int _tmain(int argc, TCHAR* argv[], TCHAR* envp[]) { int nRetCode = 0; //初始化com环境 CoInitialize(NULL); GetDatabaseInfo("c://test.mdb"); CoUninitialize(); return nRetCode; } void GetTableInfo(_ConnectionPtr& pConnection, CString strTableName) { _variant_t recAffected; try { int nCount = 0; //开始打开表，以便得到表的结构 _RecordsetPtr pRecordset; pRecordset.CreateInstance(__uuidof(Recordset)); pRecordset->Open((LPCTSTR)strTableName, pConnection.GetInterfacePtr() , adOpenDynamic, adLockOptimistic, adCmdTable); FieldsPtr fds = pRecordset->GetFields(); //依次读取字段，并显示 //字段名 for(int i = 0; i < fds->GetCount(); i++) { FieldPtr fd = fds->GetItem(_variant_t(short(i))); if(fd->Value.vt != NULL) { CString strName((LPCTSTR)fd->GetName()); printf("字段%i,名称%s,类型%s,长度%ld /r/n",i+1 , (LPCTSTR)fd->GetName(), GetType(fd->GetType()) , fd->GetDefinedSize()); } } pRecordset->Close(); } catch(_com_error e)///捕捉异常 { CString strStatus; strStatus.Format("错误:%s", (LPCTSTR)e.Description()); } } BOOL GetDatabaseInfo(CString strFile) { //首先进行数据库连接： _ConnectionPtr pConnection; _RecordsetPtr pRecordset; HRESULT hr; try { hr = pConnection.CreateInstance("ADODB.Connection");//创建Connection对象 if(SUCCEEDED(hr)) { CString str; str.Format(_T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s"), strFile);//需要设置文件的路径名 hr = pConnection->Open(_bstr_t(str.GetBuffer(1)), "", "", adModeUnknown);///连接数据库 ///上面一句中连接字串中的Provider是针对ACCESS2000环境的， //对于ACCESS97,需要改为:Provider=Microsoft.Jet.OLEDB.3.51 if(!SUCCEEDED(hr)) { printf("连接数据库失败"); return FALSE; } } } catch(_com_error e)///捕捉异常 { CString errormessage; errormessage.Format("连接数据库失败!/r/n%s", (LPCTSTR)e.Description()); printf(errormessage);///显示错误信息 return FALSE; } pRecordset.CreateInstance(__uuidof(Recordset)); //开始查询所有表名称 pRecordset = pConnection->OpenSchema(adSchemaTables);//枚举表的名称处理 while(!(pRecordset->adoEOF)) { CString strTableType; CString strTableName; _bstr_t table_name = pRecordset->Fields->GetItem("TABLE_NAME")->Value;//获取表的名称 _bstr_t table_type = pRecordset->Fields->GetItem("TABLE_TYPE")->Value;//获取表的类型 strTableType.Format("%s",(LPCSTR) table_type); strTableName.Format("%s",(LPCSTR) table_name); if(!lstrcmp(strTableType,_T("TABLE"))) { //strTableName是表名称 printf("查到一个表：%s/r/n", strTableName); GetTableInfo(pConnection, strTableName); } pRecordset->MoveNext(); } pRecordset->Close(); pConnection->Close(); return TRUE; } CString GetType(int nType) { switch(nType) { case adEmpty: { return _T("Empty"); } case adSmallInt: { return _T("SmallInt"); } case adInteger: { return _T("Integer"); } case adSingle: { return _T("Single"); } case adDouble: { return _T("Double"); } case adCurrency: { return _T("Currency"); } case adTinyInt: { return _T("TinyInt"); } case adBigInt: { return _T("BigInt"); } case adUnsignedTinyInt: { return _T("UnsignedTinyInt"); } case adUnsignedSmallInt: { return _T("UnsignedSmallInt"); } case adUnsignedInt: { return _T("UnsignedInt"); } case adUnsignedBigInt: { return _T("UnsignedBigInt"); } case adDecimal: { return _T("Decimal"); } case adNumeric: { return _T("Numeric"); } case adBoolean: { return _T("Boolean"); } case adError: { return _T("Error"); } case adUserDefined: { return _T("UserDefined"); } case adVariant: { return _T("Variant"); } case adIDispatch: { return _T("IDispatch"); } case adIUnknown: { return _T("IUnknown"); } case adGUID: { return _T("GUID"); } case adDate: { return _T("Date"); } case adDBDate: { return _T("DBDate"); } case adDBTime: { return _T("DBTime"); } case adDBTimeStamp: { return _T("DBTimeStamp"); } case adBSTR: { return _T("BSTR"); } case adChar: { return _T("Char"); } case adVarChar: { return _T("VarChar"); } case adLongVarChar: { return _T("LongVarChar"); } case adWChar: { return _T("WChar"); } case adVarWChar: { return _T("VarWChar"); } case adLongVarWChar: { return _T("LongVarWChar"); } case adBinary: { return _T("Binary"); } case adVarBinary: { return _T("VarBinary"); } case adLongVarBinary: { return _T("LongVarBinary"); } case adChapter: { return _T("Chapter"); } case adFileTime: { return _T("FileTime"); } case adPropVariant: { return _T("PropVariant"); } case adVarNumeric: { return _T("VarNumeric"); } case adArray: { return _T("Array"); } default: { return _T(""); } }

 

编译运行，可以打印Access数据库的信息，一个例子如下：

 

查到一个表：Result 字段1,名称ResultID,类型Integer,长度4 字段2,名称TaskID,类型Integer,长度4 字段3,名称TestTime,类型Integer,长度4 字段4,名称DestIP,类型VarWChar,长度255 字段5,名称TestType,类型Integer,长度4 字段6,名称AvgDelay,类型Integer,长度4 字段7,名称MinDelay,类型Integer,长度4 字段8,名称MaxDelay,类型Integer,长度4 字段9,名称IPAvailability,类型Integer,长度4 字段10,名称IPDV,类型Double,长度8 字段11,名称LossRate,类型Double,长度8 查到一个表：ResultData 字段1,名称ResultDataID,类型Integer,长度4 字段2,名称ResultID,类型Integer,长度4 字段3,名称ElapsedTime,类型Integer,长度4 字段4,名称PacketSize,类型Integer,长度4 字段5,名称ResultType,类型Integer,长度4 字段6,名称Delay,类型Integer,长度4 字段7,名称ErrorInfo,类型VarWChar,长度255 Press any key to continue