5

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.

George Stocker
  • 57,289
  • 29
  • 176
  • 237
Dhanapal
  • 14,239
  • 35
  • 115
  • 142
  • 1
    Please rephrase the title to express your real question: how to implement a .NET application for ms access. – Stefan Steinegger Jan 05 '10 at 12:37
  • I can't help much with the Access stuff except to say that OLEDB and ODBC should work against it. I have been a lone .NET developer for years. While it is nice to be free of restraints, my best advice is to impose some on yourself. The biggest improvement for me was to get control of my time management using The Pomodoro Technique. In addition to that, I sought out accountability: I now report specifics of my activities twice a week to my employers. I've found both of these to be great non-technical advancements in my productivity. – Joel Cochran Jan 05 '10 at 16:06
  • I'm afraid the people most likely to have significant experience with Jet/ACE (Access developers) are the ones least likely to have experience with .NET. And the .NET developers likely have little contact with Jet/ACE (and for good reason, since it's built for COM not for .NET). You're going to have to be more specific if you expect to get useful answers about Jet/ACE (as opposed to nice advice about developing database apps with .NET). – David-W-Fenton Jan 08 '10 at 03:56
  • Below you also confuse the issue by saying "it is a small application focusing Report generation." Do you mean automatic Access reports, or doing reporting through technologies available in .NET? – David-W-Fenton Jan 08 '10 at 04:04

6 Answers6

13

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.

