Using a Connection object in Dreamweaver MX
On the vast majority of webservers, Dreamweaver MX recordsets will work fine.Typical recordset code will look like:
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_site_STRING
Recordset1.Source = "SELECT * FROM Admin_AccessGroups"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
The recordset object has an ActiveConnection property. This property is set from a connection string. This means that a connection object is created for each recordset opened. For recordsets with a client-cursor, the activeconnection property can be explicitly destroyed. This disconnects the recordset from the server.
<% Set Recordset1.ActiveConnection=Nothing Recordset1.Close() Set Recordset1 = Nothing %>
Some servers may have limit on the number of connections to the database that can be opened. This can be overcome by opening just one connection and then re-using that connection for all recordsets. The connection string can be changed to a connection object in the connections file that Dreamweaver MX generates.
<%
' FileName="Connection_ado_conn_string.htm"
' Type="ADO"
' HTTP="true"
' Catalog=""
' Schema=""
'MM_Site_STRING = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source= c:\virtualroot\database\property1.mdb"
set MM_Site_STRING=Server.CreateObject("ADODB.Connection")
MM_Site_STRING.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source= c:\virtualroot\database\property1.mdb")
%>
See how the original string has been commented out. This will ensure that MX will still recognise the connection string internally. Developers might also want to create an ASP file called CloseConnection.asp. This would contain the code:
<% if IsObject(MM_Site_STRING) then Set MM_Site_STRING=Nothing end if %>
So, the site could contain this include file code at the bottom of each page. This would ensure that the connection object has been destroyed.
<!--#include file="CloseConnection.asp" -->
Online References:
http://www.tek-tips.com/gfaqs.cfm/lev2/4/lev3/31/pid/333/fid/618
http://www.devguru.com/Technologies/ado/quickref/recordset_activeconnection.html
