The use of Active Server Pages is supported in your package as standard. Of course this also includes support for Access databases. However, this is often very difficult, because a variety of factors become involved. To make the use of Access databases a little simpler, instructions are given below.

Attention! Due to a bug in the MDAC (Microsoft Data Access Components, the component that provides communication between the web server and the Access database) drivers from Microsoft, we cannot offer support in case of problems with Microsoft Access 97 databases. We advise you to use Access 2000, Access XP (2003) or Access 2007.

Examples of connection strings

There are different ways to make a connection with an Access database: via an OLE DB provider or via an ODBC (-Open Database Connectivity-) driver. An OLE DB connection string is:

Access 2007, 2003

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:wwwyourlogindatabasedatabase.accdb;"

Examples of ODBC connections

MS Access ODBC (2007):

"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=d:wwwexample.comdatabasedatabase.mdb;"

MS Access ODBC (2000, 2003):

"Driver={Microsoft Access Driver (*.mdb)};Dbq=d:wwwexample.comdatabasedatabase.mdb;"

In general, an Ole Db connection is faster than an ODBC connection and is therefore recommended by VEVIDA.

Login information can also be included in these examples of Ole Db connections. After the database name, give:

User;Password=;

In some cases the type of database must then also be defined in the connection string:

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:wwwexample.comdatabasedatabase.mdb;Jet OLEDB:Engine;"

For more information, see for example: http://msdn.microsoft.com/en-us/library/aa140022.aspx, http://en.wikibooks.org/wiki/JET_Database/Creating_and_connecting.

Because “Provider” can have quite a wide range of values (which is less the case with ODBC), it is difficult to determine how your connection string needs to look with different databases. At http://www.connectionstrings.com and http://www.carlprothman.net/Default.aspx?tabid=81 you will find as complete an overview of connection strings as possible.

Reading from a database

To read from a database using ASP data, the following code is sufficient. You must of course replace the string D:wwwexample.comdatabasedb.mdb with the location of your database file. The database folder stated corresponds to the database folder in the root of your FTP site.

NB: In this example it is not necessary to use the Server.MapPath command. This command will not function in this setup.

<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim Conn, strConn

Set Conn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:wwwexample.comdatabasedb.mdb;"
Conn.Open strConn

Dim DTBrs
Set DTBrs = Server.CreateObject("ADODB.Recordset")
DTBrs.Open "tblTabel", Conn, 3, 3

' Doe hier wat u wilt doen in de database
' U kunt hier de ADO commando's DTBrs("VELDNAAM") of DTBrs.Fields("VELDNAAM") gebruiken. 

DTBrs.Close
Conn.Close
Set DTBrs = nothing
Set Conn = nothing
%>

The 3, 3 at the end of the 4th line can be replaced by the official VB constants. However, you must have access to the file adovbs.inc for this. This file can be downloaded here.

Writing to a database

Writing to a database is somewhat more difficult, because specific rights need to be assigned to the database. To make this easier, you can best place all the databases into one directory. We recommend putting this in d:wwwexample.comdatabase. We have put the rights on this directory by way of standard – you can use them to write to the database.

An example of a script for writing looks like this:

<%@LANGUAGE=VBSCRIPT%>
<%
Option Explicit
Dim Conn, strConn

Set Conn = Server.CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:wwwexample.comdatabasedb.mdb;"
Conn.Open strConn

Dim DTBrs
Set DTBrs = Server.CreateObject("ADODB.RecordSet")
DTBrs.Open "tblTabel", Conn, 3, 3

' Zoek het juiste record
Do while not DTBrs("PRIMAIRYKEY") = anykey and not DTBrs.EOF
DTBrs.movenext
Loop

' Als het juiste record is gevonden
DTBrs("VELDNAAM") = "gegevens"
DTBrs("VELDNAAM2") = "gegevens"
DTBrs.Update
%>

Or, to create a new record:

<%
DTBrs.Addnew
DTBrs("VELDNAAM") = "gegevens"
DTBrs("VELDNAAM2") = "gegevens"
DTBrs.Update
%>

And ALWAYS close the connections and record sets that you opened!

<%
DTBrs.Close
Conn.Close
Set DTBrs = Nothing
Set Conn = Nothing
%>

