Eliminate any database connection logic from your ASP pages by implementing a simple VB component that wraps up the most common ADO functionalities and in conjunction with a UDL.
1. Create a wrapper ADO component using VB.
Expose the all the typical methods you use such as GetRecordSet (for reads) and Execute (for Insert/Update/Delete). Add a string parameter to all of your ADO wrapper methods. This will hold the name of a UDL.
The ADO wrapper component performs the task of loading the DB connection logic from the UDL--you just need to specify a standard location to store all the UDLs for your site. (i.e. C:\UDL or D:\UDL --only the ADO wrapper component needs to know about this physical file location).
Put your wrapper ADO component in an MTS/COM+ Package.
Your ASP files then need to instantiate the wrapper ADO component and pass the UDL name as another parameter. They don't need to know anything about the database --just pass the UDL name.
Dim oADO 'holds custom ADO component wrapper
Dim rs 'my recordset
Dim sSQL 'can contain SP with parameters or raw
SQL - advanced
functionality includes using Input Parameters and
passing them in an array
sSQL = "SELECT * FROM AUTHORS"
Set oADO = CreateObject("MY_ADO.Data_Services")
Set rs = oADO.GetRecordSet(sSql,"PUBS") 'the wrapper
ADO component will
read all DB connection info from D:\UDL\PUBS.UDL
Do While Not rs.EOF
You can completely eliminate ADO and Recordsets from ASP using ADO 2.6 (or higher), MS SQL Server 2000 (or higher), and XML 3.0 (or higher). Wrap up the ADODB.Stream method, pass XML back directly from SQL Server to the component, then pass it on to the calling method, or ASP, from the wrapper ADO component. All your ASP does is work with XML.
Other advanced techniques include referencing only the wrapper ADO component from other VB component methods, and having those methods return XML to the browser, thus eliminating ALL knowledge of the source of the data from ASP.