Microsoft ActiveX Data ObjectsMicrosoft ActiveX Data Objects*
*Contents  *Index  *Topic Contents

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 = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	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

The following is the same example written in VBScript to be used in an Active Server Page (ASP). To view this example, you need to create a system Data Source Name (DSN) called AdvWorks for the Microsoft Access database file AdvWorks.mdb, which is installed with Microsoft Internet Information Server and located at C:\InetPub\ASPSamp\AdvWorks. 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 1.5 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>

Up Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.