Open and Close Methods Example

This example uses the Open and Close methods on both Recordset and Connection objects that have been opened.

Public Sub OpenX()

   Dim cnn1 As ADODB.Connection
   Dim rstEmployees As ADODB.Recordset
   Dim strCnn As String
   Dim varDate As Variant

   ' Open connection.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
   Set cnn1 = New ADODB.Connection
   cnn1.Open strCnn
   
   ' Open employee table.
   Set rstEmployees = New ADODB.Recordset
   rstEmployees.CursorType = adOpenKeyset
   rstEmployees.LockType = adLockOptimistic
   rstEmployees.Open "employee", cnn1, , , adCmdTable

   ' Assign the first employee record's hire date
   ' to a variable, then change the hire date.
   varDate = rstEmployees!hire_date
   Debug.Print "Original data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date
   rstEmployees!hire_date = #1/1/1900#
   rstEmployees.Update
   Debug.Print "Changed data"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date

   ' Requery Recordset and reset the hire date.
   rstEmployees.Requery
   rstEmployees!hire_date = varDate
   rstEmployees.Update
   Debug.Print "Data after reset"
   Debug.Print "  Name - Hire Date"
   Debug.Print "  " & rstEmployees!fName & " " & _
      rstEmployees!lName & " - " & rstEmployees!hire_date

   rstEmployees.Close
   cnn1.Close

End Sub

VBScript Version

Here is the same example written in VBScript to be used in an Active Server Page (ASP). To view this fully functional example, you need to create a system Data Source Name (DSN) called AdvWorks using the data source AdvWorks.mdb installed with IIS and located at C:\InetPub\ASPSamp\AdvWorks. This is a Microsoft Access database file. Use Find to locate the file Adovbs.inc and place it in the directory you plan to use. Cut and paste the following code to Notepad or another text editor and save it as ADOOpen.asp. You can view the result in any client browser.

<!-- #Include file="ADOVBS.INC" -->
<HTML><HEAD>
<TITLE>ADO Open Method</TITLE>
</HEAD><BODY> 
<FONT FACE="MS SANS SERIF" SIZE=2>
<Center><H3>ADO Open Method</H3>
<TABLE WIDTH=600 BORDER=0>
<TD VALIGN=TOP ALIGN=LEFT COLSPAN=3><FONT SIZE=2>
<!--- ADO Connection used to create 2 recordsets-->
<% 
Set OBJdbConnection = Server.CreateObject("ADODB.Connection") 
OBJdbConnection.Open "AdvWorks" 
SQLQuery = "SELECT * FROM Customers" 
'First Recordset RSCustomerList
Set RSCustomerList = OBJdbConnection.Execute(SQLQuery) 
'Second Recordset RsProductist
Set RsProductList = Server.CreateObject("ADODB.Recordset")
RsProductList.CursorType = adOpenDynamic
RsProductList.LockType = adLockOptimistic
RsProductList.Open "Products", OBJdbConnection 
%>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Customer Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Company Name</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Contact Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=150 BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>E-mail address</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>City</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#008080">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>State/Province</FONT></TD></TR>

<!--Display ADO Data from Customer Table-->
<% Do While Not RScustomerList.EOF %>
  <TR><TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RSCustomerList("CompanyName")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("ContactLastName") & ", " %> 
  <%= RScustomerList("ContactFirstName") %> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
  <%= RScustomerList("ContactLastName")%> 
 </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("City")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RScustomerList("StateOrProvince")%> 
  </FONT></TD></TR> 
<!-Next Row = Record Loop and add to html table-->
<% 
RScustomerList.MoveNext 
Loop 
RScustomerList.Close
OBJdbConnection.Close
%>
</TABLE>
<HR>
<TABLE COLSPAN=8 CELLPADDING=5 BORDER=0>

<!-- BEGIN column header row for Product List Table-->

<TR><TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Type</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Name</FONT></TD>
<TD ALIGN=CENTER WIDTH=350 BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Product Description</FONT></TD>
<TD ALIGN=CENTER BGCOLOR="#800000">
<FONT STYLE="ARIAL NARROW" COLOR="#ffffff" SIZE=1>Unit Price</FONT></TD></TR>
<!-- Display ADO Data Product List-->
<% Do While Not RsProductList.EOF %>
  <TR>  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("ProductType")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER> 
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("ProductName")%> 
  </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1>
   <%= RsProductList("ProductDescription")%> 
 </FONT></TD>
  <TD BGCOLOR="f7efde" ALIGN=CENTER>
  <FONT STYLE="ARIAL NARROW" SIZE=1> 
  <%= RsProductList("UnitPrice")%> 
  </FONT></TD>
  
<!--  Next Row = Record -->
<% 
RsProductList.MoveNext 
Loop 
'Remove Objects from Memory Freeing  
Set RsProductList = Nothing
Set OBJdbConnection = Nothing
%>
</TABLE></FONT></Center></BODY></HTML>