VBA/Excel/Access/Word/Access/SQL Select
Содержание
- 1 Add where clause to the select statement
- 2 Creating a Select Query with ActiveX Data Objects
- 3 Executing a Select Query
- 4 Get DISTINCT records
- 5 Get DISTINCTROW
- 6 Get more than one column
- 7 Get only one column
- 8 Get the top 10 percent
- 9 Get the top 5 percent
- 10 Loop through the ResultSet after executing select statement
- 11 Modifying a Select Query
- 12 Order by two fields
- 13 Order record in a decscending order
- 14 Order the resultset with Order by clause
- 15 Select all columns
- 16 Use and to combine conditions
- 17 Use Between And
- 18 Use between and with number type column
- 19 Use Date function in where clause
- 20 Use IN and like in where clause
- 21 Use IN in select statement
- 22 Use "Is not null"
- 23 Use Is NULL to check if a column is null
- 24 Use Not In
- 25 Use "Select all"
- 26 use SUM in sql statement
- 27 Use where clause and order by clause together
- 28 Use wild card character in link
- 29 Using Date field type in select statement
- 30 You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:
Add where clause to the select statement
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT txtCustFimyRecordsetName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ"") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Creating a Select Query with ActiveX Data Objects
<source lang="vb">
Sub Create_SelectQuery()
Dim cat As ADOX.Catalog Dim cmd As ADODB.rumand Dim strPath As String Dim strSQL As String Dim strQryName As String On Error GoTo ErrorHandler strPath = CurrentProject.Path & "\mydb.mdb" strSQL = "SELECT Employees.* FROM Employees WHERE Employees.City="London";" strQryName = "London Employees" Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath Set cmd = New ADODB.rumand cmd.rumandText = strSQL cat.Views.Append strQryName, cmd
ExitHere:
Set cmd = Nothing Set cat = Nothing MsgBox "The procedure completed successfully.", _ vbInformation, "Create Select Query" Exit Sub
ErrorHandler:
If InStr(Err.Description, "already exists") Then cat.Views.Delete strQryName Resume Else MsgBox Err.Number & ": " & Err.Description Resume ExitHere End If
End Sub
</source>
Executing a Select Query
<source lang="vb">
Sub Execute_SelectQuery()
Dim cmd As ADODB.rumand Dim myRecordset As ADODB.Recordset Dim strPath As String strPath = CurrentProject.Path & "\mydb.mdb" Set cmd = New ADODB.rumand With cmd .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath .rumandText = "[Products by Category]" .rumandType = adCmdTable End With Set myRecordset = New ADODB.Recordset Set myRecordset = cmd.Execute Debug.Print myRecordset.GetString myRecordset.Close Set myRecordset = Nothing Set cmd = Nothing
End Sub
</source>
Get DISTINCT records
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT DISTINCT City FROM Employees ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Get DISTINCTROW
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT DISTINCTROW CompanyName FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID ORDER BY CompanyName;
")
Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Get more than one column
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT FirstName, LastName, PhoneNo FROM Employees") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Get only one column
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT LastName FROM Employees ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Get the top 10 percent
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT TOP 10 PERCENT * FROM Products ORDER BY UnitPrice ASC; ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Get the top 5 percent
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT TOP 5 * FROM Products ORDER BY UnitPrice DESC ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Loop through the ResultSet after executing select statement
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("select * from employees") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Modifying a Select Query
<source lang="vb">
Sub Modify_Query()
Dim cat As ADOX.Catalog Dim cmd As ADODB.rumand Dim strPath As String Dim newStrSQL As String Dim oldStrSQL As String Dim strQryName As String strPath = CurrentProject.Path & "\mydb.mdb" newStrSQL = "SELECT Employees.* FROM Employees" & _ " WHERE Employees.City="London"" & _ " ORDER BY BirthDate;" strQryName = "London Employees" Set cat = New ADOX.Catalog cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath Set cmd = New ADODB.rumand Set cmd = cat.Views(strQryName).rumand oldStrSQL = cmd.rumandText Debug.Print oldStrSQL cmd.rumandText = newStrSQL Debug.Print newStrSQL Set cat.Views(strQryName).rumand = cmd Set cmd = Nothing Set cat = Nothing
End Sub
</source>
Order by two fields
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ" ORDER BY txtCustLastName DESC, txtCustFirstName") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Order record in a decscending order
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT * FROM Employees ORDER BY Country DESC") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Order the resultset with Order by clause
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT * FROM Employees ORDER BY EmployeeID ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Select all columns
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT * FROM Employees") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use and to combine conditions
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT txtCustFimyRecordsetName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ" AND txtCustLastName = "Miller"") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use Between And
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("Select * FROM Products WHERE UnitPrice NOT BETWEEN 10 and 25 ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use between and with number type column
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("Select * FROM Products WHERE UnitPrice Between 10 and 25") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use Date function in where clause
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE ((Year([HireDate])<1993) OR (City="Redmond")) ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use IN and like in where clause
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE City IN ("Redmond", "London") AND ReportsTo LIKE "Buchanan, Steven" ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use IN in select statement
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE City IN ("Redmond", "London")") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use "Is not null"
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("Select * from Employees WHERE ReportsTo IS NOT NULL ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use Is NULL to check if a column is null
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("Select * from Employees WHERE ReportsTo IS NULL") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use Not In
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT * FROM Employees WHERE City NOT IN ("Redmond", "London") ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use "Select all"
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT ALL * FROM Employees ORDER BY EmployeeID; ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
use SUM in sql statement
<source lang="vb">
Private Sub RunningSumSQL()
Dim db As Database Set db = CurrentDb Dim qry As QueryDef Dim sSQL As String On Error Resume Next db.QueryDefs.Delete "temp" On Error GoTo 0 sSQL = "SELECT R1.Event,(SELECT SUM(R2.Duration) FROM Running As R2 WHERE R2.Event < R1.Event) AS StartTime FROM Running As R1" Set qry = db.CreateQueryDef("temp", sSQL) DoCmd.OpenQuery qry.Name
End Sub
</source>
Use where clause and order by clause together
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT txtCustFirstName, txtCustLastName FROM tblCustomer WHERE txtState = "NJ" ORDER BY txtCustLastName, txtCustFirstName
")
Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Use wild card character in link
<source lang="vb">
Sub CreateRst_WithSQL()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strConn As String strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set myRecordset = conn.Execute("SELECT txtCustFimyRecordsetName, txtCustLastName FROM tblCustomer WHERE txtCustLastName Like "M*" ") Do Until myRecordset.EOF For Each fld In myRecordset.Fields Debug.Print fld.Name & "=" & fld.Value Next fld myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Using Date field type in select statement
<source lang="vb">
Sub FindProject()
Dim strSQL As String Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * FROM Employees WHERE BirthDate = #12/31/2007#" "Attempt to find a specific project strSQL = "[EmployeeID] = " & 1 rst.Find strSQL "Determine if the specified project was found If rst.EOF Then msgBox lngValue & " Not Found" Else msgBox lngValue & " Found" End If rst.Close Set rst = Nothing
End Sub
</source>
You must use a pound symbol (#) when delimiting dates for Microsoft Access, like this:
<source lang="vb">
Sub FindProject()
Dim strSQL As String Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * FROM Employees WHERE BirthDate = #12/31/2007#" "Attempt to find a specific project strSQL = "[EmployeeID] = " & 1 rst.Find strSQL "Determine if the specified project was found If rst.EOF Then msgBox lngValue & " Not Found" Else msgBox lngValue & " Found" End If rst.Close Set rst = Nothing
End Sub
</source>