VC+ADO动态创建Access数据库

ado技术目前已经成为连接数据库的主流技术，下面介绍如何使用ado来动态创建aclearcase/" target="_blank" >ccess数据库。

为了使用ado，必须引入微软的两个动态连接库msadox.dll和msado15.dll：

#pragma warning (disable: 4146)

#import "c:\Program Files\Common Files\system\ado\msadox.dll"

#import "c:\Program Files\Common Files\system\ado\msado15.dll" no_namespace rename("EOF", "EndOfFile")

#pragma warning (default: 4146)

将上述代码加入到stdafx.h文件中，由于ado是com组件，因此使用ado之前还要初始化com环境：

CoInitialize(NULL);

下面是一个在access数据库中创建表的sql语句的例子：

create table test

(

?name???text(20) WITH COMPRESSION not null,

?score???decimal(12,4) not null default 0,

?ID????? smallint not null default 0,

?birthday? date,

?sex????? char(1),

?CONSTRAINT CK_CH_SEX Check(SEX in ('N','V')),

?CONSTRAINT PK_ID Primary Key (ID)

);

使用ADOX::CatalogPtr来创建mdb文件：

HRESULT hr = S_OK;

//Set ActiveConnection of Catalog to this string

CString strcnn(_T("Provider=Microsoft.JET.OLEDB.4.0;Data source = d:\\test.mdb"));

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

??? }

}?

catch(_com_error &e)

{

?? // Notify the user of errors if any.

?? AfxMessageBox(_T(“error“));

}

创建一个连接对象打开刚刚建立的mdb文件：

_ConnectionPtr g_pConn;

g_pConn.CreateInstance(__uuidof(Connection));

g_pConn->Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb;",

?"", "", adConnectUnspecified);

下面函数用来执行sql文件中的sql语句：

// Function name : RunSqlScript

// Description?: 执行SQL脚本, [email protected], 2003-09-15

// Return type?: BOOL 成功返回TRUE

// Argument? : LPCSTR SqlScriptFile SQL脚本文件名

// Argument? : char separator SQL 分割符号, 缺省';'

// Argument? : char Remark??SQL 注释符号, 缺省'-'

BOOL RunSqlScript(LPCSTR SqlScriptFile, char separator = ';', char Remark = '-')

{

BOOL bRet = FALSE;

CFileFind finder;

CString ErrLong;

if (finder.FindFile(SqlScriptFile) == TRUE)

{

?CFile fSql;

?TCHAR *buffer, *pSQL, *p;

?

?fSql.Open(SqlScriptFile,Cfile&::modeRead);

?UINT nFileLength = fSql.GetLength();

?

?buffer = (TCHAR *)malloc((nFileLength + 1) * sizeof(TCHAR));

?_tcsnset(buffer, TCHAR('\0'), nFileLength + 1);

?UINT nBytesRead = fSql.Read(buffer,nFileLength); //把sql文件内容读入内存缓冲区

?fSql.Close();

?p = pSQL = buffer;

?BOOL bRunOK = FALSE;

?BOOL bInNote = FALSE;

?BOOL bSkip = FALSE;

?CString strSQL;

?TCHAR ch;

?ErrLog = _T("");

?while (p < (buffer + nFileLength)){

?????//判断是否是注释行

? if (bInNote)

? {

??if (*p == TCHAR('\x0a'))

?? bInNote = FALSE;

? }

? else

? {

??if ((*p == Remark) && (*(p+1) == Remark))

??{

?? bInNote = TRUE;

?? p++;

??}

??else

??{

??????//判断是否是sql语句结束标志

?? if (*p == separator){

???strSQL = _T("");

???bSkip = FALSE;

???while (pSQL < p)

???{

??? if (bSkip == FALSE){

????if ((*(pSQL) == Remark) &&(*(pSQL+1) == Remark))

????{

???? bSkip = TRUE;

???? pSQL++;

????}

????else

????{

???? ch = *pSQL;

???? strSQL = strSQL+ch;

????}

??? }

??? else{

????if (*pSQL = TCHAR('\x0a')){

???? bSkip = FALSE;

???? ch = TCHAR(' ');

???? strSQL = strSQL + ch;

????}

??? }

???

??? pSQL++;

???}

???pSQL = p + 1;

???ErrLog = ErrLog + _T("-- SQL Running ...\n");

???ErrLog = ErrLog + strSQL;

???

???_variant_t vRecords;

???m_nRecordsAffected = 0;

???try

???{

??? g_pConn->CursorLocation = adUseClient;

??? g_pConn->Execute(_bstr_t((LPCTSTR)strSQL), &vRecords, adExecuteNoRecords);

??? m_nRecordsAffected = vRecords.iVal;

??? bRunOK = TRUE;

???}

???catch(_com_error &e)

???{

??? bRunOK = FALSE;

???}

???

???if (bRunOK)

??? ErrLog = ErrLog + _T("\n-- Successed!\n");

???else

???{

??? ErrLog = ErrLog + _T("\n-- Failed!\n");

??? break;

???}

???

?? }

??}

? }

? p++;

?}

?free(buffer);

?bRet = bRunOK;

}

return bRet;

}

调用RunSqlScript创建access数据库中的表：

RunSqlScript(_T("d:\\test.sql"));

关闭数据库连接：

g_pConn->Close();

::CoUninitialize();

以上代码演示了怎样在vc中利用ado根据sql文件动态创建一个access数据库。