The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

MS Access as a Development Tool

Forking this thread (http://discuss.joelonsoftware.com/default.asp?design.4.315310.21) to discuss the use of MS Access to develop software applications.

For the most part I agree with Albert that Access is a great tool for developing business apps for individuals and small groups. There are lots of ways to save time creating data-driven forms, and Access gives you a lot for "free" in terms of CRUD. My issues with Access to develop apps are:

(1) Lack of "change all" capability. If you rename a database object you have remember to manually change all its references in code (the VBA editor does do change all). I believe there all some 3rd party tools that will change all the code references for you, but why isn't this built-in to Access?

(2) Lack of version control. Even if you split your presentation (forms, reports) from your data (tables). Since all the objects are stored in a single database it's hard to have any kind of version control on them.

(3) Difficult to maintain once in production. By this I mean that once you have a database app that people are using, it's hard to make changes to it. You have to be able to open the database for exclusive access in order to change things, which you can't do if someone's using it. While open exclusively you lock out others from using the app. I usually create a copy of the code database and make my changes there then copy over the production db, but again, you can't do this when people are using the database. And, if you link to the datatables by a different drive mapping (I haven't been able to link tables by UNC and have access preserve the path :() then you still have to change the database once copied to correct the link paths.

Admittedly, I haven't used MDEs for deploying Access solutions, which may address some of these issues.

My favorite use of Access is to link to other data sources (Oracle, SQLServer) for reporting and compiling data for Excel pivot tables. I also like to use Access as a quick-and-dirty testing tool for DB apps written in other languages to verify the app writes data to the data source correctly. However, this use of Access has made me lazy with regard to writing SQL; I haven't written an SQL statement by hand for about 8 years. I use Access to design my query then copy/paste the generated SQL (for better or worse).

What have been others experiences?

Regards,
Former COBOL Programmer Send private email
Tuesday, March 07, 2006
 
 
It's a very nice reporting tool, and the GUI data entry front end is OK, but I would never use the Jet Engine for production work.
Steve Hirsch Send private email
Tuesday, March 07, 2006
 
 
Add to that the lack of n-tier separation and the abysmal error handling and you have the reasons we don't do much with Access anymore.  Every time we go back to VBA for something there are a round of groans and curses at the barbarism of it.

Also, the barriers to entry are so low that you rarely see a well-designed Access database.  Not that they don't exist, but I just don't see many from our consulting clients.  Of course, if our consulting clients had well-designed databases, they wouldn't have fired the guy who built it and called us. Maybe this serves to bias us against it too.
BrianB Send private email
Tuesday, March 07, 2006
 
 
You can integrate ms access in visual sourcesafe with Office developer edition.
lonely consultant
Tuesday, March 07, 2006
 
 
You can also separate UI from Data by having 2 Access Databases. 1 for UI and 1 for Data.

I've used this strategy before and it has worked quite well, especially in small groups where I can put the data on a network drive and have the UI on a number of desktops linking in to the data.
Douglas Boyce Send private email
Tuesday, March 07, 2006
 
 
I have been doing Access development since Access 2.0 through a side consulting job.

Usually my jobs come about when someone hacks togther an awfully desgined database and find it doesn't work. I come to the rescue using the bad database as a requirements jump start and quickly build them a properly designed database.

Overall it is a very productive development environment. I'm happy and my customers are very happy.
DJ
Tuesday, March 07, 2006
 
 
I've made a nice living working almost exclusively as an Access Developer for the last 5 years.

Access has its limitations, but I can't knock it! It's been very kind to me....

However, I will say this...the perfect incarnation of Access (in my mind) would be to see a company (Microsoft sure isn't going to do it) build a unique IDE that mimics the database window of Access, has the SAME number and kind of diverse controls contained within Visual Studio, and can connect you to a back end that has a table size of 50-100 MB - something like Jet on steroids..., and a language that users either VB.Net or VBA.

A product like that would give Visual Studio a run for its money......
Brice Richard Send private email
Tuesday, March 07, 2006
 
 
Former COBOL Programmer,

The problem is that you have all of your users using the same front end. The point of splitting into an FE and a BE is that you put one copy of the FE on each user's computer (or make one copy per user on the server, if you're using TS).

Then you can make all the changes you want to the FE and replace it at will, using a batch file. (Let me know if you want to see a sample of something like that.)

Jeremy
Jeremy Wallace Send private email
Tuesday, March 07, 2006
 
 
Brice, what's the difference between what you're dreaming of, and Access connected to Oracle or SQL Server via ODBC?
Steve Hirsch Send private email
Tuesday, March 07, 2006
 
 
The Jet Engine is fine for single-user and small workgroups, say 5 users. Most problems I have seen are related to server caching of updates, but there are known fixes to this problem.

Best practices (as mentioned), is to have a pure data database, and another database for the forms, reports, and queries.

I prefer to use Jet only for the back end, and VB or C++ Builder for the front end. That gives me more control and better error handling.
MBJ Send private email
Tuesday, March 07, 2006
 
 
I started out programming Access and VBA, have done it for awhile.

The real issue IMHO, is that anything you are spending legitamate development resources on in my mind is pretty important. So if it's important, why are you using something that just isn't scalable?

There are large mdb's out there, running multiple users. Just because you can't see cracks in the dam doesn't mean they are not there.

My current opinion is if the business need is too great for excel, skip Access and go straight to something more scalable.

Just my 2 cents
D in PHX Send private email
Tuesday, March 07, 2006
 
 
MS Access as a development tool: It's the right tool for many jobs, the wrong one for many others. Choose wisely.

You would use a hammer to bang in a nail and a saw to cut wood (I hope). Is a hammer better than  a saw?

For small work groups & small business Access is often the only afordable solution and often the wisest choice.

It is a far cheaper solution for the initial deployment of a product and the on going costs of modifications are also very low. This is very important.

Small businesses grow so they need to constantly change these custom applications. Access provides a way to do this cheaply & quickly.
Kim
Tuesday, March 07, 2006
 
 
>(1) Lack of "change all" capability. If you rename a database object you have remember to manually change all its references in code

Hum, does any IDE system have that ability? It turns out that that ms-access will do this for you, but you have to enable track-name autocorrect.  This feature does not work for code..since

strF = “frmCustomres”

forms(strF)!LastName = bla bal bal..

How can the above reference be resolved unless the code is run? If you rename your form, that code can’t possibly know to change also.

So, dependences are managed by ms-access, but for most part this feature is not much use since it can’t fix your code anyway…

You can also right click on any object (form, report, query) etc. and view dependences (this was a new feature for a2003 --- very nice)). However, you have to turn on track-name autocorrect, and this feature is really something better left off during development (so, I’ll often view dependencies then turn off auto-correct). As mentioned, this feature does not work for code.

>2) Lack of version control. Even if you split your presentation (forms, reports) from your data (tables). Since all the objects are stored in a single database it's hard to have any kind of version control on them.

