Using the Recordset with MS Access and ADO - Write code to Open ADODB connection, Recordset and close open objects

(Page 3 of 4 )

I will be showing you two ways to create recordsets, one explicit and the other implicit. The following discussion should make you comfortable with seeing the differences (notice the highlighted statements in the code).

Create a connection object and then create the recordset

Remember that intellisense will guide you along the way. Just type in the code window, ADODB. (after the period pause to see the drop-down, do not be hasty and type away) and you will get a drop-down as shown, giving the choice of the ADODB object type you want to create. It could be a connection, it could be a command, and so forth. Insert the following code as shown in the next paragraph in the form's Load event.

Now as discussed in my earlier article, declare a string and assign the connection string you copied from the UDL file. With this you can now open the connection. Now you need to give a source for the recordset (declared with the name rst) which in this case is chosen to be an SQL query (statement run against the Northwind database's table 'Employee'). Now you can type rst. (and pause here after the period) and you get immediate help as a tool tip as shown in this picture.

What the tool-tip saying is that you need specify a number of arguments for the recordset to open, the first of which is the source of the data (in this case the SQL Statement), the second is the Connection which is used, the third is the CursorType (these are pointers to the record will be discussed later). The fourth - LockType, deals with the way data is to be made available from the point of view of whether others can make changes to the data or not while it is being requested by you, etc. The fifth is really the options for how you may choose the source of data. Here an SQL statement has been chosen as the source, whereas it could be a table, or a stored procedure in the database under study. In order to open a recordset you may not need all of the parameters, as you shall shortly see.

At present do not concern yourself with all of these different arguments and the variety of values each of them can assume. These will be dealt with in detail in future tutorials. In the code that is shown only the first two arguments are shown, which means the others will assume their defaults. In this explicit way of opening the recordset, you are providing the source of the data as well as the connection object.

Again the code is written without any frills so that you may focus on the essentials. You may notice towards the end of the code, you are setting the two objects to nothing in the shown order. This will remove the objects from the memory. Alternately you may also close the connections but they will remain in memory.

Private Sub Form_Load()
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:

Program FilesMicrosoft OfficeOFFICE11SAMPLES

Northwind.mdb;Persist Security Info=False"
conn.Open strg
Dim strsql As String
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
rst.ActiveConnection = conn
'to open a recordset a source (strsql) and an ActiveConnection 

(conn) are provided.  rst.Open strsql, conn MsgBox ("open")
Set rst = Nothing
Set conn = Nothing
End Sub

Use the connection string directly to open the recordset 

Do you always need to create a connection object and then a recordset object? No. You could open a recordset without creating the connection object. This is the way to open the recordset implicitly, as the following code shows in the click event of a button on the same form. Although this works, the performance is not as good as in the previous case, since each time a recordset is opened a new connection is made; in the explicit method of opening, the same connection can be used for another recordset.

Private Sub Command0_Click()
Dim rst1 As New ADODB.Recordset
Dim strg As String
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:

Program FilesMicrosoft OfficeOFFICE11SAMPLES

Northwind.mdb;Persist Security Info=False"
Dim strsql As String
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
'to open a recordset a source (strsql) and a connections string 

(strg) are provided.  rst1.Open strsql, strg MsgBox 

("rst1 is open")
Set rst1 = Nothing
End Sub 

Next: Review recordset properties using code >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

Comments On: Using the Recordset with MS Access and ADO

· ADO and ADO.NET will be the technologies you may need to pursue in the future....    · Hi Jay,I was working with a form. The example you have given here perfectly...   >>> Post your comment now!