Using MS Access as a .NET Application Backend

Question

I am the lone .NET developer in an non-IT organization. I've been asked to develo ae .NET application using Microsoft Access as the back-end (existing DB).

I don't know where to start.

Since I'm developing by myself, what should I keep in mind to avoid during development? What situations does only a lone developer face?

Please include Microsoft Access specific advice in your answer, since that is germane to the question.

Answer 1

If you are already familiar with a .NET language and MS Access then my advice would be to start off by developing a very simple MS Access database and write a small .NET console application that connects to that database and performs some basic functions e.g. querying/inserting/deleting/updating. Then its just a case of builiding on top of this piece by peice, introducing GUIs/separate libs (dlls) etc on the way.

Unfortunately for you .NET Linq to SQL (ORM) does not support MS Access databases so you will have to develop your Business Objects from scratch (not always a bad thing!).

Here is a good starting point MS Application with C#.

Answer 2

Where to start?

1. Choose a development environment (I would suggest Visual Studio 2008 Express or Professional, depending on the budget and the need for features of the Professional version)
2. Even for one single developer: choose a Version control system !!!! (Subversion has only little administrative overhead, fine for one developer)
3. Choose a .NET Framework version (3.5 is fine unless your application has to run on Win2K; for Win2K use .NET 2.0)
4. Choose a mature programming language (C# or VB.NET, what you or your boss likes best)
5. Choose a GUI technology (for a single developer, I would suggest using WinForms, unless you are going to write a Web application or a command line utility)
6. Choose a mature DB access technology (ADO.NET works for a lot of things, unless you have very high performance requirements that are better dealt with old ADO/OleDB or DAO)
7. EDIT: use Google to find some entry examples according to the choosen technology, or buy yourself a book. For example, here is one for C# using OLE DB to access a MS Access DB. This Access site is a good starting point, too.
8. EDIT2: make yourself familiar with "Microsoft Access" (the Office Application). Not because you are going to use it like a typical user, but you will probably need it for administrative purposes. And the VBA & SQL documentation included will be sometimes helpful, even if you code with C# or VB.NET

9. EDIT3: for reporting purposes, choose a reporting technology. There are plenty of possibilites here, depending on your needs, your skills and/or budget, for example

   • Plain ASCII or CSV reports (coded by hand)
   • HTML or XML reports
   • using Excel as reporting engine
   • using a PDF library like Report.NET
   • using a third party tool like Crystal Reports

   You will find a lot of helpful links when you give "report generation .net" to Google, for example this one.

And finally: come back to SO and ask more concrete questions when you come to the point where you have them.

There might be other constraints, depending on what code is already existing in your organization. And I would avoid technologies like F#, WPF or Linq to Entities.

Answer 3

While there are ways to connect .Net applications to MS Access, one of the strengths of Access is its ability to move its data up to Sql Server and link to it in Access as if it was part of the local database. So your first step should be to put the tables on Sql Server and just have links to the tables in your Access database. Access will still behave exactly as it did before except now you have your data in a real database that you can use from .Net.

Answer 4

Your question is too vague to give more than just general advice. If you have already developed other .Net applications, then the approach to developing this new application should not really be any different.

Database considerations:

The only things to bear in mind when using MS Access as a back-end database are:

1. Scalability - MS Access does not scale very well and is only suitable for a small number of users *EDIT: Numbers vary depending on the type of activity the users are performing - for a reporting solution, Microsoft themselves suggest that up to ~100 concurrent users is the maximum - this white paper provides more information *
2. Security - MS Access does not offer the same sophisticated levels of security that you will find in other database products (SQL Server, Oracle, MySQL)
3. SQL Syntax - there are some subtle differences in the way you write certain types of query for MS Access
4. Other Limitations - MS Access does not support stored procedures, so all your data access code will have to use inline SQL Commands (command.Type = CommandType.Text)
   1. The maximum database size supported by Microsoft Access is 2GB - keep an eye on the growth of the database

Design considerations:

1. Does the existing MS Access database already have some user forms and code modules in it? If so, you could use these as the basis for your application - MS Access uses Visual Basic for Applications (VBA) as it's programming language and there are no tools/utilities that I know of that will port VBA to VB.Net

2. Do similar applications exist elsewhere that could help inform your design?

3. Keep data access code out of your forms as much as possible - try to keep data access code in a separate class/DLL so that it is easier to maintain

4. EDIT: as others have suggested, try to avoid having instances of ADO.Net connection and command objects scattered throughout the user interface - put all database connection code in one class/DLL so that it easier to fix/maintain/replace. I'd also suggest putting all your SQL query statements in a separate class or module for the same reason. *

5. Follow any in-house guidelines that you, or others before you, have put in place.

6. Keep maintainability in mind - someone after you may have to make changes. Use comments in code and give your objects (forms/variables/function names) sensible names

7. Take regular backups of your code - put a copy on a network drive or USB drive every day

Answer 5

One point of advice, encapsulate all Access-specific code within a single class. The class should at least be able to:

• locate the Access .mdb file
• create and open all OleDbConnection objects
  • It's critical that all Connections are guaranteed to close, so wrapping their use in a using block is a very good idea
• (Possibly) build and execute all OleDbCommands (removing the db-specific logic from the consuming components--they should be able to make data-requests and retrieve results while transparently creating the Connection & Command, etc.

Answer 6

For accessing the Access database, you could maybe have a look at NHibernate? As far as I know it supports Microsoft Access and using a library like that could perhaps make things easier if you are going to move the data to some other kind of database later on.

Answer 7

There is a lot of good advice here, all I would add is be sure to build all your data access and modifying classes behind a well defined interface(s). I am sure there will come a time when this application out grows MS Access and having well defined interfaces will make upgrading to another database easier.