Reducing and optimising an Access database

If a database is changed regularly, parts of the database become fragmented (“divided”). Therefore the Compact Database Utility from Access must be run regularly to defragment the database. This also reduces the size of the database, which improves performance and reduces download times.

If you do not have the MS Access program, you can compact (reduce) the database with the JetComp utility. You can download this from Microsoft:
Jet compact utility.

Or online via an ASP script:

<%@ Language=VBScript %>
<%
Option Explicit

' (c)2005 - 2008 VEVIDA Services BV
' 20051020.1947 - Jan Reilink <info@vevida.com>
'               Laatste wijziging: 20080826.1710
'
' Het onderstaande ASP script verkleint (compact) een Access 
' database waarna het script redirect naar een op te geven 
' pagina, bijvoorbeeld als onderdeel van een CMS.
'
' Wijzig "example.com" in uw eigen inlognaam en de database
' naam "database.mdb" naar de naam van uw database.

Const Jet_Conn_Partial = "Provider=Microsoft.ACE.OLEDB.12.0; Jet OLEDB:Engine; Data source="
' Const Jet_Conn_Partial = "Provider=Microsoft.Jet.OLEDB.4.0; Data source="

Dim LoginNaam, DatabaseNaam, strKlaarURL
Dim strDataBaseFile, strTempFile, strBackupFile
Dim fso, objJRO, SourceFile, DuplicateFile, CompactedFile

' Uw loginnaam
LoginNaam = "example.com"

' Databasenaam
DatabaseNaam = "database.mdb" ' of .accdb voor Access 2007

' Bron database bestand
strDataBaseFile = "d:www" & LoginNaam & "database" & DatabaseNaam

' Compacted database bestand
strTempFile = "d:www" & LoginNaam & "databasetmp_" & DatabaseNaam

' Eventueel Backup bestandsnaam, gebruik accdb als extensie voor Access 2007
' strBackupFile = Replace(strDataBaseFile, "accdb","BAK")
strBackupFile = Replace(strDataBaseFile, "mdb","BAK")

' de bestandsnaam of url van de pagina waarnaar het script moet 
' doorverwijzen zodra het klaar is. Standaard naar de root ("/")
strKlaarURL = "/"

' Onder deze regel hoeft u niets aan te passen, tenzij
' u het maken van een backup uit wilt schakelen
' ---------------------------------------------------------------------

Set objJRO = Server.CreateObject("JRO.JetEngine")
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists(strDataBaseFile)) Then
Set SourceFile = fso.GetFile(strDataBaseFile)

' Eventueel back-up van de database maken
SourceFile.Copy strBackupFile

If (fso.FileExists(strTempFile)) Then
Set DuplicateFile = fso.GetFile(strTempFile)
DuplicateFile.Delete
Response.Write "Bestaande tempfile is verwijderd!"
End If

objJRO.CompactDatabase Jet_Conn_Partial & strDataBaseFile, Jet_Conn_Partial & strTempFile
SourceFile.Delete
Set CompactedFile = fso.GetFile(strTempFile)
CompactedFile.Copy strDataBaseFile
CompactedFile.Delete

' het script verwijst door naar de volgende pagina zodra het klaar is met
' het compacten van de database.
Response.Redirect strKlaarURL

' of, geef een tekstuele bevestiging (comment de Response.Redirect regel hierboven)
' Response.Write strDataBaseFile & " verkleind."

Else
Response.Write "<b>" & strDataBaseFile & "</b> - Het bestand is niet gevonden!"
End If

Set fso = Nothing
Set objJRO = Nothing
%>

Access-related error messages

ASP and Access error messages are unfortunately very generic.

