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

原文地址:http://blog.csdn.net/stavck/article/details/3932114

 

 

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

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

#import "c:\program files\commonfiles\system\ado\msado15.dll" no_namespacerename("EOF","adoEOF")

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

view plaincopy to clipboardprint?
CString GetType(int nType);   
void GetTableInfo(_ConnectionPtr& pConnection, CStringstrTableName);   
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, CStringstrTableName)   
{   
    _variant_t recAffected;   
  
  
    try  
    {   
        int nCount = 0;   
  
        //开始打开表，以便得到表的结构           
        _RecordsetPtrpRecordset;   
       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))   
        {   
            CStringstr;   
           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   

           // ACCESS2007: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=XXX.accdb;
           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("");   
        }   
    }  
CStringGetType(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;DataSource=%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数据库的信息，一个例子如下：

view plaincopy to clipboardprint?
查到一个表：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 

 

 

原文地址:http://blog.csdn.net/stavck/article/details/3932114