Doc Brown
  • 19,739
  • 7
  • 52
  • 88
  • +1 For Version Control...I started out as a single dev in this type of environment, and implementing source control was one of the best things I could have done. In addition to allowing for rollbacks and tracking changes, knowing how to use source control is critical if you plan to move past the "single dev" environment.. – Pete H. Jan 05 '10 at 13:08
  • 3
    He already said he's a .Net developer, he doesn't need advice on how to start developing in .Net, he just wants advice on how to handle his data which, at the moment, is in MS Access. – Chris Latta Jan 05 '10 at 13:12
  • .NET is a broad spectrum of technologies. I tried to make some suggestions which .NET parts I would choose under the given constraints. – Doc Brown Jan 05 '10 at 13:21
  • WPF has a huge learning curve, but I'd hardly classify it as bleeding edge; It's been around since 2006. – Richard Szalay Jan 05 '10 at 13:27
  • @Richard: ok, I have removed the term "bleeding edge" – Doc Brown Jan 05 '10 at 13:38
  • If you don't have an existing database i'd recommend that you create a n information model. This work should involve you and people from the business end so when you discuss features, functions and so forth, you're using the same terminology. A way to reduce misunderstandings. – magnus Jan 05 '10 at 13:52
  • Uh, so far as I know, ADO.NET can't do Jet/ACE, no? If so, can someone please provide a pointer on that? – David-W-Fenton Jan 06 '10 at 04:04
  • 1
    Nice answer here, basically it is a small application focusing Report generation. What i want to know is, is it necessary to use any design pattern on this? – Dhanapal Jan 06 '10 at 04:22
  • @David W. Fenton: Surely ADO.NET can do Jet/ACE (see, for example, here http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbconnection%28VS.71%29.aspx or here http://visualbasic.about.com/od/learnvbnetandadonet/l/aa050303b.htm). However, AFAIK you don't have any modifying cursors/recordsets in ADO.NET (but for a Report generation tool one probably won't need it). – Doc Brown Jan 06 '10 at 06:41
  • @LittleBoy: I have added another hint according to 'reporting' to my original answer. However, I am not sure what you expect when you say "design patterns", I suspect you don't have the classical 'Gang of Four patterns' in mind (http://en.wikipedia.org/wiki/Design_pattern_%28computer_science%29), since IMHO this seems not to be related to your original question. – Doc Brown Jan 06 '10 at 07:07
  • @Doc Brown: "Surely ADO.NET can do Jet/ACE" (then citations of articles about OLEDB). So far as I know, ADO.NET != OLEDB. Classic ADO is a wrapper around OLEDB, but I don't know what ADO.NET is an abstraction of (I'm an Access programmer -- I use Jet/ACE natively, with not data-access layer except it's native DAO). Indeed, the second cite says "Only one product - SQL Server - is natively supported by an ADO .NET managed provider." That seems to indicate that using Jet/ACE via ADO.NET is like using Jet/ACE via classic ADO or via DAO -- you're using COM instead of native .NET technologies. – David-W-Fenton Jan 08 '10 at 04:03
  • @LittleBoy: "it is a small application focusing Report generation". Uh, are you using Jet/ACE as a data store, or are you wanting to automate Access reports from .NET? The two are completely different operations, and your original question sounds entirely like a question about a Jet/ACE data store. But now you've thrown the reporting in the mix. Which is it? How can people answer your question helpfully if you can't formulate it clearly? Or do you mean that you're using .NET technologies for the reporting? If so, why is it relevant to a question about a Jet/ACE datastore? – David-W-Fenton Jan 08 '10 at 04:05
  • @David: My citations were on the *System.Data.OleDb* part of the System.Data assembly of the .NET framework. Surely this uses classic ADO or OleDb internally, but for a .NET programmer, it provides you with the ADO.NET programming interface, with all of its pros and cons. In a .NET program, you can use this, or you can use classic ADO (via COM). Classic ADO has a very different API as ADO.NET with its pros and cons, too (as I already said above). – Doc Brown Jan 08 '10 at 18:11
6

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

    • 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. *

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

  5. 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

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

Jazza
  • 1,042
  • 1
  • 9
  • 22
  • "MS Access does not scale very well and is only suitable for a small number of users": can you qualify "small number of users" here? – David-W-Fenton Jan 06 '10 at 04:05
  • 1
    "MS Access does not support stored procedures": Access/Jet/ACE has no *procedural* code, but it has its saved QueryDefs, which for SELECT statements are like VIEWS, and for DML SQL like stored procedures (without any procedural code). So the statement that you have to do all your SQL inline in code is COMPLETELY FALSE. – David-W-Fenton Jan 06 '10 at 04:06
  • "Keep data access code out of your forms as much as possible": what do you mean by "data access code"? The form is the natural place for code specific to that particular form's function. Obviously, if you need the same code in more than one form, you generalize it and put it in a standalone module. All that said, seems to me you've misread the question (though it does say "Access" and not "Jet/ACE"), since the question is about using Jet/ACE as a data store, not as an application development environment. Many of your comments apply only to an Access app, and not at all to a Jet/ACE data store. – David-W-Fenton Jan 06 '10 at 04:10
  • Have edited answer to provide more details – Jazza Jan 06 '10 at 11:39
  • @David: when you asked for "small number of users", why did you not post this link http://stackoverflow.com/questions/763888/is-ms-access-jet-suitable-for-multiuser-access/766181#766181 to this already given answer of yours? – Doc Brown Jan 06 '10 at 12:19
  • Why not post a link to an answer of mine? Because I don't remember all my answers, nor do I find it easy to locate them with the SO search facilities even when I *do* remember them! – David-W-Fenton Jan 08 '10 at 04:07
  • In regard to 100 as a max for a reporting app, I know people who've engineered read/write Access apps for user populations greater than 100 with a Jet/ACE back end. It's not easy, but it's doable if you know what you're doing and the schema and business rules and type of app make it feasible. – David-W-Fenton Jan 08 '10 at 04:08
  • @Jazza: there's an asterisk at the end of your answer. Did you mean to provide a footnote explanation of something? – David-W-Fenton Jan 08 '10 at 04:10
  • @David: No, it was an attempt to italicise some text to distinguish an edit – Jazza Jan 08 '10 at 14:35
4

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.
STW
  • 44,917
  • 17
  • 105
  • 161
0

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#.

James
  • 80,725
  • 18
  • 167
  • 237
0

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.

Svish
  • 152,914
  • 173
  • 462
  • 620
  • Hibernate would not be my first choice if the task is using an existing DB, as the OP requires. It may work, but is it really worth the effort? – Doc Brown Jan 05 '10 at 15:38
  • Well, I don't have much experience with NHibernate, but it looks like a good database abstraction tool/ORM. And if you can use Linq for your queries(which I have read that should be possible), I would say it is definitely a good thing. My personal preference though, I think would be to use Linq to Entities, which from the latest PDC seems to be quite smooth in use. (http://microsoftpdc.com/Sessions/FT10) – Svish Jan 05 '10 at 17:02
  • I meant "is it really worth the effort when you have an already existing DB that was not specially designed for NHibernate"? By the way, the OP added a comment that he just wants to write a Report generation tool. – Doc Brown Jan 06 '10 at 07:11
  • I thought kind of the clue with NHibernate and other ORM tools like Entity Framework was exactly that you *don't* have to have a database that is specially designed for it. Anyways, it was just a suggestion :) I am currently working on a project where we are using Linq to SQL, and the database wasn't really designed with that in mind. Still, I don't think I would want to drop Linq to SQL even if you paid me for it. For reporting we use Microsoft Reporting Services. – Svish Jan 06 '10 at 08:00
  • @Svish: found an answer here that supports you, maybe of interest: http://stackoverflow.com/questions/454249/would-you-use-nhibernate-for-a-project-with-a-legacy-database-which-is-partly-ou – Doc Brown Jan 06 '10 at 13:41
0

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.

dionysus55
  • 149
  • 1
  • 8