如果在VB中,可将ACCESS中的查询名直接作为数据表名使用 例一个查询名chaxun SQL = "select * from chaxun"
比如,你ACCESS数据库中有个带参数的查询,名称为Q_USER: SELECT * FROM users WHERE uid =[@1] And pwd =[@2]那么VB中: Dim cn As Connection Dim cmd as Command dim rs as recordset Dim StrConnect As String StrConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & App.Path & "\test.mdb;" Set cn = New ADODB.Connection cn.Open StrConnect Set cmd = New ADODB.Command cmd.ActiveConnection = cn cmd.CommandText = "Q_USER" cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("@1", advarChar, adParamInput, 20, txtUid.Text) cmd.Parameters.Append param Set param = cmd.CreateParameter("@2", advarChar, adParamInput, 20, txtPwd.Text) cmd.Parameters.Append param Set rs = cmd.Execute
'debug.print rs!uid;rs!pwd
Set rs = Nothing Set cmd = Nothing Set cn = Nothing
这是一个登录验证的函数:Public Function Validate(Optional strUserName As String, Optional strPassWord As String) As Boolean Dim sql As String sql = "PARAMETERS username Text ( 32 ), [password] Text ( 32 );" & _ "SELECT users.* From users " & _ "WHERE users.username=@username and users.password=@password;" Dim com As ADODB.Command Dim rs As ADODB.Recordset Set com = New ADODB.Command Set com.ActiveConnection = Cnn com.CommandText = sql com.CommandType = adCmdText com.Parameters.Append com.CreateParameter("@username", adVarChar, adParamInput, 32) com.Parameters.Append com.CreateParameter("@password", adVarChar, adParamInput, 32) com("@username") = strUserName com("@password") = strPassWord Set rs = com.Execute If rs.EOF And rs.BOF Then Validate = False Else Validate = True End If Me.UserName = strUserName Me.PassWord = strPassWord Set com = Nothing Set rs = Nothing End Function
例一个查询名chaxun
SQL = "select * from chaxun"
比如,你ACCESS数据库中有个带参数的查询,名称为Q_USER:
SELECT *
FROM users
WHERE uid =[@1] And pwd =[@2]那么VB中: Dim cn As Connection
Dim cmd as Command
dim rs as recordset
Dim StrConnect As String StrConnect = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & App.Path & "\test.mdb;"
Set cn = New ADODB.Connection
cn.Open StrConnect Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "Q_USER"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("@1", advarChar, adParamInput, 20, txtUid.Text)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("@2", advarChar, adParamInput, 20, txtPwd.Text)
cmd.Parameters.Append param
Set rs = cmd.Execute
'debug.print rs!uid;rs!pwd
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing
Dim sql As String
sql = "PARAMETERS username Text ( 32 ), [password] Text ( 32 );" & _
"SELECT users.* From users " & _
"WHERE users.username=@username and users.password=@password;"
Dim com As ADODB.Command
Dim rs As ADODB.Recordset
Set com = New ADODB.Command
Set com.ActiveConnection = Cnn
com.CommandText = sql
com.CommandType = adCmdText
com.Parameters.Append com.CreateParameter("@username", adVarChar, adParamInput, 32)
com.Parameters.Append com.CreateParameter("@password", adVarChar, adParamInput, 32)
com("@username") = strUserName
com("@password") = strPassWord
Set rs = com.Execute
If rs.EOF And rs.BOF Then
Validate = False
Else
Validate = True
End If
Me.UserName = strUserName
Me.PassWord = strPassWord
Set com = Nothing
Set rs = Nothing
End Function
从com.CommandType就可以看出二者是不同的......