Migrating from Sql Server to Access in ASP.NET

Maybe the project is relatively small, or maybe they just don't want to spend the extra cash on the costs of Sql Server hosting - or perhaps they have been using Access for ages, and want to make the current database available on the web. Whatever. That's the decision and you're stuck with it. Nearly all the articles you can find talk about moving up to Sql Server. This one will look at the considerations that should be taken into account when moving the other way.

Provider

The recommended method of connecting to Access is via the Jet 4.0 OleDb provider. It is worth mentioning at this point that while I refer to "Access", I am in fact referring to a stand-alone .mdb file. Access is the desktop application with nice designers, forms, reports, macros and modules. None of these are available to an ASP.NET application, unless MS Access is installed on the web server and Office Interop is used. How to do that is beyond the scope of this article.

Access 2007 has a new format and provider - the ACE.OleDb provider, and databases can be saved as .accdb files. Unless you have access to the server on which the application will be hosted, you should avoid using this provider for the time being. It was only made available as a download outside of Office 2007 fairly recently, and it is highly unlikely to be found on many commercial web hosting machines. Whether it will be made available as part of the Longhorn server's default installation is not known at the moment. Jet, on the other hand, is now part of the Windows platform and comes as part of the default installation for XP, Windows Servers and Vista.

Connecting

The 3 principal ways of establishing a connection are: via an AccessDataSource control, a SqlDataSource control, or finally, directly from code. Whichever you choose, the best place for the .mdb file is the App_Data folder. This folder has been configured not to serve files that are requested via http, which means that no one can browse to www.yourdomain.com/App_Data/yourdb.mdb and download the database. It also allows the use of the alias |DataDirectory| in connection strings, which automatically resolves to that location, regardless of the file system path of the machine, meaning no changes are necessary to connection strings from one machine to another.

• The AccessDataSource control makes use exclusively of the Jet OleDb provider. Consequently, it exposes a read-only ProviderName property. It also exposes a DataFile property that accepts the location and name of the .mdb file, which can be a relative path eg ~/App_Data/yourdb.mdb or a full file system path. You cannot set a ConnectionString property, which means that you are unable to use the AccessDataSource control for connecting to password protected .mdb files.
• The SqlDataSource control can be used with Access in the same way as with Sql Server. It takes a connection string, which ideally should be held in one place - the web.config.
• Connecting directly via code is done in exactly the same way as with Sql Server, except that classes from the System.Data.OleDb namespace are used instead of System.Data.SqlClient eg an OleDbConnection, OleDbCommand, OleDbDataReader etc.

Connection String

If you place the .mdb file in App_Data, the only thing you need to change from the following is <yourdb.mdb>:

Provider=Microsoft.Jet.Oledb.4.0;Data Source=|DataDirectory|<yourdb.mbd>

A common error is to merge 'Data Source' into one word, which leads to "Could not find installable ISAM" errors. Other variations for connecting to a password-protected file etc can be found at www.connectionstrings.com/?carrier=access. DO NOT use the ODBC version, or DSNs. ODBC is not stable in a multi-user environment, and is a major cause of corrupted databases.

Another common error message, "Operation must use an updateable query" results from not applying the correct permissions on the App_Data folder. On Win XP Pro, the ASPNET account needs modify permissions, and on Windows Server 2003, modify permissions must be granted on the NETWORK SERVICE account. This is because when the database is accessed, a temporary .ldb file, which is required, can be created.

Main differences between Access and Sql Server

Stored Procedures

Access does not offer stored procedures, triggers or user defined functions. The best that Access offers in this area is Saved Queries. These are individual SQL commands that can be called via OleDb. They can be created either directly within Access itself using the Query Designer, or by passing DDL commands from code: "Create Procedure MyQuery As .....". When called from code, they are treated in exactly the same way as Stored Procdures in Sql Server. The three main differences are that parameters are not declared within the body of the procedure; you cannot create batch commands; and there is no place for control-of-flow code, such as Case, If etc. There is another quirk in that saved SELECT statements, although they are created using the "Create Procedure" statement, are actually saved as View objects within Access. This means they don't appear in the list of Stored Procedures in an Access- or SqlDataSource control. You need to manually apply the name of the query to the SelectCommand of a DataSource. Also, they are found under Views in the Database/Server Explorer pane in Visual Web Developer/Visual Studio.

