<%

' BEGIN USER CONSTANTS

' To just use a DSN, the format is shown on the next line:

'Const DSN_NAME = "DSN=ASP101email"

' Two other samples I used it with. Left in as syntax examples for DSN-less connections

'Const DSN_NAME = "DBQ=C:\InetPub\wwwroot\asp101\samples\database.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=25"

'Const DSN_NAME = "DBQ=C:\InetPub\database\donations.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=25"



Dim DSN_NAME

DSN_NAME = "DBQ=" & Server.MapPath("db_dsn.mdb") & ";Driver={Microsoft Access Driver (*.mdb)};DriverId=25;"

Const DSN_USER = "username"

Const DSN_PASS = "password"

' Ok, I know these are poorly named constants, so sue me!

' This script can be used without actually setting up a DSN, so

' DSN_NAME as well as the other two constants should really be named

' something more generic like CONNECTION_STRING, CONNECTION_USER, and

' CONNECTION_PASS, but I did it this way without really thinking about

' it and I'm too lazy to change it now. If it bothers you, you do it!

' END USER CONSTANTS



' BEGIN SUBS & FUNCTIONS SECTION

Sub OpenConnection

Set objDC = Server.CreateObject("ADODB.Connection")

objDC.ConnectionTimeout = 15

objDC.CommandTimeout = 30

objDC.Open DSN_NAME, DSN_USER, DSN_PASS

End Sub



Sub OpenRecordset(sType)

Dim sSqlString ' as String - building area for SQL query

Dim sCritOperator ' as String - basically "=" or "LIKE"

Dim sCritDelimiter ' as String - parameter delimiter "", "'", or "#"



Set objRS = Server.CreateObject("ADODB.Recordset")

Select Case sType

Case "ListTables" ' Open RS of the Tables in the DB

Set objRS = objDC.OpenSchema(adSchemaTables)

Case "ViewTable" ' Open the Selected Table

Set objRS = Server.CreateObject("ADODB.Recordset")

objRS.Open "[" & sTableName & "]", objDC, adOpenForwardOnly, adLockReadOnly

Case "DrillDown" ' Open the Recordset built by the selected options

Set objRS = Server.CreateObject("ADODB.Recordset")



' Build Our SQL Statement

sSqlString = "SELECT * FROM [" & sTableName & "]"



' If we're limiting records returned - insert the WHERE Clause into the SQL

If sCritField <> "" Then

' Figure out if we're dealinh with Numeric, Date, or String Values

Select Case iCritDataType

Case adSmallInt, adInteger, adSingle, adDouble, adDecimal, adTinyInt, adUnsignedTinyInt, adUnsignedSmallInt, adUnsignedInt, adBigInt, adUnsignedBigInt, adBinary, adNumeric, adVarBinary, adLongVarBinary, adCurrency, adBoolean

sCritOperator = "="

sCritDelimiter = ""

Case adDate, adDBDate, adDBTime, adDBTimeStamp

sCritOperator = "="

sCritDelimiter = "#"

Case adBSTR, adChar, adWChar, adVarChar, adLongVarChar, adVarWChar, adLongVarWChar

sCritOperator = "LIKE"

sCritDelimiter = "'"

End Select

sSqlString = sSqlString & " WHERE [" & sCritField & "] " & sCritOperator & " " & sCritDelimiter & sCritValue & sCritDelimiter

End If



' If we're sorting - insert the ORDER BY clause

If sSortOrder <> "none" Then

sSqlString = sSqlString & " ORDER BY [" & sSortField & "] " & sSortOrder

End If



sSqlString