You can use VSS 6.0. All forms, code modules, and even  your sql quires can be checked in, and checked out. The fact that you can actually check in, and out the sql queries is rather nice. What IDE are you using that lets you check in, and out your sql ?  To me, source code control with ms-access is really nice, since it works with your sql queries also!! (one could argue that VSS is the weak spot here!).

>You have to be able to open the database for exclusive access in order to change things

You mean the data part? I can’t imagine that very many systems allow table structures to be modified when users are live. (very few systems really allow this) – anyone…please correct me on this one? The ONLY system I ever used where I did this on a regular bases was pick (the raining data d3 system) – and that was adding new fields…not removing fields…

However, where jet falls short is there is no script generation tools to create DDL for the sql. I often deploy software updates to remote clients, and that software modifies the table structures in my startup code (the first person in will make the modes via my startup code – after that..they don’t need to make the changes!!). So, I often deploy updates to users (and, the mde is wrapped in a inno script).

>usually create a copy of the code database and make my changes there then copy over the production db, but again, you can't do this when people are using the database.

In every platform I used, I never did share the .exe file. You CAN NOT run multiple users with the same front end (mde) and expect any reliability. So, you simply must distribute your .exe, or in the case of access, a mde to EACH user. I explain in details here why

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

If you give each user a mde (just like you now give each user a new updated .exe with whatever system you use now, then you have not a different problem then what you have with most development systems). The above article explains this issue in detail. Really, this is same as every other system I developed with.