Service Unavailable: The primary cause of a Service Unavailable message is that the application pool (AppPool) in which the website runs, has stopped. This can be due to a crash of this AppPool, a crash of a “parser” or driver in this AppPool, or due to deliberate stoppage by VEVIDA (for example in case of misuse, whether or not by third parties).
The error message (0x)8004005 primarily means: Access denied, or often in another form: Operation Must Use an Updateable Query. This simply means that the script does not have certain rights to perform an action. The error message “Operation Must Use an Updateable Query” is primarily given when an Access database does not have write permissions for the IUSR (-visitor-). The write permissions can be assigned via the website settings link on MyVEVIDA. To do this, replace the www with “database” in that location and click ‘assign write permissions’. It may also be that the web space quota is full, meaning that no extra data can be written to the database.
Couldn’t use ; file already in use. : This Access database-related error message is caused when a user opens a database and does not have create permissions on the respective folder. The Jet database engine can result in no lock file being written. This can have two causes:

  • The write/modify rights on the database-folder are missing;
  • The quota for a user (FTP or IUSR) is full.

Links to more information:

Online management of an Access database

The full online management of an Access database is possible with a package such as dbadmin. The configuration assigns itself, documentation is available.
It is recommended to place dbadmin in a protected folder. The package generates a ‘dbadmin.xml’ file with your databases. Place this in the same protected folder, or in the database folder.
You can also download Dbadmin here, including all the available languages.

This package was not developed by VEVIDA and is not “Access 2007 ready”.

Tips and recommendations for working with Access databases

Microsoft devotes a large page to tips and recommendations for working with and optimizing Jet (Access) databases:
How to keep a Jet 4.0 database in top working condition.

1. Important tip
You can solve many problems yourself by switching off the “friendly” error messages from Internet Explorer. Instead of the message “HTTP 500 error” you will then see an error message with the statement of the line in which an error occurs in your code.
You can find this option under internet options in the advanced tab.

The Web Application Stress Tool is a handy tool that you can use to see if your scripting is good: http://www.microsoft.com/technet/archive/itsolutions/intranet/downloads/webstres.mspx

2. Use an efficiently designed database.

An efficiently designed database improves the performance of the database.

3. Do not use reserved words or reserved characters for object names and/or field names
Reserved words or characters that are only used in combination with other words, separated by spaces, can cause database corruption.

4. Regularly make a backup of your database
Create a backup scheme that corresponds to the amount of data that you could/may lose. For example, if you can afford to lose a day of data, make a daily backup. If a week is acceptable, make a weekly backup. A full backup of the database file is the best way to be sure that you can restore the database, should it become corrupt.

5. Microsoft Jet (i.e., Access) is a shared database file system.
A shared database file system means that the file can be shared by a number of users and processing the file is done at client level. Multiple client processes use read, write and file locking operations on the same file. If a process cannot be completed, there is a chance that the file remains in an incomplete state or in a corrupt state. A process could not be completed for the following reasons:

  • If a client unexpectedly stops (they close the browser, the PC crashes, etc.)
  • If a network connection to the server is lost (the user is suddenly without internet)

Microsoft Jet is not intended for high-stress server applications, high-concurrency server applications or server applications that run 24 hours per day, seven days per week. This includes server applications such as web applications, e-commerce applications, transactional applications and server-message applications. For these types of applications, the best solution is to select a true client/server database system, such as MySQL or MS SQL Server.
If you use Microsoft Jet in high-stress applications such as Microsoft Internet Information Server (IIS) you may experience one of the following problems:

  • Database corruption
  • Stability problems, such as IIS (the application pool) crashes, or “locking up”
  • Inexpected errors or constant driver (MDAC, OLE DB provider) errors when connecting with a valid database

6. Minimise the number of simultaneous connections from every client
We recommend that you always only make one connection with a Microsoft Access database. Every connection represents an individual client connection with the database, even if these connections originate from the same client process.
To optimise performance and network I/O (Input/Output) and to reduce multi-user stress on the back-end database, design the application to make a single connection with the database. Distribute this connection over a number of record sets, if necessary. This has the additional advantage that read and write delays in the application are avoided. As standard, there is a delay of five seconds between writing a value to the database and the possibility to read this updated value if two different connections are made for reading and writing.

7. Use ADO to connect to a Microsoft Access database
If you connect to a Microsoft Access database via ADO, we recommend using the Microsoft Jet OLE DB Provider instead of the Microsoft Access ODBC driver. More information about this can be found at:
Using Microsoft Jet with IIS
If you have a comment on this article, please send it to us via the communication module at MyVevida.

« Back

Related articles

Customer service

Cannot find what you are looking for? Please contact our customer service:

We are glad to be of service.