h2g2 The Hitchhiker's Guide to the Galaxy: Earth Edition

As mentioned somewhere else some genius has decided that a dyed in the wool Oracle person is the ideal resource to work on an Access migration / enhancement project.

Anyway, I'm taking an Access 97 db to Access 2002 and trying to make it work a bit better. So here are some questions from an (almost) complete Access newbie.

1) It's actually made up of 2 mdb's, one with all the queries, reports, forms, some local temp tables and some linked tables to the 2nd mdb. The second mdb is nominally the backend and has all the data tables. These are both stored on a network. If I copy them locally (hdd) and update the links, it runs OK. Across the network though it runs like a complete dog. Even worse if there are multiple users in. Are there any tweaks / settings should be set for multi-user WAN usage?

2) When converting to 2002, it comes back saying there are errors. So I opened up the code in the modules and did a 'compile xxxxxxx'. This brings up an error with a line saying:-

"Compile error: Method or data member not found"

" If Me.subrepTOTALS_FP.Report.HasData = 0 Then" is the line, specifically the "subrepsTOTALS_FP" bit.

There are no subreps listed under 'reports' with that name. What does the 'Me.' bit mean?

Please ask for more info if needed, not sure what I need to supply here. It's been 4 + years since I did this stuff!

cheers

Me is a "default" parent object - for instance in a form or a report, you could address a subform/-report by using "Me.(name of sub)"

I haven't used 2002 yet - and I'm not too experienced at multi-use across networks. The split in 2 dbs is very common though - also what I use to do.

I haven't used 2002, but a couple of years back I had to get a 97 DB working properly in 2000. 2000 had a "convert database" option which worked pretty well, but slightly screwed the alignment of the fields in a densely packed form, so I had to spend a couple of days fixing it.

If 2002 doesn't "convert" directly from 97, see if it'll convert from 2000. If that is the case, "borrow" a copy of 2000 and convert the databases in two steps.

Cheers both for that!

I have managed to convert the DB to 2002 from 97 in one go, there was one error on that report mentioned above but after asking round it appears to be a half developed bit of code (it's full of 'STOP' statements when it gets to various points!). So I deleted it

So far I've converted the DB, which also compacted it from 30Mb to 7Mb !! I assume regular compacts are a good thing in Access?

I've also brought the Front End (forms, reps, queries etc) across to the local machine and left the backend on the network. Updated the links. This had a fairly big impact on performance, seems Access doesn;t like networks?

All told, the above seems to have had a fair old impact on performance, 1:05 minute actions now taking 15 seconds, longer queries cut by 10 - 15 mins.

Guess I'd best go look at the code now, which i shall no doubt curse cos it isn't SQL as I know it!

Any other suggestions on performance improvements welcome.

After fighting bloated Access dbs for a while - and long one, too - I discovered the setting "compact on exit". That really helped me a lot!

(afaik, Access doesn't automatically get rid of all sorts of temporary stuff you get out of trying queries, designing forms etc)

I'm not good enough at SQL to say whether or not Access' implementation is good, bad or just MS Different - but I get by with it for my purposes.

Their coding environment is something I do like, though - probably the only kind of smart thinking by MS that I feel works really well. Always getting a list of available properties & methods for whatever object you're writing in there is just nice... methinks, anyway