Here I will show you how to connect your existing ODBC database (e.g. Ms Access,
Paradox, Dbase, Foxpro) without configuring DSN trough ODBC32 Icon from Control Panel.
There are so many reason why we need to connect to our database On-The-Fly. Some of
them are listed below :
My hosting services charge some fee to setting DSN to your database. I don't want to pay
that much for setting up a DSN on their machine.
I have an ASP project on a machine that need access to database. I plan to move my
project several times on a different machine, maybe to install it on my boss notebook. I
don't want to waste my time to setting up a DSN everytime I move my application to a
different machine. I want them to set up themselves automatically.
All I have to do is create a virtual directory that has a read and execute permission
on it. That's all.
Please note that this only work on a file based database like Ms Access/Dbase/Foxpro. It
will not work on Oracle, Sybase, Ms SQL Server and other server based database. I'm using
Ms Access for an example. Here's the steps :
Specify what database you use (e.g : Ms Access).
Make a file DSN from ODBC32 in Control Panel to get the connection string (this step is
not necessary if you know the connection string to use for your database).
Double Click on ODBC32 icon.
On ODBC Data Source Administrator dialog, choose File DSN and click Add
From Create New Data Source dialog, choose which type of database you use and click Next
Type the name of your new File DSN e.g. "test" (click browse to specify
another folder where your File DSN file reside). Click Next and then Finish.
For Ms Access and Ms Excell type, you must tell the dialog where your database is. It
will ask you for a database name. In this cases, select any database name on your
computer.
Open the DSN file using any text editor. Usually it resides on "C:\Program Files\Common Files\ODBC\" folder.
Use that string on your ODBC connection in global.asa with changes on the
"DefaultDir" (or the "DBQ Value" on Ms Access file) string value.
The changes on DefaultDir (or DBQValue) will be generated on the fly and then stored on
a Session variable.
' Automatic DataConnection
ScriptName = Request.ServerVariables("SCRIPT_NAME")
tmpScriptName = Mid(ScriptName,2)
intPos = Instr(tmpScriptName,"/")
VirtualDir = Left(tmpScriptName, intPos - 1)
AbsolutePath = Server.MapPath ("/" & VirtualDir)
DBQValue = "DBQ=" & AbsolutePath & "\YourDatabaseName.mdb"
' the string below is a duplicate string from the DSN file.
Session("DataConn_ConnectionString") = _
"DRIVER=Microsoft Access Driver (*.mdb);" & _
"UID=admin;" & _
"UserCommitSync=Yes;" & _
"Threads=3;" & _
"SafeTransactions=0;" & _
"PageTimeout=5;" & _
"MaxScanRows=8;" & _
"MaxBufferSize=512;" & _
"ImplicitCommitSync=Yes;" & _
"FIL=MS Access;" & _
"DriverId=25;" & _
"DefaultDir=;" & _
' you can change this DefaultDir
' DBQValue is generated on the fly
DBQValue
' End of Automatic DataConnection
Your database must be stored within the virtual directory If you specify another
directory within your virtual directory to store your database (e.g.
/VirtualDir/DataBase/MyData.mdb), you must change DBQValue variable with :
To Use the connectionn string that we produce, from any asp file, simply use this code :
<%
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open Session("DataConn_ConnectionString")
%>
Note :
Your virtual directory must reside on a directory without spaces. I have trouble to
connect my database when I put my virtual directory on such directory.
You can also use a shorter connection string without add another string value such as
UID, PWD etc. depend on the ODBC Driver you use. Here's the long connection string that I
used:
' Automatic DataConnection
ScriptName = Request.ServerVariables("SCRIPT_NAME")
tmpScriptName = Mid(ScriptName,2)
intPos = Instr(tmpScriptName,"/")
VirtualDir = Left(tmpScriptName, intPos - 1)
AbsolutePath = Server.MapPath ("/" & VirtualDir)
DBQValue = "DBQ=" & AbsolutePath & "\YourDatabaseName.mdb"
' the string below is a duplicate string from the DSN file.
' This value is generated on the fly
Session("DataConn_ConnectionString") = _
"DRIVER=Microsoft Access Driver (*.mdb);" & _DBQValue
' End of Automatic DataConnection
This issue of 15 Seconds contain an example of how to create an ISAPI server extension in MSVC 4.2 with ODBC 3.0 connection pooling. There is also an evaluation of ODBC 3.0, OLEDB, ADO and DAO. [Read This Article][Top]
Connection pooling might be the easiest way to speed up your dynamic web pages reading from SQL Server. Unfortunately, connection pooling within is turned off by default in Active Server pages. Probably because connection pooling is rarely understood in its entirety. This issue discusses connection pooling with ASP, ISAPI, IDC, and Visual Basic applications. Included is a discussion about ODBC 3.0 and the newest bug fix for ODBC. [Read This Article][Top]
In this article Amos El-Roy demonstrates how to create a file repository using ASP pages. A seamless approach that maximizes accessibility and lowers administrative overhead is illustrated in the article's example, which is available for download. [Read This Article][Top]
Bill Jeffries's article on Excel's Web Query tool demonstrates how to update selected spreadsheet cells instantly over an HTTP connection. [Read This Article][Top]
The help system presented in Vujosevic and Laberge's article is self contained and can be updated and altered without impacting the original Web application. Much like an online book, the help icon in the Web application dives into an application system for the help option. Each Web page has its own separate help page with a database that contains one row in a table for every calling Web page. Sample code is provided. [Read This Article][Top]
Selva Kumar’s article shows how to create practical Oracle database connectivity from ASP using Oracle Objects for OLE (OO4O). OO40, the Oracle middleware, allows native access to Oracle from client applications using the Microsoft Object Linking and Embedding (OLE) standard. Sample code is provided. [Read This Article][Top]
Cindy Cruciger claims there is a better way to write a functional Active Server Page that allows interaction between a database file and the Web, without getting caught in an SQL nightmare. She offers a snippet of SQL code and adds some logical layers, error checking and formatting. [Read This Article][Top]
Developer Stephan Onisick shows us how to create a standalone/custom recordset and use its organizational ability to perform logical tasks with data without connecting to a database. This article uses a small application written using VBScript, ADO 2.1, and an Excel spreadsheet to record and print computer expenses for tax preparations. The standalone recordset is saved in XML format, and the file can be updated with new data simply by reopening as a recordset and using normal recordset methods. [Read This Article][Top]
Storing frequently used lookup data in a database is a great idea (e.g. order status codes, state names, etc.) that saves tremendous amounts of time in design and maintenance. However, retrieving that data from the database every time it is needed is very inefficient. This article describes how to use Application variables to cache frequently used lookup data in memory to achieve lightning fast access times. In my tests, I've seen as much as a 5000% increase in performance. [Read This Article][Top]
Many offices, particularly in military and government organizations, are required to have someone in charge during office hours. If the official manager is absent, that person delegates responsibility to someone else as acting, but who?
A Key Personnel Today table shows who is acting in every official position and how to reach them.
The FileSystemObject group is a set of COM objects that allow you to
manipulate the file system on from an Active Server Page.
FileSystemObject which is documented here is the version that comes with
IIS 4.0. Which is much different then the limited version which shipped
with IIS 3.0. The set of COM objects which consist of the
FileSystemObject software are all free and cover almost every feature
needed.
