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 PhrostyMcByte (589271) <phrosty@gmail.com> on Friday August 11, 2006 @11:02PM (#15892880) Homepage
    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.
  • by TheSpoom (715771) <slashdot@uberm00. n e t> on Saturday August 12, 2006 @12:27AM (#15893168) Homepage Journal
    Hell, don't use Access if it's going to be used by more than five users simultaneously. It's designed for occasional, personal, direct usage. Using it as a backend to a single-user application would be OK, as long as that application isn't used over the network, though there are probably some bettter solutions [sqlite.org] out there.

    NEVER, EVER, EVER use an Access database as a backend to a server. It's just asking for trouble.

    Unfortunately I get the feeling that a lot of these Access overusage problems stem from managers with just enough knowledge to be dangerous saying to themselves "hey, I've got a great working database right here," and storing their mission-critical data in it without knowing the consequences of that action down the road. I kinda wish that Microsoft would put a fairly prominent warning on Access that that's simply not what it's designed for... maybe when it's been used by more than a few users simultaneously it could pop up a warning to the next one.
  • Re:Tools (Score:4, Insightful)

    by Maserati (8679) on Saturday August 12, 2006 @12:51AM (#15893248) Homepage Journal
    And there never has been.

    Set the wayback machine to Access 1.0 and you'll find me working at a Software Etc. back in college. We got, and sold, 12 copies of Access 1.0. All twelve came back from customers complaining that the program was uselessly buggy.

    Wind forward a bit to Access 97. I'm a sysadmin for... well, a company using a lot of Filemaker 3 databases (my first task there was finishing the FMP 2->3 migration). We were looking to migrate off of Filemaker (it wasn't a Microsoft product; just don't ask me about that manager - I have no idea how he got out of there without being charged with embezzling for kickbacks from the consultant he was partnered with immediately prior to joining our company). One candidate to replace Filemaker for production databases with 50 users, 250,000+ records and 2 or 3 people running reports was Access 97 (the other two were CRM products, one from a company that later ran Superbowl ads and the other from a company later acquired by Nortel; and no, management didn't select the company that survived). Our lead FMP developer managed to stretch the Access97 evaluation out to a full hour before he deleted every table in his test implementation without recovery, undo, or prompt. End of evaluation.

    So I've seen two versions of Access in business situations. Microsoft usually gets things right in the third version. For Access, you have to start counting with Access 2000 - if that version was useful.

    How you handle it is to document the functionality and re-implement the application in something else.
  • by Anonymous Coward on Saturday August 12, 2006 @12:01PM (#15894468)
    I love how you say "Access isn't that bad" and then your first step is "Convert everything to SQL Server".
  • Re:Tools (Score:3, Insightful)

    by cruachan (113813) on Saturday August 12, 2006 @12:54PM (#15894705)
    Early access was indeed pretty crud. However Access 97 was a solid release that I still have clients running on even now. My milage varies because I never use Access as anything else put a front end to SQL Server or MSDE, and in these circumstances it's an excellent, reliable and solid system. Access should never be used as a database for all but the smallest of systems and personally I'd never trust it for anything else but single user, however to decry it because it doesn't work reliably in such circumstances is unfair - MSDE is free and works well for up to 5 simultaneous users, which should tell you something.

    Your comment about starting counting from Access 2000 is actually quite perverse as this release was generally agreed to be considerably more buggy than 97 and best avoided - consequently many users didn't upgrade until Office XP. Access 2002, and particularly 2003 are good releases, although again myself I always use them as a front end to SQL Server.

    But underline the point, using Access as a database and front end system, and using it as a front end system to MSDE or SQL Server are two completely different things and should be treated as such (indeed that there is the option of the adp file format in Access 2000+ specifically designed to work with SQL Server is a strong clue :-). Don't dismiss Access as a useful business system if you've only seen the former.

Always draw your curves, then plot your reading.

Working...