Access Database Library

[vor]

Here is the function library that I used to use in Asp. I'm an ASP.NET person now. Hopefully some of you will enjoy it. It has basic stuff, Grab single String, Return Array, Open a Disconnected Recordset and regular recordets.

This should really be made into a Class, but eh. Enjoy.

Code: Select all

'Library Written by Pedro "VoR" Sousa
Dim conn, rec 'Global Connection Object Vars

'xtype: 0 = create rec & conn and disconnect , 1 = create rec then Disconnect
function DBdiscRec(sqlcode,xtype,dbFilePath)'Create a Closed RecordSet.
  if xtype = 0 then
     set conn = Server.CreateObject("ADODB.Connection")
     conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =  " + dbFilePath  +";"    
     conn.open
  end if
  
  set rec = Server.CreateObject("ADODB.RecordSet")
  With(rec)
     .CursorLocation = 3
     .LockType = 1
     .Open sqlcode, conn,,,0
     Set .ActiveConnection = Nothing 
  end with
  
  if xtype = 0 then DBclose "conn" 'Now Close the Connection
end function

'==============================================================================================
function DBcloseConnOnRec()'Disconnects RecordSet and Closes Conn
   set rec.ActiveConnection = Nothing 
   conn.close: set conn = nothing 'Now Close the Connection
end function

'==============================================================================================
'xtype : 0 = Connection , 1 = w/ RecordSet for Read, 2 = w/ Recordset for Write
function DBconnection(sqlcode,xtype,dbFilePath)
  set conn = Server.CreateObject("ADODB.Connection")
  conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =  " + dbFilePath  +";"
  conn.open
  
  select case xtype 
     case 1 'Read
         set rec = Server.CreateObject("ADODB.RecordSet")
         rec.open sqlcode,conn,3,1,0 
     case 2 'Write
          set rec = Server.CreateObject("ADODB.RecordSet")
          rec.open sqlcode,conn,3,3,0
   end select
end function

'==============================================================================================
'xtype : 0 = create Connection, 1 = Use Global Connection
function DBreturnArray(sqlcode,xtype,dbFilePath)
   dim trec,tconn
     set trec = Server.CreateObject("ADODB.RecordSet")
   
   'Setup RecordSet, Must use Disconnected Record Set to prevent memo Cropping when Counting on Joined Table
   if xtype = 0 then 'Create Connection
      set tconn = Server.CreateObject("ADODB.Connection")
      tconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =  " + dbFilePath  +";"    
      
      trec.CursorLocation = 3 : trec.LockType = 1
      tconn.open : trec.Open sqlcode, tconn,,,0
      
      Set trec.ActiveConnection = Nothing 
      tconn.close : set tconn = nothing
      
   else 'Use Global Connection ============
      set trec = Conn.Execute(sqlcode)
   end if

    if not trec.eof then
       DBreturnArray = trec.getrows()
    else
       DBreturnArray = ""
    end if
    trec.close : set trec = nothing
end function

'==============================================================================================
function DBreturnVar(sqlcode,varName)
   dim trec
   set trec = Conn.Execute(sqlcode)
   if not trec.eof then
      DBreturnVar = trec(varName)
   else
      DBreturnVar  = ""
   end if
   trec.close : set trec = nothing
end function

'==============================================================================================
function DBexecute(sqlcode,dbFilePath)
  dim tconn
  set tconn = Server.CreateObject("ADODB.Connection")
  tconn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =  " + dbFilePath  +";"    
  tconn.open : tconn.execute(sqlcode)
  tconn.close : set tconn = nothing
end function

'==============================================================================================
function DBclose(xtype)
  select case xtype
     case "rec"
         rec.close: set rec = nothing
     case "conn"
         conn.close: set conn = nothing
     case "all"
         rec.close: set rec = nothing
         conn.close: set conn = nothing
  end select
end function

[Rob]

Nice functions...

I have a set very similar but are heaviliy modified for my own site content management system which runs my own site:- www.dotmagic.co.uk

Excellent contribution...

Thanx