Beta
×

Welcome to the Slashdot Beta site -- learn more here. Use the link in the footer or click here to return to the Classic version of Slashdot.

Thank you!

Before you choose to head back to the Classic look of the site, we'd appreciate it if you share your thoughts on the Beta; your feedback is what drives our ongoing development.

Beta is different and we value you taking the time to try it out. Please take a look at the changes we've made in Beta and  learn more about it. Thanks for reading, and for making the site better!

How Do I Make Sense of Microsoft Access?

Cliff posted more than 8 years ago | from the access-denied dept.

100

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?"

cancel ×

100 comments

Sorry! There are no comments related to the filter you selected.

Access Has You Covered (5, Informative)

Anonymous Coward | more than 8 years ago | (#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...

Tools (5, Funny)

Ricken (797341) | more than 8 years ago | (#15892817)

"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.

Re:Tools (2, Funny)

kfg (145172) | more than 8 years ago | (#15892824)

Coffee. Lots of coffee. . .

With two lumps of LSD.

KFG

Re:Tools (3, Funny)

bcat24 (914105) | more than 8 years ago | (#15892969)

Coffee. Lots of coffee. . .

With two lumps of LSD.
I prefer LSD with two spoons of coffee.

Re:Tools (2, Funny)

Mistshadow2k4 (748958) | more than 8 years ago | (#15892842)

No, no, no. You need a case of beer and a large bottle of hard liquor (bourbon, rum, etc.). Coffee makes you realize how little sense Access makes.

Re:Tools (1)

shadowbearer (554144) | more than 8 years ago | (#15893166)


  Apparently there's not enough coffee being served to the MS developers, then.

SB

Re:Tools (3, Insightful)

Maserati (8679) | more than 8 years ago | (#15893248)

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.

Re:Tools (2, Insightful)

cruachan (113813) | more than 8 years ago | (#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.

Re:Tools (2, Interesting)

afidel (530433) | more than 8 years ago | (#15894857)

I don't have mod points today so I'll just respond. What you have said is VERY correct. Access isn't a horrible design tool or presentation layer, it IS a horrible database. Developers who base commercial products off it should be shot and those who base internal projects off it should be educated. Anyone who's doing real development work probably already has a license to redistribute MSDE, and internal developers don't need one. I have a friend who's a programmer/DBA and probably 90% of his workload is taking messes made with Access and converting them to Access frontends to SQL/MSDE.

Horrible database, yes... (1)

leonbrooks (8043) | more than 8 years ago | (#15896256)

...have you had it totally, irreversably trash a database file yet?

The rest of it looks relatively simple and easy-to-use but is ever-so-prone to making exactly the kinds of spaghetti-farms which the OP is asking about.

AFAICT, those spaghetti-farms are a lock-in policy done with more stealth than is usual for MS. It's likely that the cheapest, most effective answer for anything beyond an instant fix is the total rewrite (in something standard and comprehensible).

Re:Tools (1)

Zebra_X (13249) | more than 8 years ago | (#15909478)

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.

Access isn't the greatest thing on the planet. However, if you unreasonably expect your database to hold your hand while managing tables... I have some bad news for you!

DELETE FROM MY_TABLE;

DROP TABLE MY_TABLE;

Where did my table go! Johnny your table has gone to a better place, a happier place.

Re:Tools (1)

WgT2 (591074) | more than 8 years ago | (#15892905)

Yes, coffee!

And pencil and paper and very big pink eraser!

Re:Tools (3, Funny)

Neoncow (802085) | more than 8 years ago | (#15893056)

Yes, coffee!

And pencil and paper and very big pink eraser!
WTF? This tastes gross! Now I know why you all hate Access so much.

Re:Tools (1)

WgT2 (591074) | more than 8 years ago | (#15903367)

Ssshhhhhhhh!

If you make too much of fuss, everyone will know!

Switch to Excel (0)

Anonymous Coward | more than 8 years ago | (#15893611)

I find that converting the DB to an Excel spreadsheet works wonders for my understanding of the DB.
.
.
.
I know this is posted as a joke, but I am serious.
.
.
.
See our company is laying off 10-15% of the workforce. And who do we NOT layoff? Yes, the DB manager who costs us $8 million in budget overruns (this is while we are cost cutting), and forced the most experienced guy in the DB group to quit. Yeah, she (the DB manager) stays. 100s of entries just not there in the DB. When I have a senior management presentation is have to generate 100s entries myself. They are generating the weekly reports by hand. I have no clue what is on my docket, because the DB is just F'ing wrong. So, when I need info my first act is to port it to something her hands haven't touched, and I can see what is missing. After all if you can't run reports it might as well be a spreadsheet.
.
Yeah, her job is safe.
.
Wonder why the stock is at $17.41 today. Yet we made ~$9 billion in profit in 2005.

Re:Tools (2)

r00t (33219) | more than 8 years ago | (#15893681)

You also need a silver cross and some Holy Water.

Re:Tools - Pan Galactic Gargle Blaster (1)

fahrbot-bot (874524) | more than 8 years ago | (#15917195)

How Do I Make Sense of Microsoft Access? That about covers it.

Better question: (0)

Anonymous Coward | more than 8 years ago | (#15892847)

How I mine for fish?!? [vgcats.com]

Sorry. For some reason after reading the title I couldn't think of anything else.

IMHO (3, Interesting)

ManoSinistra (983539) | more than 8 years ago | (#15892849)

I have also done extensive work with LAMP and Microsoft (Access in particular). I actually started out with ASP and Windows before I learned all the LAMP stuff. IMHO, Access databases are by no means secure and what's more, they're very clumsy animals. It might be in your clients best interests to convert to LAMP/Linux, etc.

Re:IMHO (1)

drewzhrodague (606182) | more than 8 years ago | (#15892856)

It might be in your clients best interests to convert to LAMP/Linux, etc.

The 'L' in 'LAMP' stands for Linux, (GNU/)Linux, Apache, MySQL, PHP.

IMSO (0)

Anonymous Coward | more than 8 years ago | (#15892986)

Hmppf And here I thought it stood for "luscious"

Re:IMHO (1)

Fulcrum of Evil (560260) | more than 8 years ago | (#15896702)

LGLAMP? C'mon, it's Linux, not GNU Linux. That just sounds like ass.

Re:IMHO (2, Informative)

imemyself (757318) | more than 8 years ago | (#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.

Re:IMHO (0)

Anonymous Coward | more than 8 years ago | (#15892916)

Or import the Access DBs to a SQL Server Express implementation and save the trouble of reinventing the entire wheel, or rewritting it in Linux/Apache/My SQL/PHP/Linux.

Re:IMHO (1)

moosesocks (264553) | more than 8 years ago | (#15892923)

Well, yes, but it does have its place.

Just like VB, Access isn't meant to be used for 'Real' applications.

It's a great rapid prototyping tool, and I actually use it fairly often for small databases, or when I need to hammer out a small set of code very quickly that onl needs to run properly once.

Once you get into a large multi-user database, Access is pretty bad. But that's because it's the wrong tool for the job. If the database has grown this large, it's probably time to rewrite/modify the application anyway. Despite the cries of the OSS fanboys, Microsoft SQL server is surprisingly decent if you're restricted to a Win32 platform, and should offer an easy migration path.

As far as handling your access databases goes, if you're looking for functionalilty not provided by access, you shoud be able to whip up a Php,Perl, or Python script that does what you're looking for. I'll also commit heresy again in this post by suggesting you learn a little VBA. It integrates nicely with access, and it's possible to do things with a few lines of code that would otherwise require fairly complex scripts. Of course, again, it's a great rapid-prototyping tool. If you've got 200 tables, you *SEROUSLY* need to look at either moving to a new DB System, or cleaning up your DB schema (30 databases with 200 tables is just ASKING for trouble)

Re:IMHO (1)

bcat24 (914105) | more than 8 years ago | (#15893000)

I dunno Access, but VB is perfectly usable for "real" applications. VB.NET is just another .NET language, so it's as capable as C#. VB6, though not great, is also usable for real applications. I admit that VB's lower learning curve means more people write crappy software with it, but plenty of people write good software with it too.

Re:IMHO (1)

SanityInAnarchy (655584) | more than 8 years ago | (#15893097)

Fortran is just another compiled language. Does that mean it's as capable as Lisp or C?

I mean, if one language is as usable as another for real applications simply because they run on the same platform, then why not program raw assembly -- or raw CIL if you like .NET?

Or does it just have a bad rap because of a quick learning curve? Well, if that's the case, why hasn't Ruby gotten the same reputation? Why is it that the only complaint most people can find about Ruby is that it's a bit overhyped and dog-slow?

It's the syntax, stupid.

There are still plenty of reasons to hate VB, even though it now comes with .NET tacked on the end.

Re:IMHO (1)

Bodhidharma (22913) | more than 8 years ago | (#15893216)

Admittedly, I'd prefer not to go back to ForTran but if it was the only tool available, I'd just get to it.

About half of my job requires programming in Java which is, IMNSHO, the worst programming language evar (except maybe COBOL). If I can deal with that, ForTran would be no problem.

As far as the capabilities of VB, I haven't used it for a long time but it was pretty powerful and had the virtue of some pretty in-depth help files. I got started programming (professionally) with Access and VBA in the antediluvian mid 90s. I was able to learn it on the job and write some usable apps with it. I don't hate VB but it's not a serious option since I only use linux.

Re:IMHO (1)

Nutria (679911) | more than 8 years ago | (#15893338)

Java which is, IMNSHO, the worst programming language evar (except maybe COBOL)

Have you actually used COBOL in a production environment, or are you just spouting elitist CompSci blather?

Re:IMHO (1)

Joe U (443617) | more than 8 years ago | (#15894079)

Spouting blather on Slashdot? Where have you been the past few years?

Re:IMHO (1)

Nutria (679911) | more than 8 years ago | (#15897557)

Spouting blather on Slashdot? Where have you been the past few years?

I know, I know. Still, shining the light of critical thought onto Wrong Conventional Wisdom is always a Good Thing.

Re:IMHO (0)

Anonymous Coward | more than 8 years ago | (#15894962)

Hey, I take offense to that! I'm a CompSci elitist and think COBOL was a PRETTY FINE language for the day.

Re:IMHO (1)

Schraegstrichpunkt (931443) | more than 8 years ago | (#15893411)

About half of my job requires programming in Java which is, IMNSHO, the worst programming language evar (except maybe COBOL).

I feel your pain. Then again, my job involves mostly programming in PHP. PHP 4.

Re:IMHO (1)

Joe U (443617) | more than 8 years ago | (#15894097)

Fortran is just another compiled language. Does that mean it's as capable as Lisp or C?

That's not a valid comparison, all .NET languages compile to the CLR. That puts VB.NET and C# pretty close together.

It's just that BASIC is designed for the beginning programmer and C# for the more advanced.

Re:IMHO (0)

Anonymous Coward | more than 8 years ago | (#15894561)

And Fortran and C both compile to raw machine code, so they're just as close.

Re:IMHO (0)

Anonymous Coward | more than 8 years ago | (#15894674)

That assumes all machine code is equal, it isn't. On the other hand the CLR is pretty rigid about how things are done.

Re:IMHO (1)

LittleBigLui (304739) | more than 8 years ago | (#15899757)

Fortran is just another compiled language. Does that mean it's as capable as Lisp or C?


That's not a valid comparison, all .NET languages compile to the CLR. That puts VB.NET and C# pretty close together.


(1) Fortran and C and a metric shitload of other languages all compile to machine code.

(2) All .NET languages compile to the CLR.

Are you sure that (1) means that the languages can have different capabilities while (2) means those languages all have the same capabilities?

Re:IMHO (1)

Zerbs (898056) | more than 8 years ago | (#15899783)

LAMP is the Access of the decade. Do you really want to suggest people to sidegrade their application? Upgrade! From Access, SQL Server will be the easiest to convert to, as other people have mentioned.

It is being misused (4, Insightful)

PhrostyMcByte (589271) | more than 8 years ago | (#15892880)

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.

Re:It is being misused (1)

NineNine (235196) | more than 8 years ago | (#15892890)

You're right. But I'll go one step further. Import it into SQL Server, the use Toad.

TOAD, definitely. (3, Informative)

Kadin2048 (468275) | more than 8 years ago | (#15892939)

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.

Re:TOAD, definitely. (0)

Anonymous Coward | more than 8 years ago | (#15892979)

If all you want is a schema browser, you hardly need TOAD for that. There are perfectly good schema browsers in virtually any IDE, including Visual Studio, Eclipse, Netbeans, or Oracle SQLDeveloper. All of them perfectly free. SQLDeveloper has a lot more nice features than the expiring crippled FreeTOAD, that's for sure. I think it even does reverse engineered E-R diagrams (though I might be thinking of something else)

And of course most DB's come with their own schema browsers + query analyzers, e.g. mysqlCC, pgadmin, and the stuff that comes with any version of SQL server that isn't Express (but Visual Studio Express has one).

Re:Tora, not Toad. (2, Informative)

CCW (125740) | more than 8 years ago | (#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.

Some suggestions from an Access geek (5, Informative)

MikeB0Lton (962403) | more than 8 years ago | (#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:Some suggestions from an Access geek (2, Insightful)

TheSpoom (715771) | more than 8 years ago | (#15893168)

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:Some suggestions from an Access geek (2, Informative)

fredrated (639554) | more than 8 years ago | (#15893414)

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:Some suggestions from an Access geek (1)

TheSpoom (715771) | more than 8 years ago | (#15893535)

Oh yeah, Access as a UI is great for a few things. Just not as a backend database under any significant amount of load.

Re:Some suggestions from an Access geek (2, Informative)

MikeB0Lton (962403) | more than 8 years ago | (#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.

Re:Some suggestions from an Access geek (1)

CastrTroy (595695) | more than 8 years ago | (#15893934)

I think the other reason why managers stick with Access stems from the licensing aspect. It's a hell of a lot cheaper to run a database off Access than it is to run SQL Server. The express edition is free, and is still an upgrade from Access, although I highly doubt that when you call your MS rep to ask about SQL server, I doubt they really push the Express Edition. I'm also unsure of how many users you're allowed to have accessing it at a single time. I know it has a 4GB limit, which should be a nice upgrade from Access, which seems to crawl once you get up to a couple hundred megs.

Re:Some suggestions from an Access geek (1)

compwizrd (166184) | more than 8 years ago | (#15893981)

also, if you buy the visual studio tools for office, you can make a freely distributable runtime version of Access, so now you don't need to buy Office Pro for every machine that needs Access.

Re:Some suggestions from an Access geek (1)

TheSpoom (715771) | more than 8 years ago | (#15894768)

See, this makes me think that there would be a good spot for a scaleable, open source, free (as in beer and speech) replacement for Access. SQLite is good for application backend, and MySQL is good as a server backend, but AFAIK nothing quite fits into the spot where Access is right now: A tool to quickly stamp out both a database AND a UI without a whole lot of effort.

Maybe even if there were some more prominent free tools to convert Access databases to other RDBMSes it would help.

Re:Some suggestions from an Access geek (1)

CastrTroy (595695) | more than 8 years ago | (#15895424)

Well, idealy, you'd just need a program, (or a person) to do the inital installation, But I imagine a solution with OO.org Base and a MySQL/PostGres/SQLite backend. Plus if your database application grew up a little and was no longer personal, you could already be using a capable database engine. I don't imagine running small databases would be any more resource intensive in Postgres than in Access.

Re:Some suggestions from an Access geek (1)

wwphx (225607) | more than 8 years ago | (#15898636)

I don't know if you are familiar with SQL Server, but Access uses meta-data tables to describe everything just like SQL Server. Learn how to query them, write the reports that you need to analyze the data that you want, export the reports into all the databases, and you should be able to get something reasonable.

Or export the meta-table data into a meta-meta-table and have all your information in one place. I think that's the route that I'd pursue.

Working with system tables is fun! (he says, after trying to install SQL Server 2005 Developer's Edition has screwed up two different computers)

Learn VBA... (0)

Anonymous Coward | more than 8 years ago | (#15892900)

And also get to know ADO and DAO, they let you "access" all the stuff you need.

Re:Learn VBA... (1)

marcomarrero (521557) | more than 8 years ago | (#15897774)

I absolutely agree, learn VBA... DAO is the old, native one, (currentdb) and ADO is the new one. They both quite similar, and different at the same time. ADO is faster. M$ objects are weirdly organized (compared to Delphi or .Net) but, once you figure it out it's very useful.

I wrote my own documenter in VBA that writes the output to Excel sheets. Almost all the info you need is in the TableDef(s) objects. Documenter kinda either lacks detals, or, write a lot of junk you don't need. I'm not sure if M$ released the documenter source, they did release code of many of their wizards for Access 97.

Access is very quirky, and really bad if you need to do huge database transactions quickly or handle more than 10 users. But I kinda like unique stuff like using VBA funcions in Queries - it's horrendously slow, but, actually fun knowing you can write a query that can do anything. (it's dangerous, Access love requerying to refresh the screen). And have a job for quite a while when trying to port that ugly mess to VB.Net. You can also have fun with things not available in VB, like Application.Evaluate (ok, you can from VB if you use an Access or Excel object).

Of course, I'm having a really hard time finding a full time job position, all I get are Access Applications that are mostly really really badly designed databases, or, I have to deal with automating programs that handle absolutely scary data. (sometimes I can't call it 'data')

Re:Learn VBA... (1)

meringuoid (568297) | more than 8 years ago | (#15898982)

But I kinda like unique stuff like using VBA funcions in Queries - it's horrendously slow, but, actually fun knowing you can write a query that can do anything.

I hate myself every time I do that. Filthy betrayal of all I claim to stand for. But I keep on doing it.

(it's dangerous, Access love requerying to refresh the screen).

Oh hell yeah. Scrolling up and down the query output window when you've created some hybrid SQL / VBA atrocity can be so very much fun :-)

Documenter is an ugly report (3, Informative)

THC1972 (989636) | more than 8 years ago | (#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.

having taken a course in access (0, Redundant)

Lehk228 (705449) | more than 8 years ago | (#15892921)

I suggest alcohol, lots of alcohol.

Take two and call me in the morning. (0, Redundant)

twitter (104583) | more than 8 years ago | (#15892927)

"What tools do you suggest for trying to grok a large Access mess?"

A bottle of whiskey and a bottle of wine. Good luck.

Learn Visual Basic (2, Informative)

cyranoVR (518628) | more than 8 years ago | (#15892989)

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.

Visio (4, Interesting)

hbo (62590) | more than 8 years ago | (#15892999)

If someone else is paying for your tools. Visio will analyze any database that has an ODBC driver. (That includes MySQL and PostgreSQL.) Of course, then you end up struggling with two bloated Microsoft tools. But Visio at least can be used to draw pretty pictures. 8)

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)

Re:Visio (1)

Tadrith (557354) | more than 8 years ago | (#15893520)

See, the thing is, Microsoft has provided a MUCH better alternative for a number of years, for free no less. So I wouldn't really consider it a mad plot to make more money.

It used to be called MSDE (Microsoft Data Engine), and it has since been renamed SQL Express. Yes, it is not a full fledged version of SQL; however, the features that are missing are not features that would have been used by anyone considering Access in the first place. The main detractor was that MSDE never had any of the management tools the SQL Server came with, and so I think it discouraged many people who had only dabbled in database work (but were assigned it for their job) from using it. Because they had only dabbled, the idea of creating tables and such without the assistance of a GUI was just more than they were willing to do.

Now, Microsoft has a management tool for SQL Express that is basically a free version of the management tool that comes with SQL Server. There should be absolutely no reason for anybody to use the Jet database engine any longer. But... I'm sure some people will still do just that. :)

I won't argue the price point... Microsoft's products are pricy, we all know that. But, you seem to assert that SQL Server has poor performance. Have you ever even used SQL Server? I'm not claiming it's better than any other alternative database systems, but SQL Server is hardly poor in performance.

Re:Visio (1)

hbo (62590) | more than 8 years ago | (#15893605)

I'll admit I haven't looked at this closely for a couple of years. But every time I have in the past, I concluded that SQL server only made sense where Windows was not optional. I know Microstuff has put a lot of effort into SQL server over the years, and no doubt it has improved from the bloated pig I once so cordially loathed, but then Oracle and IBM and the others haven't stood still in the interim either. And those folks can at least dispense with the GUI, and can employ TCP stacks that don't tune for the desktop by default. All of that helps when you are in a pitched battle for performance. But, like I say, I haven't looked recently. Perhaps Microcash has overcome those disadvantages. And perhaps pigs can fly. 8)

Re:Visio (1)

also-rr (980579) | more than 8 years ago | (#15893587)

But Visio at least can be used to draw pretty pictures. 8)

So can Access! Create a table (rows 1,000 columns 1,000) and map 1 for black 0 for grey and no value for white... it's an ideal way to store your pictures in the database!

Try this in one of ym databases and I *will* shoot you.

Re:Visio (0)

Anonymous Coward | more than 8 years ago | (#15894883)

Mod parent up funny!

Of course, since Access only allows 255 fields per table, you'll have to store multiple pixels in each field...

Re:Visio (1)

HornWumpus (783565) | more than 8 years ago | (#15895898)

No you just have to create a second table with the same keys and join them. Viola 510 (minus your key fields) available fields.

I've seen it done.

A more elegant solution would be to have three tables X, Y, and Pixel.

Access and users who abuse it (1)

Amazing Quantum Man (458715) | more than 8 years ago | (#15894961)

We have a tool that's used for code review logging. It's written in Access (don't ask), by a developer who did it in his spare time.

The guy who wrote it didn't have a clue -- he ties fields directly to the DB (using the DB controls), so there's no transactions, you can't undo, you can't say, "No I didn't mean that... cancel".

On top of that, the guy couldn't design a UI to save his life, so users are continually corrupting the database, overwriting records when they mean to create new ones, etc.. And no, we can't change it, we're subs to the prime, and the prime has mandated the use of this piece of shit.

Re:Visio (1)

jakoz (696484) | more than 8 years ago | (#15900645)

I don't know that I would wholeheartedly recommend going for the MySQL solution.

The MySQL database driver (MyODBC) has had problems with development for a LONG time. It seems to be moving along slowly again now, but keep in mind that it has basically been on the same version forever.

Additionally, there are other problems. For example, if there is even a small error, everything falls over. The Access process has to be killed using Task Manager... hardly an optimal solution for a workplace filled with day-to-day users. There are other problems, but I guess you'll find them as you go.

The MySQL-Access solution is one of those things that is possible, but not optimal for a workplace.

I know this is easier said than done, but (2)

Travoltus (110240) | more than 8 years ago | (#15893242)

Take the time NOW and convert everything to ANSI compliant SQL and then rely on front ends to do ALL non ANSI stuff.

Do it NOW.

When your employer wakes up and decides not to remain with that closed proprietary bug ridden M$ stuff, they'll thank you profusely. Or they'll fire you and you'll come to my employer for a job and get hired right in. :)

ADO OpenSchema? (2, Interesting)

dtfinch (661405) | more than 8 years ago | (#15893243)

I've used ADO's OpenSchema method in the past to get raw lists of table schemas and relationships, including field descriptions.

What tools? (4, Funny)

Discopete (316823) | more than 8 years ago | (#15893304)

Fdisk, Format and reinstall. Next time try the SQL desktop engine or Oracle.

Re:What tools? (1)

heson (915298) | more than 8 years ago | (#15897832)

I actually think this is an insightful coment. The mess suggests poor judgement which in turn suggest poor judgment in the rest of the application. In that very likely case, a rebuild from requirements are probably the most efficient way to get a defect free system.

What i mean is that the database is probably non-normalized, so any change might make the db inconsist. There are probably lots of functions on the leaf level with duplicated code, which ofcourse have slight differences to fix bugs due to previous mention non-normalization. I would also expect important compelx business logic to be split up arbitrary into lots of small parts each put into previous mentioned leaf functions, probably merged with other functionality in some genious attemt at premature optimization.

You still want to understand the design and architecture of the project, or you will probably recreate many of the problems it had going to where it is now.

Access != Good && Access Enterprice Apps (2, Interesting)

jrmiller84 (927224) | more than 8 years ago | (#15893335)

The company I work for runs all of it's POS systems off of MS Access and it's given us nothing but problems. I would suggest switching to anything but Access. If you knew the number of corrupt databases and table property dropping we deal with over a months period (and that's only 72 stores worth, or 72 instances of Access) you'd think twice or think about switching immediately. We are constantly compacting and repairing the databases and it just generates a lot of extra work and takes up time that would better be spent fixing the POS code itself. Steer clear of Access if you want to keep large amounts of data (as it seems you do with that many mdb files and tables). People have argued with me over this, but Access just isn't meant for large scale enterprise applications. If you were a small business I could see it being appropriate but nothing above that.

Re:Access != Good && Access Enterprice App (1)

Oopsz (127422) | more than 8 years ago | (#15893438)

UGH. I feel your pain, man. POS is right. No chance of migrating to MSSQL? You could rebuild the front end to access a local SQL server express DB then replicate changes to a central database for ease of reporting.. I know, easier said than done, but it would be well worth it to ditch access.

Re:Access != Good && Access Enterprice App (2, Informative)

dtfinch (661405) | more than 8 years ago | (#15895565)

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.

Re:Access != Good && Access Enterprice App (1)

jrmiller84 (927224) | more than 8 years ago | (#15895917)

Yeah, you're very right. We actually use Jetcomp as well and it works much better than the other tools we have available to us. Network problems do always seem to be the culprit as storms and such hit even with surge protection they still get knocked out sometimes. Cutting the power in the middle of using the database and its processes can damage it no matter what. Not to mention the store employees aren't exactly computer savvy. When anything bad happens they hit everything and anything available to them(any options in error messages as well as the turning off and hitting any buttons on any devices in the go between) and that screws things up quite a bit. With all that in mind we are migrating to the free version of MSSQL here in the coming months so I suspect most of that will become a thing of the past (Dear god I hope so).

Get the data out of Access and into MDBE now. (1)

HornWumpus (783565) | more than 8 years ago | (#15895988)

It's a common compromise in your situation.

You have too much code in the MDB to just dump it. But the Jet database just blows for anything but single user with a rock solid connection to storage.

You already own the MDBE (or SQL express or whatever it's called now). Use the upsizing wizard to move the data out of the MDB to an instance of MDBE, touch the login code (think about roles, don't give the access client code admin on the database), debug the things that break. Most things should work (poorly) executing the querys in Jet but getting the data from MDBE. For performance some querys will have to changed to passthru and hence rewritten in MSSQL parlance.

It's really not that big a deal and turns the evolution of the beast in the right direction. They stop digging. I've been there, but worse.

Start today. You should have a running Access frontend/SQL backend working in a matter of days, working as well as the Access version did in a couple of weeks.

You're main learning curve will be the admin tools for the backend. Don't let them scare you, this is stuff DBAs can manage so a good coder can grok it quickly (don't let yourself get drawn into learning to code in SQL yet). Let the upsizing wizard do it's stuff on few throwaway database instances (selecting deferent candidate tables for the backend move), look at the result in SQL admin, repeat untill all data is more or less where it belongs. The upsizing wizard will leave you with an MDB full of Access code/reports/forms and attached tables that look and act a lot like access tables. Don't worry about the voodoo that access is doing, just see how much of your app works. You might be surprised.

Only after taming the beast by taking it's data from it do you get to slay it.

You get to pick the tool(s) to replace the Access frontend with. HTF do you use Access in POS? PC on the counter? Managers reports?

Re:Get the data out of Access and into MDBE now. (1)

jrmiller84 (927224) | more than 8 years ago | (#15896837)

I understand what you mean, the only problem being that there are more "politics" (aka bullshit) involved with the switch. We deal with a third party vendor here in town for our POS system development (this was done way before I started there) so there's a lot of problems with getting the number of hours to jive with the money we are putting out, etc etc. Currently I develop all other new applications going forward so that we can slowly ease their grip [off our balls]. To answer your questions, we use access shared on a main computer over a small 2-3 computer lan that all compuers access. This is a really bad system since a.) access is used for large amounts of data (all video game titles, player titles, all transactional histories, credit tracking, etc) b.) windows security across the network creates a lot of problems (dropping privledges, slow access times due to security processing, slow data access time period). Yes, they use PC's on the counters that run our proprietary POS systems that are run in vb6 to access the Access db's on the main computer. The POS handles manager reporting also through a crystal reports viewer that is built in. It's just a bad system with a lot of snags and needs to be changed after the 10+ years its been in service. Technology is better now and things have changed quite a bit since then.

In one of my courses... (2, Interesting)

Secret Rabbit (914973) | more than 8 years ago | (#15893349)

... I was forced to learn access.

The course was taught by "the new guy" who was a *NIX person (of course he got to teach the M$ classes and not the *NIX classes which would have made sense).

Anyway, one class he was showing us how to use objects, etc with what he had made prior to class... it didn't work. He made some comment like, "This worked 10 mins ago in my office." and tried to figure out what was wrong. A few french curses later and we got, "THAT IT! Class is over." And he walked out.

After working against access during the course I found that his reaction was appropriate. In fact, a class-mate that I worked with and I found out that access decides almost randomly when an object exists or not. In the end, we figured out that it had some relationship to the running context (which it shouldn't have), but I digress.

Since then I have purged all access knowledge from my mind. I think I'm the better for it. Or at least my sanity.

So, my advice would be to figure out how the system works, and convert it to a different DB. Maybe PostgreSQL? It's not like management will be able to figure it out.

Access has its place (1)

YrWrstNtmr (564987) | more than 8 years ago | (#15893456)

But NOT 200 tables in 30 mdb files. Way too much weirdness. Obviously, this ...thing...needs a complete redesign from the ground up. Whoever allowed this clusterfuck to happen needs to be shot.

Whatever tool you decide on for the back end, (Oracle, SQLServer, Postgres, whatever), I've had great success using Access as a front end. No data stored in it, but just the GUI and some queries/procedures/functions.

open source to rescue (1)

flex941 (521675) | more than 8 years ago | (#15893478)

You could try using mdbtools ...

Easy... (0)

Anonymous Coward | more than 8 years ago | (#15893590)

Access is about as simple as relational databases get.
All you have to do is learn SQL, then you will actually understand the implications of what the graphical 'query builder' is actually doing for you.
If you are not willing to do this, give up now. It will save you much frustration later, when you don't understand why something isn't working as you expected.
Access is actually quite a nice product when used as a kind of RAD tool. Nested forms are a particularly useful feature. It is one of the few Microsoft products that I have used that is genuinely 'best of class' for its narrow purpose.
Access is really a VERY simple database to use (and in many ways very limited). I would also suggest learning some VBA, as eventually, if you really want to do anything with access, you will need to write modules, or code attached as event handlers on some of the widgets (although there are prebuilt versions for common actions like moving through a record set).

Re:Easy... (1)

TexVex (669445) | more than 8 years ago | (#15895564)

Actually, I would recommend using Access's visual query designer as a stepping stone to learning SQL, not the other way around. I know people who went to school to learn SQL and still get confused about the difference between inner and outer joins. The little arrow on the line connecting two tables (or lack of an arrow) in the query builder makes the type of join you're making quite plain.

Run Away (0)

Anonymous Coward | more than 8 years ago | (#15893623)

Run away. Don't waste your time on these train wrecks. Just like 90% of the VB6 projects out there, it's a lost cause. You'll only end up loathing Microsoft for building tools that attract people with no skills to attempt to build things that should be way out of their league.

Access isn't all bad - ignore the /. bias (5, Informative)

cruachan (113813) | more than 8 years ago | (#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 accordingly. Access is a great front end system for complex data input and reporting but sucks as a database, SQL Server is one of the best databases out there, a browser application is great for deploying a less complex interface to the data but will take a vastly disproportionate amount of effort to match Access's RAD abilities. Take advantage of all of them appropriatly and the combination is a real winner.

Re:Access isn't all bad - ignore the /. bias (1)

bar-agent (698856) | more than 8 years ago | (#15893699)

Mod parent up, he knows whereof he speaks.

Re:Access isn't all bad - ignore the /. bias (0, Offtopic)

pedestrian crossing (802349) | more than 8 years ago | (#15893774)

Where are the mod points when you need them?!

Re:Access isn't all bad - ignore the /. bias (2, Interesting)

sd4l (448263) | more than 8 years ago | (#15893890)

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.

Although I disagree with most of your post (having developed applications in Access at the start of my career 10 years ago and, reluctantly, quite recently) the above is the point I'd like to take to task.

If you are deploying to a large number of people you'll likely deploy the royalty-free Access Runtime which allows you to run an access database but not edit it. It used to be part of the Access Developer's Toolkit (think Access 2.0 and 95 sort of time), then became part of the Microsoft Office Developer product (think Access XP) and now is part of Access 2003 Developer Extensions which comes with MSDN Universal. For a professional developer doing a large deployment, it's invaluable.

I'd also just like to say, don't underestimate the speed of an experience LAMP developer. I can developer web apps far faster in PHP than in Access (and they don't auto-submit amended data back to the database if you click on Previous Record by default).

Re:Access isn't all bad - ignore the /. bias (2, Interesting)

cruachan (113813) | more than 8 years ago | (#15894153)

The problem with deploying Access for widespread use on a casual basis, even with the runtime, is that the various dependencies cause issues. Different versions of libraries etc can rapidly end you up in a support quagmire. True if you've limited yourself to vanilla Access and not made use of anything non-standard it may not be too bad, expecially in a standardised environment, but in my experience it's rare for a business application not to make use of other Office products and environments in all but the smallest companies are to some degree heterogenious. These problems may or may not surface to a greater or lesser extent, but it's always going to be more of an issue to support than a browser application. Hence IMHO there is a certain degree of functionality that is required by a user to make it cost effective to deploy Access.

On the other hand if your user does need complexity then the deployment overhead is often worth it. I'm sure you're a wizz with PHP, and indeed I can throw together a nice PHP bases web application myself with some speed - slapping up a simple Access data entry form and a simple PHP data entry form is indeed probably of similar order of development time. The problem is that in virtually all the reallife business applications I've come across a simple data entry form won't cut it.

We're talking here of such things as dynamic drop down lists (i.e. context sensitive to other data entered on forms), calculations on the fly (i.e. enter net price, then lookup database to determine a gross price depending upon item classification and commission), showing/hiding areas of a form based on data entered, dynamically checking data entered against a database, non-standard controls, integrating graphics and charts, creating or merging Word/Excel documents etc etc. All of which needs to happen responsively - i.e. you'll need extensive Javascript and Ajax coding as the form display/submit cycle is not acceptable.

I'm not saying that you can't do (most) of these with LAMP or find a work-around, just it's a lot more hassle than using Access as a front end, a tool that was designed specifically for business data processing. Cases need to be examined on merit with regard to circumstances and budget to decide what approach is appropriate. What does bug me though is the multiple ill-informed knee-jerk comments on most of this thread along the lines that Access is Microsoft, it's VBA, it's not Open Source and therefore you should throw up your hands in horror, refuse to touch it, and run for the hills. Oddly enough I'm just about to start a project for a major global charity recoding an Access/SQL Server application to WISP precisely because they need to be able to deply an application more widely than they currently have and don't want the Access overhead, so you'll see my comments are based on pragmatism, not ideology :-)

Finally I'm fascinated by the line that you disagree with most of my previous post. What's to disagree with? SQL Server isn't one of the best databases going (you can't honestly propose to run a back-office business system on MySQL?) MDB vs ADP issues? That Access isn't a good front-end application to a back-end database? Identifying groups of users and deplying appropriate tools for each? None of this is contentious, it's common sense.

Re:Access isn't all bad - ignore the /. bias (1, Insightful)

Anonymous Coward | more than 8 years ago | (#15894468)

I love how you say "Access isn't that bad" and then your first step is "Convert everything to SQL Server".

Wisdom from the Data Warehousing department (2, Informative)

MythoBeast (54294) | more than 8 years ago | (#15895524)

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.

Visual Studio (2, Informative)

exKingZog (847868) | more than 8 years ago | (#15893766)

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.

Re:Visual Studio (0)

Anonymous Coward | more than 8 years ago | (#15896815)

That would be SQL Server 2005's Intergration Services. It plugs into Visual Studio.

how... (2, Funny)

syrinx (106469) | more than 8 years ago | (#15894482)

Evolution of unanswerable questions:

Middle ages: How many angels can dance on the head of a pin?

1960's: How many roads must a man walk down?

2006: How do I make sense of Microsoft Access?

access - meaningful ERD (1)

KatTran (122906) | more than 8 years ago | (#15895397)

Take all the access .mdb files and dump them into an MS SQL 2003 database. Take that and scan it with Visio 2003 and create all the ERDs. Use Visio, or your favorite ERD editor to make a real database design (one that conforms to at least 3rd normal form).

Once you have a real database design (implemented in an modern RDBMS) and all the data in MS SQL than you can transform the data to fit the new ERD.

Neal

Get to 3rd then step back thoughtfully. (1)

HornWumpus (783565) | more than 8 years ago | (#15896057)

Third normal form (absolutely no duplicate data) means you can't keep total on invoice. You must query and total line item. Line item in turn must query the pricing history table to return the correct sales price for the invoice date.

Putting a total on invoice and a price on line item both violate the third normal form but are still usually good ideas (there are exceptions where line items are added/removed constantly).

Immediatly fire anybody who speaks of normal forms higher then the third UNLESS they can produce a cognant arguement against de-normalizing thoughtfully. Those who speak of higher normal forms are usually net-negative producers.

Re:Get to 3rd then step back thoughtfully. (1)

budgenator (254554) | more than 8 years ago | (#15910480)

I totaly agree with you Third normal form (absolutely no duplicate data) means you can't keep total on invoice and that makes account statements a nightmare of coding and testing, and in the end you still can never quite trust them. Accounting wise an invoice should be a done deal, if the deal has to change it's better to issues a credit voucher and a new invoice; so their is no real reason to not have a total on the invoice record I think of it as a cross-check, technically a checksum is reduntant data too but we still download them.

Making good sense of Access is hard (3, Informative)

wrfelts (950027) | more than 8 years ago | (#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

Total Access Analyzer (1)

mmerlin (20312) | more than 8 years ago | (#15898075)

This product, Total Access Analyzer,
http://www.fmsinc.com/products/analyzer/index.html [fmsinc.com]
has helped me get my head into new db's on several occasions.

Also by FMS, their Detective product
http://www.fmsinc.com/products/detective/index.htm l [fmsinc.com]
is great for figuring out where tables/code have diverged.

Visio Professional (1)

Nom du Keyboard (633989) | more than 8 years ago | (#15908569)

If you have Visio Professional 2000 or higher you can Reverse Engineer the Access database into a printable ERD that will help you start finding your way around.
Check for New Comments
Slashdot Login

Need an Account?

Forgot your password?

Submission Text Formatting Tips

We support a small subset of HTML, namely these tags:

  • b
  • i
  • p
  • br
  • a
  • ol
  • ul
  • li
  • dl
  • dt
  • dd
  • em
  • strong
  • tt
  • blockquote
  • div
  • quote
  • ecode

"ecode" can be used for code snippets, for example:

<ecode>    while(1) { do_something(); } </ecode>