Slashdot Mirror
How Do I Make Sense of Microsoft Access?
Anthony Boyd asks: "I have a pretty good tool-set for LAMP work, but as I get into Microsoft jobs, I've started to wonder if I'm working with the best tools. In particular, I'm exploring an 'out of control' Microsoft Access setup, which has about 200 tables in 30 .mdb files, including some duplicated/outdated tables. I'd like to print the properties of each table (with the comments for each field), print the table list for each database, get info on the field types & relationships, and so forth. What tools do you suggest for trying to grok a large Access mess?"
Look for the Database Documenter in the tools menu...it will print out basically all of the information that you say you're looking for...
"What tools do you suggest for trying to grok a large Access mess?"
Coffee. Lots of coffee, and ignorance. It's bliss, or so I've heard.
If you have that many access databases, you are probably misusing it. Import all that data into SQL Server, and start from there. There is no magic way to make sense of a database schema.. the best you'll do is grabbing a GUI that visualizes it.
I'm going to assume you've got Access 2003, because I can't remember what they changed from 2000 anymore :-)
The Microsoft Access Conversion Toolkit will give you some of the information you want, and can be used to query MDBs network-wide. If you just need to figure out the mess of a single chosen database, start using the built-in features of Access. Check the relationships and see if anything has been diagrammed out for you. If it has, then you have the ERD ready. If not, have fun figuring it out... Use the stuff in 'Tools->Analyze' to get more property and design information. Try right-clicking on a table/query/form/report and selecting 'Object Dependencies'. This will allow you to see what requires it to work, as well as what the object depends on. Lastly, I just start working through the code/macros (yuck). The object dependencies stuff won't check macros or VBA, so you have to check manually. Sometimes you'll find DAO/ADO code opening connections programmatically.
Best of luck to you! This will suck badly, in case you didn't figure that out already. Access provides an upsizing wizard that can help you upload your data to an MS-SQL server, but that will require you debug (ADP as frontend) / rewrite (VB.NET) the forms and stuff.
Despite what people say, Access does allow for security rights. However, it is not linked in any way to the machine or Active Directory. You use a modified shortcut to load the database with a security file. It works alright for most things, but there is no record-level security, and it sucks when you have 20 people signed in and you have to update the file. Also, supposedly there are cracks that break that security.
This all leads me to my next point for all who read: DO NOT USE ACCESS AS AN ENTERPRISE-LEVEL / MISSION CRITICAL DATABASE SUITE. Pay for a decent tool/programmer/dba/whatever if you really like your data. This application is just for personal / small-group data storage. There is a reason it comes with Office, and not SQL Server. Thank you.
Access is frequently abused in the way you describe. Companies that have Office licenses often restrict distribution of the Access component, even if they are otherwise entitled to it, because of such abuse. Access is a very handy tool for a quick-and-dirty database design, so people use it for that - a lot. Pretty soon, you have little information islands all over the place, designed by amatuer DBAs, and containing gobs of misplaced but critical business data. I believe it is all another Nefarious Microsoft Plot (NGP) because when you switch to the solution for cleaning it all up - SQL server - your need for the software is so severe that you won't kick about the price, and expectations for performance are so low that SQL server easily passes muster. Of course, that's just the snide opinion of Yet Another Microsoft Detractor. 8)
"Even if you are on the right track, you'll get run over if you just sit there" - Will Rogers
Fdisk, Format and reinstall. Next time try the SQL desktop engine or Oracle.
Access has it's place. Ignore the OS zealots here, in the real world Access is a viable solution in many business contexts and there is no direct OS equivalent that comes anywhere close, besides if you try to convert it all to LAMP you'll most probably go out of business in the time it will take you :-).
:-). Both of these are time-consuming to replace by a browser interface: the Forms will need extensive use of AJAX to reproduce the immediate responsive feel of an Access application, and Reports can take a lot of coding and even then reproducing pagenation and so forth can be problematic.
OK, firstly you need to convert the data from Access to SQL Server. This is essential: Access is really an excellent front-end system, but it's data handling sucks big time in a mult-user situation. The upsizing wizards included with it do a pretty good job, although you'll undoutably want to tweak manually. Upsizing then reviewing the database and adding relational integrity and other database rules is an essential first step. If you can't afford SQL Server, which is probably on of MS's best products, the MSDE will do if you don't have too many users. It's also a little known fact that Access will run as a front end to Postgres - although I've never tried this myself if you Google for it there's quite a few resources out there.
Having upsized the database you then have two choices. Review and modify the upsized MDB front-end, or create a new ADP project and convert. ADP's have some advantages, but you have to convert manually and rewrite the data access code. This does take time (although a suprising amount can be cut and pasted from between MDB and ADP). The choice here is heavily influence by how the old database has been written - I've seen some Access applications which are practically VBA applications and need to be rewritten susbtantially to use SQL Server even if left as MDBs, whereas others hardly need any changes at all. If you are picking up the application to support yourself and can afford the time the ADP approach is probably prefable as you'll get to know the code and iron out any junk.
You should indeed consider what use you can make of LAMP (or more accuratly WISP). Access's strengths lie in it's ability to support detailed responsive Forms for data entry and most particularly it's Reports where complex output can be generated remarkably quickly - generally it's RAD abilities blow LAMP and similar away for anything but a simpler application (and Ruby on Rails, that includes you
However in a business context it's quite usual for there to be a core group of users who are responsible for data entry and 'expert' use of the system, and a wider group of users who need just read-only access or some very simple data entry, generally for a limited number of screens. If this is the case it's a viable strategy to replace Access by a browser interface for these users. PHP runs happily on a windows server so all your LAMP skills can be applied quite readily. THE major advantage of replacing Access for the casual users is that you then no longer need to deploy Access, which will save you both licence money and support time.
If at a later date you have the time and motivation to convert more of the core user functionality from Access to browser then you can do that. A viable strategy is to convert the Forms but leave the complex reports in a Access as a 'reporting suite'. In many business setups it's quite common to find an 'expert user' who is capable of creating bespoke reports in Access. Handled correctly these people can be a valuable asset - generally I create an 'Adhoc' or 'Scratch' Access application for them (mdb is strongly preferred in this case so that objects are not created on the server) which they can use to generate bespoke reports. The core functionality is placed in a separate Access application which they do not modify.
Take-home message is to recognise that all these technologies have strengths and weaknesses and play