How to create QueryDef Object and open Recordset object from it and enumerate the table that Recordset object contains

How to create QueryDef Object and open Recordset object from it and enumerate the table that Recordset object contains

 

1. Create a module so that we place our code as shown in above figure there for reported use.

2. And place the code as shown in figure

3. And for the execution of the code write the following code in form load event

4. Run the program and output should like as shown in figure

 

 

How explain the code line by line

Dim dbsNorthwind As Database

1.       dbsNorthwind is the object variable that contains the information used by other objects in DAO to manipulate database.

2.        And then set the it to an instance of Database object

3.       And used the With(Executes a series of statements making repeated reference to a single object or structure.) statement to avoid from repeating type of object name. You can also write the code without with statement

Set qdfTemp = dbsNorthwind.CreateQueryDef("", "SELECT * FROM Employees")

EnumerateRecordset qdfTemp

dbsNorthwind.Close

4.       And then created temporary QueryDef object by using the CreateQueryDef method of Database object and assign it to the QueryDef object variable for further filtrating.

5.       And then call EnumerateRecordset procedure by passing the object variable qdfTemp of type QueryDef as shown in function definition.

6.       And control goes to the called function and start execution sequentially.

7.       And created the Result string variable that hold the information of employee table.

8.       And then again with statement for avoid repeating type of object name.

9.       And then used the Name property of QueryDef object to determine weather the query created is temporary or not.

10.   And the used the SQL property of QueryDef object for viewing the SQL instruction executed against the database

11.   And then used OpenRecordset  method of QueryDef object and used the constant dbOpenSnapshot to open the Recordset Object as a copy

12.   And then used loop to enumerate the rows of employees table with the help of the property EOF(returns a value that indicates whether the current record position is after the last record in a Recordset object).

13.   And used the table column name as properties for collecting require information and you can use other column name as you require.

14.   And then use MoveNext method of Recordset Object to movie the record pointer to next record in Recordset Object.  

15.   And the used the message box for output that what we collect from Secordset Object

16.   And then closed the Recordset object

17.   And control move back to the called sub procedure and closed the Database object