dim cn as new adodb.connection
dim rs as new adodb.recordset
dim databasename,servername,username as string
dim i as long
servername="aaa"
databasename="aaa"
username="aaa"
On Error GoTo error1
cn.open "Provider=SQLOLEDB.1;Persist Securityi nfo=False;User ID=" & UserName & ";Initial Catalog=" & DatabaseName & ";Data Source=" & ServerName & " ;Connect Timeout=30"
rs.open "select * from aaa",cn
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
End If
i = .RecordCount
exit sub
error1:
msgbox "打开数据库出错"
连接是否成功
connection.state=adstateopen
看结果集
dim db as connection
dim rs as recordset
set db=New connection
db.open "..."
set rs=db.execute "SELECT * FROM ..."
debug.print rs!Field1 & vbTab & rs!Field2
有个现成的例子:
Public Function GetAbrByOptn( _
Optional iOrder As Integer = 1, _
Optional EmpID As String = "", _
Optional ID As Integer = 0, _
Optional TargetCountryID As Integer = 0, _
Optional PassportID As String = "", _
Optional PassportOptn As Integer = 0, _
Optional StartTime As Date = "1899/01/01", _
Optional StartTimeOptn As String _
) As ADODB.Recordset
On Error GoTo ErrorProcess
Dim strSql As String
Dim strWhereOrAnd As String
Dim Conn As ADODB.Connection
Dim p_RS As ADODB.Recordset
m_SuccessOrNot = False
'' Open ADO connection
Set Conn = New ADODB.Connection
Set p_RS = New ADODB.Recordset
'' 员工编码的组合
If EmpID <> "" Then
strSql = strSql & "WHERE EmpID = '" & EmpID & "' "
End If
'' 内部编号组合
If ID <> 0 Then
strSql = strSql & cStrWhereOrAnd(strSql) & " ID = " & ID & " "
End If
'' 国家条件组合
If TargetCountryID <> 0 Then
strSql = strSql & cStrWhereOrAnd(strSql) & " TargetCountryID = " & TargetCountryID & " "
End If
'' 护照及其条件的组合
If PassportID <> "" Then
Select Case PassportOptn
Case 0
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID = '" & PassportID & "' "
Case 1
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '%" & PassportID & "%' "
Case 2
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '%" & PassportID & "' "
Case 3
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '" & PassportID & "%' "
Case Else
strSql = strSql & cStrWhereOrAnd(strSql) & " PassportID like '" & PassportID & "' "
End Select
End If
'' 开始时间及其条件的组合
If StartTime <> "1899/01/01" Then
strSql = strSql & cStrWhereOrAnd(strSql) & " StartTime " & StartTimeOptn & "#" & StartTime & "# "
End If
'' 结果集排列顺序
Select Case iOrder
'' Order By EmpID, ID
Case 1
strSql = strSql & "Order by EmpID,ID"
Case Else
strSql = strSql & "Order by EmpID,ID"
End Select
'' Open the Connection
Conn.Open G_ADO_ConStr
'' Open the recordset
p_RS.Open strSql, Conn, adOpenStatic
Set GetAbrByOptn = p_RS
m_SuccessOrNot = True '' Success Flag
Exit Function
ErrorProcess:
Err.Raise Err.Number & Err.Source
Set GetAbrByOptn = Nothing
m_SuccessOrNot = False '' False flag
End Function
使用结果集合:
Private Sub cmdGetAbrByOptn_Click()
On Error GoTo ErrorProcess
Dim Obj1 As Object
Dim sTmp As String
Dim sTmp2 As String
Dim iTmp As Integer