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

How To Create Index In Access

To know about what the index is and its advantage and disadvantage and why we use index in access or any other database. Just follow the link…

'http://office.microsoft.com/en-us/access-help/create-and-use-an-index-to-improve-performance-HA010210347.aspx


Here Is How To Create Index Programmatically.

To create index in access we need four objects and a global method or function
Objects

1.    Database
2.    TableDef
3.    Index
4.    Field
Method

1.    OpenDatabase
Syntax

Function OpenDatabase(Name As String, [Options], [ReadOnly], [Connect]) As Database
Function CreateIndex([Name]) As Index
Function CreateField([Name], [Type], [Size]) As Field

First we need to open a database using OpenDatabase which return a database as Database object as you can see in syntax section.We set the returned database object to our Database object variable like this.

Set dbsNorthwind = OpenDatabase("C:\Program Files\Microsoft Visual Studio\VB98\NWIND.mdb")

Then we need to set our TableDef object variable to the table to which we want to create index like this.

Set tdfEmployees = dbsNorthwind ! Employees

Symbol ! is used to get database table as TableDef Object. You can use the same line for other tables like this.

Set tdfEmployees = dbsNorthwind ! Products
Set tdfEmployees = dbsNorthwind ! Categories

And so on…

Next we need to use our tdfEmployees variable and its CreateIndex method to create new index “CountryIndex” and set it to our idxCountry variable like this.

Set idxCountry = tdfEmployees.CreateIndex ("CountryIndex")

Now we have named our new index to “CountryIndex” and next we need a new field to index.
To do so we need to use our tdfEmployees variable ‘s CreateField method to create new field and set it to our Nfield variable like this.

Set Nfield = tdfEmployees.CreateField("Country")

Next we need to append our newly created field to Fields collection and index to tdfEmployees object variable ‘s Indexes collection and tdfEmployees variable ‘s Refresh method to reflect changes to our UI(User interface) and finally close our database using dbsNorthwind object variable’s Close method to free up computer resources.

**Complete Code**

   Dim dbsNorthwind As Database
   Dim tdfEmployees As TableDef
   Dim idxCountry As Index
   Dim Nfield As Field

Set dbsNorthwind = OpenDatabase("C:\Program Files\Microsoft Visual     Studio\VB98\NWIND.mdb")

   Set tdfEmployees = dbsNorthwind!Employees
   Set idxCountry = tdfEmployees.CreateIndex("CountryIndex")
   Set Nfield = tdfEmployees.CreateField("Country")
   
   idxCountry.Fields.Append Nfield
   tdfEmployees.Indexes.Append idxCountry
   tdfEmployees.Indexes.Refresh
             
   dbsNorthwind.Close

Put this code in either Button’s click event or Form’s load event and you good to go.
Here are the images before and after code execution.