Update: it has been pointed out to me that you can indeed declare parameters and their datatypes when running a CREATE PROCEDURE... command. It is not required, but doing so may save a tiny fraction of performance in negating the need for a lookup on the database fields to get the datatype. The correct syntax is as follows:

Thanks to Hans_v for pointing this out to me.

Parameters

OleDb parameters are positional. This means that the names given to parameters are irrelevant, and that parameters must be added to the Parameters collection in exactly the same order in which they appear within the SQL. You can use a name prefixed with @, or a ?, or any string that doesn't match an existing field name, so the following parameterised queries are functionally equivalent:

The first example is fine for running directly from code, but will not work if used as a Saved Query. In that case, each parameter must have a different name.

Delimiters

String delimiters are a single quote if being called directly from code, or a double quote if being used in a Saved Query
Numeric delimiters are identical to Sql Server - there are none.
Date delimiters can either be the single quote or octothorpes (#)

As with Sql Server, you can eliminate many delimiter-related problems by using parameters instead of dynamic SQL.

Wildcards

The wildcard character is %, as in Sql Server. However, if you want to design and test your query in Access, you must use *. Once you have it working, if you want to save it within Access, you must remember to revert back to %. OleDb doesn't understand * and will treat it as a literal.

Functions

MS Access offers a plethora of built-in functions. However, not all of these are available via the Jet OleDb provider. Generally, you will find that standard VBA functions are available, although even then it is not always guaranteed. I have often found, especially with string functions, that bringing more data back from a query than you need, and then using C# or VB string functions on the results is considerably quicker than getting Access to do it. YMMV, and it is always worth testing whether processing performed by the client app is quicker, especially for long running database calls.

Limitations

Apart from the perfomance limitations of using a file-based system rather than a server-based system, Access databases cannot exceed 2GB in size. Access databases have a tendency to bloat as a result of Update and Delete commands, and therefore need to be compacted regularly. This can be done via code using Jet Replication Objects (JRO), which are COM objects. A guide on how to perform Compact and Repair from code is available here. This will help to reset indexes and improve perfomance of the database as well. However, you don't want to be running this code at peak useage times. Chances are that the database will be unavailable while the process is taking place.

DataTypes

The Table Designer within Access appears to offer some new datatypes. Here's how they work:

• TEXT - for strings up to 255 characters in length. The storage size is settable
• MEMO - equivalent to Sql Server's TEXT datatype. Will store up to 2GB in theory, but since that's the limit to any object - table .mdb file etc in Access, you won't use all of that in one go.
• NUMBER - to be used for integers, doubles - any numeric value. DDL allows you to set the type of number you want to store, as does the designer.
• DATETIME - stores data and time. Built-in functions are available: DATE() will store the current date together with 12 noon in the time portion. NOW() will store both the current date and time. GETDATE() is not available.
• CURRENCY - not recommended for ASP.NET apps. Use NUMBER instead.
• AUTONUMBER - incremental seed, similar to IDENTITY in Sql Server. You must use @@IDENTITY to get the most recently added value, not SCOPE_IDENTITY().
• YESNO - boolean values. YES is -1, and NO is 0.
• OLEOBJECT - similar to IMAGE. Not recommended as a place to store files. Use the file system instead, and store a pointer to the filepath in a TEXT field.
• HYPERLINK - causes confusion. This datatype is purely for storing and displaying hyperlinks in Access Forms. It won't magically convert your web address to a hyperlink in an ASP.NET page. All it will do is shove # at either end. Don't use it.

More reading can be found here:

ASP.NET Parameter queries with MS Access
Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000