Forgot your password?
typodupeerror

How Do I Make Sense of Microsoft Access? 100

Posted by Cliff
from the access-denied dept.
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?"
This discussion has been archived. No new comments can be posted.

How Do I Make Sense of Microsoft Access?

Comments Filter:
  • by Anonymous Coward on Friday August 11, 2006 @10:34PM (#15892813)
    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...
  • by MikeB0Lton (962403) on Friday August 11, 2006 @11:04PM (#15892886)

    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.

  • Re:IMHO (Score:3, Informative)

    by imemyself (757318) on Friday August 11, 2006 @11:07PM (#15892896)
    And if not LAMP, then atleast something based on MSSQL. Access is not meant to handle things with that many tables and presumably quite a bit of (important) data. Not to mention security and reliability.
  • by THC1972 (989636) * <tania.clucasNO@SPAMgmail.com> on Friday August 11, 2006 @11:08PM (#15892903)
    but it does the job.

    On the menu, pull up Tools, Analyzer, Documenter, and pick your criteria. Access is clumsy and not secure, but it's also what most organizations have. That doesn't keep the rest of us from attempting to subvert from within...

    To consolidate the duplicate tables, build a query that replicates grep and/or another that replicates diff, and have fun from there. Somehow I'm sure that you know how to do this.

    Even though I prefer to work with other platforms and venues, my Access skills have managed to keep me employed and the cats fed while I decide what to do next with my life.
  • TOAD, definitely. (Score:4, Informative)

    by Kadin2048 (468275) <slashdot.kadin@xoxFREEBSDy.net minus bsd> on Friday August 11, 2006 @11:23PM (#15892939) Homepage Journal
    I second TOAD, having used it on a daily basis with an absolutely massive (hundreds of tables, many millions of lines) Oracle-based system, and it's been the best way I've found of making sense of things. The "Schema Browser" function I find particularly helpful when I know vaguely what column I'm looking for, but not what table it's in. It's replaced a lot of the old "cheat sheets" I used to have pinned to every flat vertical surface in my cube.

    I've heard it's a fairly expensive piece of software, but thankfully I don't pay for it. It might be tough to get your PHB to spring for it, if that's actually the case...but I've yet to use or even hear of a better way to work with really complex DB systems.
  • Learn Visual Basic (Score:3, Informative)

    by cyranoVR (518628) * <cyranoVR AT gmail DOT com> on Friday August 11, 2006 @11:35PM (#15892989) Homepage Journal
    There are a ton of commercial utilities and add-ons for MS Access (check out Access Advisor at your local bookstore), but most of those are just VB apps or ActiveX controls that just do what you could do yourself with a little Visual Basic. Once you have got the basics down from some online tutorials, Access Cookbook [oreilly.com] by Kurt Getz is a great investment.

    MS Access has a large community online, especially comp.databases.ms-access [oreilly.com]. Google is your friend - just about everything you'll ever want to do has already been done and has VB code examples online.

    Here is a thread that has code demonstrating how to dump the contents of an Access database as DDL into text files:

    comp.databases.ms-access: Exporting jet table metadata as text? [google.com]

    PS - If you are impatient with the limitations of VBA (aka "VB Classic"), there are Microsoft Office interop libraries that will let you automate Access Databases in .NET.
  • by fredrated (639554) on Saturday August 12, 2006 @02:16AM (#15893414) Journal
    I've had good success using Access as an interface to a Postgres database. The web server runs Postgres on a unix box to service our web apps and administer the database, Business admin. functions are implemented in Access through an ODBC connection. There were a couple of tricks to get Access and Postgres to agree on a few things, but after that it has been smooth sailing.
  • Re:Tora, not Toad. (Score:2, Informative)

    by CCW (125740) on Saturday August 12, 2006 @04:15AM (#15893603)
    Try Tora. It's a great database admin tool. There's a couple things Toad will do that Tora won't, but I've found it to be a nice substitute.

    Free
    Cross Platform
    Open Source
    supports Multiple Databases

    Really quite a nice application.
  • by cruachan (113813) on Saturday August 12, 2006 @05:30AM (#15893682)
    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 :-).

    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 :-). 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.

    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
  • Visual Studio (Score:2, Informative)

    by exKingZog (847868) on Saturday August 12, 2006 @06:53AM (#15893766) Homepage
    If your bosses will shell out for it, then Visual Studio 2005's Integration Services can take data from any number of MDBs, Excel files, text files, databases, etc and transform them into whatever you want. We recently used it to move a creaking Access DB (1 table, 165 columns!!!) into a (temporary, and slightly more normalised) SQL Server schema - we had cursors running to generate keys, data cleansing procedures plugged in, and best of all it ran at the click of a button, so we could test it very easily with copies of the data before the final rollout.
  • by MikeB0Lton (962403) on Saturday August 12, 2006 @07:21AM (#15893783)

    I agree with your comments about Access not being good for a backend under any decent load. The problem ultimately is one of scalability. The only way up is to use the upsizing wizard and place the data into your SQL server. This requires you convert to an Access Project (ADP), or to rewrite your app. Why VB.NET cannot convert your VBA into an executable is beyond me. Also, Access 2003 does not work right with SQL Server Express. You have to have MSDE/SQL2000 to fully work. Unacceptable in my opinion.

    Managers will indeed get themselves in a hole when they start using this for mission critical work, and they never see it coming. Business grows, and so surely the database can store more data, right? BZZZZZZZZ wrong.

  • by MythoBeast (54294) on Saturday August 12, 2006 @04:44PM (#15895524) Homepage Journal
    I'll agree with most of what Cruchan has to say, but want to add in a perspective that I've found important. Almost any business case that is capable of making good use of Access's capabilities is also capable of outgrowing Access's utility. Let me be more specific.

    Access's primary strength is that it allows a novice but intelligent user to store data in a database and create views with which they can examine, alter, and add to that data. This makes it very attractive for many small business owners to create methods of keeping track of customers, sales, products, whatever. If your business stays small, then you've saved yourself a bit of money and solved a paperwork headache.

    If your business (venture, hobby, whatever) grows, then you will invariably run into Access's limitations. It's very easy to use, but a database program of any complexity will eventually run into programming errors that result in data corruption. They aren't everywhere, but when you run into one you're pretty well hosed. Microsoft may have fixed all of these kinds of bugs between their 2000 and 2003 release - I've somewhat gotten out of that kind of business - but I somehow doubt that Microsoft has changed its philosophy that much.

    When you do run into that kind of problem, you have two choices. Keep a second paper trail of all of your changes so that you can fix the database when it hits that bug, or pay someone to migrate you to something more reliable. You would think that there would be an option to pay someone to fix your Access implementation, but by the time someone is willing to shell out money for this kind of thing you can pretty well guarantee that the flaws are in Microsoft's software, not in anything the user has done with it.

    So on to the user's question - what do you do when you hit that tree and fly through your windshield? You have to remember that Accesss is a front end database management tool, not an actual database. What you need to replace isn't the actual storage of the information, but the routines that alter and display it. LAMP is an entirely viable idiom for this kind of change, even in a Windows environment. I run a LAMP environment on my laptop so I can develop and show off my web site designs while not online, and it's very reliable. Additionally, it allows remote access of information from many locations, although it takes a bit of skill to write something that can be altered from many locations at the same time.

    I don't suggest WISP simply because any further growing will either lock you into Microsoft tools (many of which are highly suspect) or result in tedious and expensive searching for obscure features that allow you to attach other people's tools to the Microsoft Architecture.

    Moving away from Access involves two things - migrating the data out of that .mdb file and re-creating the data interface in HTML forms. The first is easy via an SQL dump, which Access supports. The second is more difficult because what you're essentially doing is Industrial Engineering.

    When you're talking about a 200 table database, you can quite readily start with the Database Documenter. It'll spit out a bunch of stuff that'll tell you what the formats of the tables are, but won't tell you how they're hooked together or what they're used for (unless the person who made the database was very, very professional). Then you need to have someone show you how the database is used. From that you can figure out what the inputs of the data process are, where the information is put, what is done with it, and what form and place it has to exist for it to be viewed and outputted.

    From that you can generate charts that show how the various tables are connected, identify what the rules are (all cars shalt have a color), and get an idea for process flow.

    No, this isn't easy. For really big databases it can take months. I was on a project where we were attempting to reconcile seven regional AAA databases into a central database and the entire project collapsed for the inability to get someone to tell us how the data was used. There just isn't enough information in a database dump to determine this.

    Best of luck.

  • by dtfinch (661405) * on Saturday August 12, 2006 @04:56PM (#15895565) Journal
    Until you get that POS replaced or moved to a real database, you can automate repair with a tool called jetcomp. It can repair many databases that Access's Compact and Repair and DAO's CompactDatabase method can't, and can be automated from the command line.

    I went so far as to write a script to detect when the database has been corrupted by attempting to open it and checking the error message if it can't, and automatically backup and repair it using jetcomp. Our corruption problem finally went away when I found a system that had a slightly bad network connection and fixed it. Network problems become more apparent if you specify a larger packet size when pinging, like 400 bytes or so. We went from almost daily corruption to no corruption in over a month.
  • by wrfelts (950027) on Saturday August 12, 2006 @06:38PM (#15895927)
    There are some tools available. I always wrote my own. Get one of the tools listed by other commenters, though. It will be much quicker.

    If the former programmers were "macro happy" it is very difficult to decode, but with time, a lot of paper to map it out, and even more patients, you can get it done.
    1. The main thing is to get into the head of the previous programmers and find out why every table, macro, or other object was there. Don't make assumptions, it could bite you bad.
    2. Also, make backups before and after major changes and keep dated historical copies. A large Access database can become very unweildy and overwrite code, tables, etc. as it grows. I have had the code to one form cross link to another with weird errors, as an example of some of Access craziness. (BTW, this has been the case through v.2003)
    3. If management is open to the idea, a gradual shift to SQL Express (or the older MSDE) is worth the pain. You can use the existing Access front end with a somewhat gradual migration. If this is an option, keep reading:
    4. Remember that Access will try to pull the entire dataset into memory from SQL Server if you build an Access query on SQL tables. So, work on creating SQL Server View, Stored Procedures, or User Defined Functions instead.
    5. Learn efficient SQL Server SP methodologies. It will help speed things up and use less memory.
    6. Once you have ALL the tables (that are needed) and as much of the logic as possible shifted to SQL Server. Start looking at a better user interface than Access. C# (or even VB.Net) is really good for building a solid business oriented front-end and works well with SQL server once you've got the hang of the interface. I recommend it highly. I also recommend using a .Net 2.0 flavor, as the 1.1 stuff is way too slow.

    Good luck with your project. If you need any help, reply to this message and we can talk.

    Randy

It is better to give than to lend, and it costs about the same.

Working...