select در vba محیط access

اجرای دستور select در VBA اکسس

توسط admin | گروه sql | 1396/09/03

نظرات 0

 در این مقاله به بررسی نحوه اجرای دستور select به صورت مستقیم در محیط برنامه نویسی VBA در Microsoft Access می پردازیم. لطفا به متدهای زیر دقت فرمائید:

Sub SelectX1()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Select the last name and first name values of all 

    ' records in the Employees table.

    Set rst = dbs.OpenRecordset("SELECT LastName, " _

        & "FirstName FROM Employees;")

    ' Populate the recordset.

    rst.MoveLast

    ' Call EnumFields to print the contents of the

    ' Recordset.

    EnumFields rst,12

    dbs.Close

End Sub


Sub SelectX2()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Count the number of records with a PostalCode 

    ' value and return the total in the Tally field.

    Set rst = dbs.OpenRecordset("SELECT Count " _

        & "(PostalCode) AS Tally FROM Customers;")

    ' Populate the Recordset.

    rst.MoveLast

    ' Call EnumFields to print the contents of 

    ' the Recordset. Specify field width = 12.

    EnumFields rst, 12

    dbs.Close

End Sub


Sub SelectX3()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind

    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Count the number of employees, calculate the 

    ' average salary, and return the highest salary.

    Set rst = dbs.OpenRecordset("SELECT Count (*) " _

        & "AS TotalEmployees, Avg(Salary) " _

        & "AS AverageSalary, Max(Salary) " _

        & "AS MaximumSalary FROM Employees;")

    ' Populate the Recordset.

    rst.MoveLast

    ' Call EnumFields to print the contents of

    ' the Recordset. Pass the Recordset object and

    ' desired field width.

    EnumFields rst, 17

    dbs.Close

End Sub

متد نمایش رکوردهای واکشی شده توسط SELECT در متدهای فوق:

Sub EnumFields(rst As Recordset, intFldLen As Integer)

    Dim lngRecords As Long, lngFields As Long

    Dim lngRecCount As Long, lngFldCount As Long

    Dim strTitle As String, strTemp As String

    ' Set the lngRecords variable to the number of

    ' records in the Recordset.

    lngRecords = rst.RecordCount

    ' Set the lngFields variable to the number of

    ' fields in the Recordset.

    lngFields = rst.Fields.Count

    Debug.Print "There are " & lngRecords _

        & " records containing " & lngFields _

        & " fields in the recordset."

    Debug.Print

    ' Form a string to print the column heading.

    strTitle = "Record  "

    For lngFldCount = 0 To lngFields - 1

        strTitle = strTitle _

        & Left(rst.Fields(lngFldCount).Name _

        & Space(intFldLen), intFldLen)

    Next lngFldCount    

    ' Print the column heading.

    Debug.Print strTitle

    Debug.Print

    ' Loop through the Recordset; print the record

    ' number and field values.

    rst.MoveFirst

    For lngRecCount = 0 To lngRecords - 1

        Debug.Print Right(Space(6) & _

            Str(lngRecCount