IT News & Technology update

DSNless Connection Code Sample

If you have a secure folder on your server, you may want to avoid using DSN because it's a little bit slower. I've set my data folder to be "secure". This means, the folder has full permissions for the asp engine context, but no permissions for the visitors.

Let's create the connection and recodset objects now.

<%
set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
%>

First way to define where your database is to use relative location by utilizing Server.MapPath method.

<%

'Define the location of your database
'as follows if you want to use a relative folder.
cDBLocation = "/securedata/mydatabase.mdb"

'Construct the connection string using MapPath.
sConnSample = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;Data Source=" & _
Server.MapPath(cDBLocation)

%>

You may want to define your physical database location. You should always use the physical location if you can.

<%

'If you are sure about the pyhsical location
'of your database, it's better to use it that way.
cDBLocation = "c:\securedata\mydatabase.mdb"

sConnSample = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False;Data Source=" & _
cDBLocation

%>

After defining the location, it's time to connect to the database and get the records!

As a sample, I've defined two re-usable functions for opening and closing the connections. Of course, there are much better ways depending on the functional needs of your application, but you can still use the following code.

<%

'ADO Constants
Const adOpenForwardOnly = 0
Const adOpenStatic = 3
Const adCmdTable = 2

sub OpenDB(sConn)
'Opens the given connection
'and attachs the recordset to it
conn.open sConn
set rs.ActiveConnection = conn
'Using a static cursor, you will be able
'to use AddNew and Update methods.
'Consider using adOpenForwardOnly if you
'are only reading records (much faster).
rs.CursorType = adOpenStatic
end sub

sub CloseDB()
'Closes the active connection
'And cleans up the memory
rs.close
conn.close
set rs = nothing
set conn = nothing
end sub

%>

You may execute an SQL statement to get your recordset filled:

<%

OpenDB sConnSample

sSQL = "SELECT Field1, Field2 FROM TestTable"
rs.Open sSQL,,, adCmdTable

'Display the records anyway you want
CloseDB
%>

I hope this sample gives you a quick overview of connecting to Access Mdb files.
You are always welcome to express your feedback!

No Comment | Posted in » ASP, Coding, Database Connection