以下代码放入模块中,再引用,好爽的啊!!!
Public Function opensql(ByVal SQL As String, msg As String) As ADODB.Recordset
Dim Cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
'发生错误时跳转
On Error GoTo opensql_Error
'将查询语句转化为字符串
sTokens() = Split(SQL)
'连接到指定的数据源
Set Cnn = New ADODB.Connection
Cnn.Open ConnectString
'判断查询语句
If InStr("insert,delete,update", UCase$(sTokens(0))) Then
'执行语句
Cnn.Execute SQL
'返回查询信息
msg = sTokens(0) & " query successful"
Else
Set rst = New ADODB.Recordset
'执行查询语句
rst.Open Trim$(SQL), Cnn, adOpenKeyset, adLockOptimistic
'返回数据集对象
Set opensql = rst
'返回查询记录
msg = "查询到" & rst.RecordCount & "条记录"
End Ifopensql_Exit:
'释放资源
Set rst = Nothing
Set Cnn = Nothing
Exit Functionopensql_Error:
'显示错误信息
msg = "查询错误:" & Err.Description
Resume opensql_Exit
End Function
'指定数据源字符串
Public Function ConnectString() As String
ConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=data\aaa.mdb"
'我这里是引用的Access中的一个表,打开SQL Sever用下面的语句
'ConnectString = "fileDSN=***.dsn;UID=sa;PWD="
End Function
Public Function opensql(ByVal SQL As String, msg As String) As ADODB.Recordset
Dim Cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
'发生错误时跳转
On Error GoTo opensql_Error
'将查询语句转化为字符串
sTokens() = Split(SQL)
'连接到指定的数据源
Set Cnn = New ADODB.Connection
Cnn.Open ConnectString
'判断查询语句
If InStr("insert,delete,update", UCase$(sTokens(0))) Then
'执行语句
Cnn.Execute SQL
'返回查询信息
msg = sTokens(0) & " query successful"
Else
Set rst = New ADODB.Recordset
'执行查询语句
rst.Open Trim$(SQL), Cnn, adOpenKeyset, adLockOptimistic
'返回数据集对象
Set opensql = rst
'返回查询记录
msg = "查询到" & rst.RecordCount & "条记录"
End Ifopensql_Exit:
'释放资源
Set rst = Nothing
Set Cnn = Nothing
Exit Functionopensql_Error:
'显示错误信息
msg = "查询错误:" & Err.Description
Resume opensql_Exit
End Function
'指定数据源字符串
Public Function ConnectString() As String
ConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=data\aaa.mdb"
'我这里是引用的Access中的一个表,打开SQL Sever用下面的语句
'ConnectString = "fileDSN=***.dsn;UID=sa;PWD="
End Function
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货