Migrating from Sql Server to Access in ASP.NET

Yes, you did read that correctly. Here's the situation: you know that Sql Server is by far the superior database system (if indeed MS Access can be called a database system). You also know that MS Access databases are not recommended as a data store for web applications. You are comfortable with Sql Server, and haven't really looked at Access for years - if at all. But now, your boss or your client wants you to use it in the next project.

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:

  

CREATE PROCEDURE MyProc AS

PARAMETERS @MyID LONG;

SELECT MyFields FROM MyTable

WHERE MyID = @MyID

  

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:

  

INSERT INTO Categories (CategoryName, Description) VALUES (?,?)

INSERT INTO Categories (CategoryName, Description) VALUES (@CategoryName,@Description)

INSERT INTO Categories (CategoryName, Description) VALUES (param1,param2)

INSERT INTO Categories (CategoryName, Description) VALUES ([p1],[p2])

  


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

 

Date Posted: Friday, November 16, 2007 10:45 AM
Last Updated: Monday, August 23, 2010 9:30 AM
Posted by: Mikesdotnetting
Total Views to date: 16392

6 Comments

Monday, April 27, 2009 10:17 PM - Dave Kilroy

Thanks Mike for this - a real gem to find out that OleDb parameters are positional!

Kind regards

Dave

Friday, August 21, 2009 2:24 PM - wonn1377

I use data loader for migrating almost any data, it helps me to convert MSSQL to MYSQL, MS access to MSSQL, mysql, csv loader, foxpro and MSSQL to MS access, MYSQl, CSV, foxpro etc. In my view this is a best Data Migration Tool

Download Free : http://www.dbload.com

Friday, August 21, 2009 4:58 PM - Mike

@won1377

I think you missed the whole point of the article. It has nothing to do with the actual migration of data whatsoever.

Friday, October 16, 2009 10:01 PM - TheBlackTruth

Thank you so much for your work on this. I have spent literally 6 hours on all the top forums and help sites (Asp.net, KB, etc.) and could not get the simple info about adding the Network Service to the permissions for Access 2007 DB OLEDB calls! Thanks again!

Monday, September 23, 2013 8:20 PM - Lawrence

I am trying to do the Membership and Role management on the Access database using Chapter 12 Security of Beginning ASP.NET Web Pages with WebMatrix as reference. When I run the register.cshtml, I received error: 'Could not find file c:\Windows\system32\Information_Schema.mdb'.

The site is created using the WebMatrix 3.0's empty template. I would like to know whether this is Membership Provider for Access and how to specify 'the provider' in the site.


Thanks

Tuesday, September 24, 2013 5:34 AM - Mike

The WebSecurity helper and SimpleMembershipProvider don't work with Access databases. They only work with SQL Server and SQL Compact Edition. SQL CE was introduced as a replacement for Access in Web applications.
Add your comment

If you have any comments to make about this article, please use this form to do so. Make sure that your comment relates specifically to the article above. More general comments can be posted through the form on the Contact page.

Please note, all comments are moderated, and some may not be published. The kind of things that will ensure your comment is deleted without ever seeing the light of day are as follows:

  • Not relevant to the article
  • Gratuitous links to your own site or product
  • Anything abusive or libellous
  • Spam
  • Anything in a language I don't understand including gibberish.

I do not pass email addresses on to spammers, so a valid one will assist me in responding to you personally if required.

Recent Comments

Gautam 11/20/2014 8:01 AM
In response to I'm Writing A Book On WebMatrix
Hello Mike, I read your book, loved it! However, I have a few request/suggestions: 1) an example...

Bret Dev 11/19/2014 8:39 PM
In response to The Difference Between @Helpers and @Functions In WebMatrix
Excellent post! One concern - where can you place global @Functions code within an MVC project to Is...

Rob Farquharson 11/19/2014 4:28 PM
In response to iTextSharp - Links and Bookmarks
How can I place text at an absolute position on the page? Also, how can I rotate text?...

Andy 11/17/2014 8:08 PM
In response to MVC 5 with EF 6 in Visual Basic - Sorting, Filtering and Paging
Hello I'm testing your sorting instructions above. This is great and I was able to get it to work...

Gautam 11/17/2014 5:51 PM
In response to WebMatrix - Database Helpers for IN Clauses
Hi Mike, I am very new to programming: In the above example if I want to use a delete button the...

donramon 11/17/2014 3:22 PM
In response to Entity Framework 6 Recipe - Alphabetical Paging In ASP.NET MVC
Congratulations on your new website look and the excellent articles. Thank you!...

Gautam 11/17/2014 11:26 AM
In response to Looking At The WebMatrix WebGrid
Hi Mike, I add the jquery script at the end of my html file.. when ajax attribute is added to the be...

Chet Ripley 11/15/2014 6:57 PM
In response to Adding A New Field
It appears the command is case sensitive. I had the same issue as Cameron. When I changed the to it...

Alvin 11/14/2014 12:49 PM
In response to Razor Web Pages E-Commerce - Adding A Shopping Cart To The Bakery Template Site
Great article Mike! When do you plan to extend the bakery shopping cart beyond this point?...

Gautam 11/14/2014 10:16 AM
In response to Web Pages - Efficient Paging Without The WebGrid
to get the count can we use only the below sql, why to join category and author table var sql =...