public conn as recordesource conn.ConnectString="server="";uid=;pwd=;database="
'========================== '==直接使用ODBC API得方法== '=========================='== 声明odbc32.dll中定义的ODBC API函数 == Public Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal Henv&, phdbcd&) As Integer Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal Hdbc&, phstmt&) As Integer Public Declare Function SQLConnect Lib "odbc32.dll" (ByVal Hdbc&, ByVal szDSN$, ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szPWD$, ByVal cbPWD%) As Integer Public Declare Function SQLColAttributes Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, ByVal fDescType%, ByVal rgbDesc As String, ByVal cbDescMax%, pcbDesc%, pfDesc&) As Integer Public Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer Public Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal Hstmt&, ByVal szSqlStr$, ByVal cbSqStr&) As Integer Public Declare Function SQLFetch Lib "odbc32.dll" (ByVal Hstmt&) As Integer Public Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal Henv&) As Integer Public Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal Hstmt&, ByVal fOption%) As Integer Public Declare Function SQLGetData Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, ByVal fCType%, ByVal rgbValue As String, ByVal cbValueMax&, pcbValue%) As Integer Public Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal Hstmt&, pccol%) As Integer Public Declare Function SQLGetDiagRec Lib "odbc32.dll" (ByVal HandleType%, ByVal Handle&, ByVal RecNumber%, Sqlstate%, NativeErrorPtr%, MessageText As String, ByVal Bufferlenchgth%, TextlenchgthPtr%)'数据库访问常量'在SQL语句中使用C语言中的CHAR数据类型 Public Const SQL_C_CHAR As Long = 1 '列标签 Public Const SQL_COLUMN_LABEL As Long = 18 '释放SQL语句句柄 Public Const SQL_DROP As Long = 1 'SQL语句返回错误信息 Public Const SQL_ERROR As Long = -1 '结果集为空 Public Const SQL_NO_DATA_FOUND As Long = 100 'SQL语句执行成功 Public Const SQL_SUCCESS As Long = 0 '执行SQL语句返回一个警告信息 Public Const SQL_SUCCESS_WITH_INFO As Long = 1 '最大连接次数 Public Const CONNECT_LOOP_MAX = 10 '==标记数据库是否连接== Private IsConnect As Boolean '==数据源信息== Private DSN As String Private DB_USER_NAME As String Private DB_PASSWORD As String '==标记执行Connect()函数后,访问数据库的次数== Private Connect_Num As Integer Private Henv As Long '环境句柄 Private Hdbc As Long '连接句柄 Private Rc As Long Public Hstmt As LongPrivate Sub Connect() Dim TmpStat As Long 'SQLConnect()函数的返回值 '如果连接标记为真,则返回 If IsConnect = True Then Exit Sub 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'断开与数据库的连接 Private Sub DisConnect() '如果连接标记为假,表示已经断开连接,则直接返回 If IsConnect = False Then Exit Sub End If '断开连接 Rc = SQLDisconnect(Hdbc) '释放连接句柄 Rc = SQLFreeConnect(Hdbc) '释放环境句柄 Rc = SQLFreeEnv(Henv) IsConnect = False End Sub'使用Connect_Num控制数据库连接 Public Sub DB_Connect() Connect_Num = Connect_Num + 1 Connect End Sub'使用CONNECT_LOOP_MAX控制断开数据库连接 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'执行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执行错误" MsgBox TmpSQLStmt DBAPI_DisConnect End End If End Sub
conn.open "dsn=ODBC数据源名;uid=;sa=;"
conn.ConnectString="server="";uid=;pwd=;database="
'==直接使用ODBC API得方法==
'=========================='== 声明odbc32.dll中定义的ODBC API函数 ==
Public Declare Function SQLAllocEnv Lib "odbc32.dll" (phenv&) As Integer
Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal Henv&, phdbcd&) As Integer
Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal Hdbc&, phstmt&) As Integer
Public Declare Function SQLConnect Lib "odbc32.dll" (ByVal Hdbc&, ByVal szDSN$, ByVal cbDSN%, ByVal szUID$, ByVal cbUID%, ByVal szPWD$, ByVal cbPWD%) As Integer
Public Declare Function SQLColAttributes Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, ByVal fDescType%, ByVal rgbDesc As String, ByVal cbDescMax%, pcbDesc%, pfDesc&) As Integer
Public Declare Function SQLDisconnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer
Public Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal Hstmt&, ByVal szSqlStr$, ByVal cbSqStr&) As Integer
Public Declare Function SQLFetch Lib "odbc32.dll" (ByVal Hstmt&) As Integer
Public Declare Function SQLFreeConnect Lib "odbc32.dll" (ByVal Hdbc&) As Integer
Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal Henv&) As Integer
Public Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal Hstmt&, ByVal fOption%) As Integer
Public Declare Function SQLGetData Lib "odbc32.dll" (ByVal Hstmt&, ByVal icol%, ByVal fCType%, ByVal rgbValue As String, ByVal cbValueMax&, pcbValue%) As Integer
Public Declare Function SQLNumResultCols Lib "odbc32.dll" (ByVal Hstmt&, pccol%) As Integer
Public Declare Function SQLGetDiagRec Lib "odbc32.dll" (ByVal HandleType%, ByVal Handle&, ByVal RecNumber%, Sqlstate%, NativeErrorPtr%, MessageText As String, ByVal Bufferlenchgth%, TextlenchgthPtr%)'数据库访问常量'在SQL语句中使用C语言中的CHAR数据类型
Public Const SQL_C_CHAR As Long = 1
'列标签
Public Const SQL_COLUMN_LABEL As Long = 18
'释放SQL语句句柄
Public Const SQL_DROP As Long = 1
'SQL语句返回错误信息
Public Const SQL_ERROR As Long = -1
'结果集为空
Public Const SQL_NO_DATA_FOUND As Long = 100
'SQL语句执行成功
Public Const SQL_SUCCESS As Long = 0
'执行SQL语句返回一个警告信息
Public Const SQL_SUCCESS_WITH_INFO As Long = 1
'最大连接次数
Public Const CONNECT_LOOP_MAX = 10
'==标记数据库是否连接==
Private IsConnect As Boolean
'==数据源信息==
Private DSN As String
Private DB_USER_NAME As String
Private DB_PASSWORD As String
'==标记执行Connect()函数后,访问数据库的次数==
Private Connect_Num As Integer
Private Henv As Long '环境句柄
Private Hdbc As Long '连接句柄
Private Rc As Long
Public Hstmt As LongPrivate Sub Connect()
Dim TmpStat As Long 'SQLConnect()函数的返回值
'如果连接标记为真,则返回
If IsConnect = True Then
Exit Sub
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'断开与数据库的连接
Private Sub DisConnect()
'如果连接标记为假,表示已经断开连接,则直接返回
If IsConnect = False Then
Exit Sub
End If
'断开连接
Rc = SQLDisconnect(Hdbc)
'释放连接句柄
Rc = SQLFreeConnect(Hdbc)
'释放环境句柄
Rc = SQLFreeEnv(Henv)
IsConnect = False
End Sub'使用Connect_Num控制数据库连接
Public Sub DB_Connect()
Connect_Num = Connect_Num + 1
Connect
End Sub'使用CONNECT_LOOP_MAX控制断开数据库连接
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'执行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执行错误"
MsgBox TmpSQLStmt
DBAPI_DisConnect
End
End If
End Sub