2024年1月10日发(作者：)

一、声明ODBC API函数

这些函数都在中定义了，要在程序中调用这些函数，必须首先对它们进行声明。建立一个模块，在里面写入下列内容：

'== 声明中定义的ODBC API函数 ==

Public Declare Function SQLAllocEnv Lib "" (phenv&)As Integer

Public Declare Function SQLAllocConnect Lib "" (ByValHenv&, phdbcd&)

As Integer

Public Declare Function SQLAllocStmt Lib "" (ByValHdbc&, phstmt&) As

Integer

Public Declare Function SQLConnect Lib "" (ByValHdbc&, ByVal

szDSN$, ByVal cbDSN%, ByVal szUID$, ByVal cbUID%,ByVal szPWD$, ByVal

cbPWD%) As Integer

Public Declare Function SQLColAttributes Lib "" (ByValHstmt&, ByVal

icol%, ByVal fDescType%, ByVal rgbDesc As String,ByVal cbDescMax%,

pcbDesc%, pfDesc&) As Integer

Public Declare Function SQLDisconnect Lib "" (ByValHdbc&) As Integer

Public Declare Function SQLExecDirect Lib "" (ByValHstmt&, ByVal

szSqlStr$, ByVal cbSqStr&) As Integer

Public Declare Function SQLFetch Lib "" (ByValHstmt&) As Integer

Public Declare Function SQLFreeConnect Lib "" (ByValHdbc&) As

Integer

Public Declare Function SQLFreeEnv Lib "" (ByValHenv&) As Integer

Public Declare Function SQLFreeStmt Lib "" (ByValHstmt&, ByVal

fOption%) As Integer

Public Declare Function SQLGetData Lib "" (ByValHstmt&, ByVal icol%,

ByVal fCType%, ByVal rgbValue As String,ByVal cbValueMax&, pcbValue%) As

Integer

Public Declare Function SQLNumResultCols Lib "" (ByValHstmt&,

pccol%) As Integer

Public Declare Function SQLGetDiagRec Lib "" (ByValHandleType%,

ByVal Handle&, ByVal RecNumber%, Sqlstate%,NativeErrorPtr%, MessageText

As String, ByVal Bufferlenchgth%,TextlenchgthPtr%)

数据库访问常量和数据源信息常量：

'数据库访问常量

Public Const SQL_C_CHAR As Long = 1

Public Const SQL_COLUMN_LABEL As Long = 18

Public Const SQL_DROP As Long = 1

Public Const SQL_DRROR As Long = -1

Public Const SQL_NO_DATA_FOUND As Long = 100

Public Const SQL_SUCCESS As Long = 0

Public Const SQL_SUCCESS_WITH_INFO As Long = 1

Public Const SQL_ATTR_MAX_LENGTH As Long = 100

'数据源信息常量

Public Const DATABASE As String = "newdb"

Public Const DSN As String = "newdb"

Public Const DB_USER_NAME As String = "sa"

Public Const DB_PASSWORD As String = "sa"

'版本号

Public Const VerNum As String = "版本 1.0.01"

'生成日期

Public Const GenDate As String = "日期 2002-08-26"

'一次执行connect操作可以访问数据库的次数

Public Const CONNECT_LOOP_MAX = 10

二、初始化ODBC

'== 标记数据库是否连接 ==

Private IsConnect As Boolean

'== 标记执行Connect()函数后，访问数据库的次数 ==

Private Connect_Num As Integer

Private Henv AsLong '环境句柄

Private Hdbc AsLong '连接句柄

Private Rc As Long

Public Hstmt As Long

三、与ODBC数据库建立连接

'连接数据库

Private Sub Connect()

Dim TmpStat As Long 'SQLConnect()函数的返回值

'如果连接标记为真，则返回。否则会出错

If IsConnect = True Then

ExitSub

End If

'分配环境句柄，保存在变量Henv中

If SQLAllocEnv(Henv) Then

MsgBox"无法初始化ODBC环境!", , "ODBC API执行错误"

End

End If

'根据环境句柄，分配连接句柄，保存在变量Hdbc中

If SQLAllocConnect(Henv, Hdbc) Then

MsgBox"无法连接ODBC!", , "ODBC API执行错误"

End

End If

'根据连接句柄、数据源、用户名和密码连接指定的数据库

TmpStat = SQLConnect(Hdbc, DSN, Len(DSN),DB_USER_NAME, _

Lench(DB_USER_NAME), DB_PASSWORD, Len(DB_PASSWORD))

'如果连接不成功则退出程序

If TmpStat <> SQL_SUCCESS And _

TmpStat <> SQL_SUCCESS_WITH_INFO Then

MsgBox"无法获得连接句柄!", , "ODBC API执行错误"

IsConnect =True

Disconnect

End

End If

'设置连接标记

IsConnect = True

End Sub

'使用Connect_Num控制数据库连接

Public Sub DB_Connect()

Connect_Num = Connect_Num + 1

Connect

End Sub

四、存储数据

'执行ODBC数据库操作语句

Public Sub OdbcExt(ByVal TmpSQLstmt As String)

'根据连接句柄，分配语句句柄

If SQLAllocStmt(Hdbc, Hstmt) Then

MsgBox"句柄分配失败", , "ODBC API执行错误"

DBapi_Disconnect

End

End If

'执行SQL语句，Lench是用户自定义函数，计算包含汉字的字符串长度

If SQLExecDirect(Hstmt, TmpSQLstmt,Lench(TmpSQLstmt)) Then

MsgBox"数据库访问语句执行失败", , "ODBC API执行错误"

MsgBoxTmpSQLstmt

DBapi_Disconnect

End

End If

End Sub

'使用Data控件连接数据库，将执行指定的SQL语句

Public Sub DBdataExt(TmpData As Data, ByVal TmpSource AsString)

'关闭已有的ODBC连接

DBapi_Disconnect

'Data控件连接数据库

t = "ODBC;DATABASE=" + DATABASE_

+ ";UID=" + DB_USER_NAME + ";PWD=" _

+ DB_PASSWORD + ";DSN=" + DB_NAME

Source = TmpSource

h

End Sub

五、结束应用程序

'断开与数据库的连接

Private Sub Disconnect()

Dim Rc As Long

'如果连接标记为假，标明已经断开连接，则直接返回

If IsConnect = False Then

ExitSub

End If

'断开连接

Rc = SQLDisconnect(Hdbc)

'释放连接句柄

Rc = SQLFreeConnect(Hdbc)

'释放环境句柄

Rc = SQLFreeEnv(Henv)

IsConnect = False

End Sub

Public Sub DB_Disconnect()

If Connect_Num >= CONNECT_LOOP_MAX Then

Connect_Num= 0

Disconnect

End If

End Sub

'强制关闭api方式访问的数据库，计数器复位

Public Sub DBapi_Disconnect()

Connect_Num = 0

Disconnect

End Sub