>Add to that the lack of n-tier separation

Ok, Now, this is a big one!!! (BrianB gets a big prize here!!).

However, there is two answers to this problem!!!

#1 – well, lots of the procedures you write are in sql server…right? (golly, crappy t-sql is WHY people started writing middle teirs!!!).

#2 – with sql server 2005, we can write VB.net code, and up-load that code to sql server….and the server will execute that code!!!

Both of the above points actually still are in effect a 2 teir system, but that is fine for the most part. If you have to write a middle teir, then ms-access is not a good choice here at all (between .net assemblies on sql server, and t-sql...we have breathing room here!).

However, what % of applications do you write are 3 tier? (and, I can see a whole bunch readers here raising their hands ---yes..Albert!!...…all of our applications are 3 teir!!).

>the abysmal error handling

Yes….again true. Really, what we have is the same as VB6. It is weak, and I just think the whole error business in VB was done wrong wrong wrong!! However, it is what we have..and there is a LOT of VB code out there. You can work though this one, but it should have been better.

>SAME number and kind of diverse controls contained within Visual Studio, and can connect you to a back end that has a table size of 50-100 MB - something like Jet on steroids..., and a language that users either VB.Net or VBA.

Actaully, the net vb.net express and sql server 2005 epxress is REALLY amazing, and VERY close to what you ask for.. They well nearly come up with a data binding system that rivels ms-access.....so it is about as easy to build forms in vb.net (express) as it is in ms-access. So, data binding was something that ms-access was miles ahead of everything else. The new vb and sql express is very nice in this regards. So, the killer product is close (what is missing however is the rich forms event model, and many features of ms-acces forms...not to mention the report writer that ms-access has).

And, a few commented, when you link ms-access to sql server, then you do have a application that will scale to many users anyway, and you still have most of the access RAD benefits .

I think when a project grows beyond one person, perhaps two...after that...likey ms-acces becomes not the best choice...

Albert D. Kallal
Edmonton, Alberta Canada
Kallal@ msn.com
http://www.members.shaw.ca/AlbertKallal
Albert D. Kallal Send private email
Wednesday, March 08, 2006
 
 
I am the only developer for a division of my agency.  I create applications that our staff use to save time and manage their programs.  For my situation, MS Access is almost ideal. 

I use SQL Server as the back-end, so there Jet is almost not an issue.  (I do have some local tables for specific uses.)  Each user gets a .mde on their PCs.  A batch file which runs each time a user logs in each morning will automatically update the .mde if there is a new version.  It is easy as pie to release new versions and users never have to do a thing.  I could release a new version every day if I wanted.  But I don't.

I am able to make amazingly user-friendly, usable, feature-RICH applications in a very short time.  Years later I still have staff telling me how much they love application X or Y that they are still using.  I get to hear how much they are helped.  They even brag about my applications to staff at other agencies around our state who don't have my apps.  I bring this up for a point (not to brag myself): the purpose of writing software is to help people in some way.  I believe that the shape of software is not only determined by the developer's skills and other outside issues like budgets, but by the tool in which the developer has to work with.  Some things are just easier to do with some development tools compared to others.  I believe that a big part of my success is because I am able to use MS Access as my development tool. 

I also believe that there is a huge need in the world for my kind of development.  Shrink-wrapped packages have an important place in our world.  But so does software designed to meet the specific needs of a set of people.  It can make them very happy to be able to do what they need to do efficiently and in the long run can save an agency a lot of money.

I know that MS Access is not perfect, but in certain circumstances, like small development teams which service a single agency (even one that is spread out over several cities and is a very big agency/has a large user base), it can be ideal - especially if you can have SQL Server as the back-end and a way to auto-update using batch files. 

- JJ
Eugene, Oregon
JJ Bienn Send private email
Thursday, March 09, 2006
 
 
Brice it has been done before Access - it is called powerbuilder

Thursday, March 16, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz