Hosting an Access '97 Application. Good practices.

[Maqu1na]

Hi everybody.

We have an Access '97 database on a server. It is used to print and save orders, so we have one form to register them -sorry for my poor english-, another form used to register payments and another one designed for displaying reports.

My question is: how bad it could be?

All users logs like admins, we use environ to determine users and then open one form or another based on that, users have only the run time version of Access installed, so they can't see the other forms or the database tables.

We have a folder on the server, where everyone opens the database.mdb file in order to work.

I don't see clearly enough a three layer division, like UI, logic and database layers, because it is everything on the same place, with a lot of vba code working around.

Thank you very much for your time.

 

[JoeAtWork]

The one most obvious improvement would be to seperate the user interface and data into front-end/back-end MDB files (all tables are on the back-end file, everything else - forms, code, etc., are on the front-end, the front-end has linked tables to the back-end). Everyone should have their own copy of the front-end.

As for "three layer division", or what I would call "n-tier architecture", that generally can't be done in Access. The UI and business logic especially tends to be meshed together.

Whether that's "bad" or not completely depends on your requirements. If your requirements absolutely require an n-tier architecture (for scaleability, for instance) then Access is not the solution. Just be aware that a well designed n-tier solution is not cheap, it will cost you time and money.

 

[Maqu1na]

Thanks for your answer, Joe, im pretty much on the scenario described by you on a post, where a power user starts a solution, and that solution keeps on growing...

I got the idea, but with some doubts.

'Everyone should have their own copy of the front-end'
It's a good solution having one shared front-end, so when we add something to a form for example everybody will 'see' the changes right away, without having to upgrade their own versions of the application? Or we need a copy of the front end on every machine?

What is the main advantage of this improvement? Security on the 'data' side? Or something else i am missing? What about slowness, can be an issue? Is Access really multiuser capable?

Thank you very much for your time!

 

[JoeAtWork]

so when we add something to a form for example everybody will 'see' the changes right away

The solution I have seen most often to that is for the short-cut that people use to open the application to point to a batch file rather than the MDB front-end itself. The batch file is a little script that first copies the front-end MDB from a network location to the user's computer, and then starts Access with the copied MDB file. That way, when you have a new version, you just need to copy it to the central network location, and everyone will have it the next time they start the application.

There are a number of reasons for everyone to have their own front-end, and I think I've forgotten some, but here are the ones I know:
1. Shared front-ends are more likely to become corrupt
2. Makes it simpler to use temporary tables, which can be very handy for certain tasks

Is Access really multiuser capable?

Yes, that's not a problem if you follow basic good design principles. But it all depends on how many users you are talking about. 10 to 15 shouldn't be a problem, but 100 would be way past what I would be comfortable with (although I've seen other posters claim to have this large a user base or even more).

In the end, your choice will mostly depend on your requirements and resources. You will never be able to build as scaleable an application with Access as you could with .NET + SQL Server for example, but a good Access design may be "good enough" for your needs and will almost certainly be the cheaper solution.

A hybrid solution you might consider is upgrading the back-end database to SQL Server while keeping the front-end in Access.

 

[DavidSoussan]

Just one point here: never distribute or use an MDB in production, always create an MDE for end users to use. This is both a basic security principle and an operational advantage: the MDE is always named the same but each version of the MDB is named with a version number to control development.

 

[MazeWorX]

optionally you can use a db as oppossed to batch files to check version numbers and do any updates to an mde front end then start your app

M

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work

 

[Maqu1na]

JoeAtWork, DavidSoussan, MazeWorX: thank you guys for everything. We are implementing these changes on the next few days, very helpful advice!