Access XP

Topic

New Reply

Does anyone have info on whether Access XP cures any of the problems of Access 2000, such as (and especially) performance issues? I was at Office XP launch, and could find no-one from Microsoft who knew anything about Access.

All I’ve been able to find out since then is that you have a choice of using either Access2000 or AccessXP format for database. Apparently AccessXP format offers performance gains, so I guess if you choose to use Access2000 format things are as bad as before.

— Mark Liquorman

Reply | Quote

Replies

I haven’t tried XP yet, but I like 2000 and develop in it. The conventional wisdom is usually to give a new version time to settle down a bit, so you might try addressing whatever problems you’re having with 2000 while you wait for the dust to settle.

Reply | Quote

Actually, I don’t have any problem with A2000, because I never upgraded to it! On the Access forum in Compuserve, however, one of the most frequent questions we get is from people who upgraded to A2000 and have immediate performance problems. Another frequent questions is “Why is A2000 Help so bad?”; I know of some people who keep A97 open while working on A2000, merely so they can switch to it to use Help!

Reply | Quote

Ah, yes, I’ve seen some of the same posts. Usually the poster hasn’t discovered that using a 97 backend is barely practical in Access 2000, they haven’t turned off subdatasheets, and they’re still using Name Autocorrect. Once you deal with those issues, its performance is quite comparable to Access 97.

Reply | Quote

Where can I turn off SubDatasheets – there’s nothing in Tools|Options|Datasheets that I can see relates to Subdatasheets. I unchecked all Name Autocorrect boxes on the General tab. Thanks Charlotte!

I am not certain what is meant by Help being ‘bad’ (as compared to other Helps – I have never found an EASY Help
that does not force me to scan thru reams of unrelated info and many levels of hyperlinks just at a time when I am in a hurry to find an answer to a seemingly easy question – maybe I just don’t have the temperament and/or mental organizing ability to deal with this @%#&).

My problem with Access Help has been (after several installs) that it continues to ask for the CD even though Help loads in a minimized version on the screen. It’s more of an annoyance than a quality issue.

TIA

Reply | Quote

The HTML help is much harder to navigate than the previous versions. I got used to using the Find tab exclusively in previous versions, but it doesn’t exist in HTML help and the Answer Wizard doesn’t do a full text search. Even the MS support people sometimes have trouble finding stuff in it. Plus, it differs depending on whether you’re in the UI or in the VBE, which can be terribly confusing. I’ve found the best way to work with it is to use the Answer Wizard and type in the equivalent of “how do I …”, which usually gives me something approximating what I’m looking for, but not always.

Unfortunately, subdatasheets are properties of the individual tables and you can only turn them off by opening the table in design view and then bringing up the properties dialog and setting subdatasheet to [none] (it’s set to [auto] by default. You can also do it from code, which is the method I generally use. There is code in KB Article Q261000: Slower Performance on Linked Tables to turn off subdatasheets, but I wrote my own before I found Microsoft’s. You also have to watch out for new tables (i.e., make-table queries) because they are all created with the subdatasheet property set to [auto] and there is no way to change the default. BTW that’s also true of the Name Autocorrect–it has to be turned off in each new database.

Reply | Quote

Charlotte,

I’m experiencing exactly the same. Used before only the Find tab I really can go nuts with A2K help. I tried the answer wizard also, it seems to work better, but one has to type more and figure out questions for 2/more items. And I don’t like the VBE/UI splitting either, it’s not entirely logical. When I search some VBA thing, I’ll expect to find the items from the Access catalogue also (DoCmd etc). Now I just search sometimes when I forget about the splitting and search and search… In a moment of mental derangement caused by a fruitless search with “subform referencing” I even thought to decompile the whole bunch and try to recompile with the new help compiler. I don’t know of course if this can be made, but I’m dreaming of those (newer?) help files from other software having more search options.

And I didn’t say anything about the tasty mix of english and german code in the samples… (I had to download the whole original solutions+orders package, the german solutions.mdb is completely unusable. It doesn’t even open due to localized modules incl. table, field etc. names in the code but not localized database object names such as table, field etc. Which means, the thing was not even once clicked upon to test it after localization!)

I have read somewhere the question, if someone in Redmond is reading this…

But let’s think positive 🙂 I’m reading this forum since couple of weeks now, and it’s just great ! This is the highest level forum I found until now, one can really learn a lot. Thanks!! Some day I even may be grateful to Compuserve for having closed the german forums.

Reply | Quote

I’ve gotten used to the split interface now, and it is, after all, what the rest of Office has to cope with so it’s only fair.

I’m fortunate enough to live and work on the same continent with Microsoft, so I don’t have to cope with localized versions, thank goodness. But I’m glad you found the Lounge. I think it’s one of the best things going, even though Moderators are supposed to be modest about such things.

Reply | Quote

Thanks for the info on autocorrect, I’d never heard of that being a problem. As for subdatasheets, is that a problem only if you use datasheets? (Personally, I never use them; I always use a continuous form). I tried to access that article Q261000 in MSKB that you mentioned in another post, but that link appears to be broken (I even went to MSKB site and tried to get it).

Reply | Quote

Get to the “Knowledgebase Search Screen”, don’t pick a Microsoft product, then change the “Search By” to “Specific Article ID Number”, type in Q261000 in “My Question is”, and voila, you got it. Don’t quite understand why the link doesn’t work, it brings up the same exact URL Charlotte posted.

FWIW

Reply | Quote

Did you actually try to call-up the document, or did you just see it in the list of search results? Searching for Q261000 produced a search list with the document on it, but selecting the document from the list is when I get the “page not found”

Reply | Quote

I don’t know what happened to the link, but it definitely isn’t working. Follow TomGs advice to get the article the other way. Here’s the link the the search page: http://search.support.microsoft.com/kb/c.a…&SD=GN&LN=EN-US

There are several knowledge base articles on Name Autocorrect, and it’s been implicated in all sorts of bizarre behavior in Access 2000. Subdatasheets are table properties and have nothing to do with datasheet view. They seem to have been thought up by someone who forgot that the resulting queries would be layered under form queries, etc.. They can seriously delay loading of tables, let alone forms, especially if the tables are linked. If you are using linked tables, by the way, you need to change the subdatasheet property in the BACK END.

Reply | Quote

See my response to Tom’s post. MSKB lists the article, but when I select it to display, that’s when I get the “page not found”. And I can’t figure out how to tell Microsoft!

BTW, what is that little red symbol with an ‘M’ in it next to your name?

Reply | Quote

The symbol just means I’m a moderator.

I don’t know what’s going on with the link. It isn’t something I copied from text, I actually went to the search window, entered the article number and pulled up the article, then pasted the link from there. Now, when I go to the search window and enter the article number, it returns a link to the article, but clicking on the link takes me to an error screen.

What’s even weirder is that if I click the link on the error page that says “Microsoft Product Support Services”, it takes me to http://support.microsoft.com/directory/, but the page says “United Kingdom Product Support”. Clicking the “Support Home” link also takes you to the UK site.

Unless they’ve actually revoked our Declaration of Independence (I’m from California), there’s something wrong with the MS site. Either they’ve screwed up or they’ve been hacked again.

Reply | Quote

Well, I’m sure they must have been hacked, because Microsoft doesn’t mess-up! Just ask them. I was at recent XP launch event, and noticed that MS people referred several times to “when your PC crashes”, but NEVER said “when Windows crashes”.

Reply | Quote

Oddly enough, tonight when I search on the article number, I find no matches. but if I click the link I posted earlier for the article, it goes straight to it. I think I’m losing my grip.

Reply | Quote

Hi Charlotte,
“change the subdatasheet property in the BACK END”?
Are you talking about the same back end mentioned in
http://www.wopr.com/cgi-bin/w3t/showthread…&vc=1#Post44472 ?

Reply | Quote

That post was a little ambiguous. Don’t even think about using Access 97 as a back end for Access 2000. The performance is awful. But in an Access 2000 back end, that’s where you need to turn off the subdatasheets, especially if the back end is on a network. The performance really improves then.

Reply | Quote

Peformance issues?

Reply | Quote

Hi Jim, how have you been?

You know “performance issues”! (I’m starting to talk like Microsoft!) As in “I take issue with the fact that performance generally sucks”!

Reply | Quote

Been doing fine. Hope you are too. But really, from what I’ve heard, A2000 does fine once configured properly and after applying SR1 (bug fix).

It also seems to deal with locking a lot more then other versions, so when working with NT (which has a poor performing lock manager), it seems to be quite a bit slower. But there are ways around that. Top 5 things you can do for A2000 are:

1. Hold open a table in the backend MDB for the life of the app.
2. Turn off the subdata sheet feature.
3. Turn off name autocorrect.
4. Make sure the client/server are using hte same default protocol.
5. Turn off client side caching of files (opportunistic locking under NT)

Jim.

Reply | Quote

[indent]

---

Hold open a table in the backend MDB for the life of the app

---

[/indent]I haven’t run across that one before. What is it supposed to accomplish? Is this for DAO or ADO or both?

Reply | Quote

Keeping a table open in the backend keeps it from repeatedly opening and closing. If a frontend closes all references to a backend, Access goes ahead and closes it. As soon as a reference is made to it, it then opens it again.

With that process, there is quite a bit of overhead (check for access to the file, placing an entry in the LDB file, placing a user lock, etc) so this can really slow down and app.

Under NT, the problem seems to worse because its not that great of a performer, especially the lock manager.

Jim.

Reply | Quote

[indent]

---

especially the lock manager

---

[/indent]This still confuses me. Since Access is the one handling the locks on its pages/records, what does NT have to do with it except to know that there either is or isn’t an ldb file out there?

Reply | Quote

It’s a little more complicated then that. Access only *requests* locks be placed/removed with the OS/NOS that handles the file operations for the MDB file (and hence the LDB file). It doesn’t handle the locks themselves. For example, it says to the OS/NOS “I want to take a lock on byte 16,000,000 of the file xxxx.LDB” The OS/NOS then takes care of recording the lock. With Win 3.1, that was done by SHARE.EXE. With Win9x, it’s done by vshare.vxd. Under NT, it’s the lock manager.

This is called extented byte range locking. What that means is is that Access takes locks out on parts of the LDB file that don’t exist. For example, Access uses a “User Lock” to indicate that a user is in the MDB and taking up one of the 255 slots in the LDB file. This lock is placed in the 7 million byte range (if memory serves correct). The LDB file doesn’t have 7 million bytes though. In fact, it will never grow larger then 16K.

So by using different byte ranges to represent different types of locks, Access can generate whatever type of locking scheme it wants without every writting anything to disk or having to work within any predefined data structure. This means that the locking scheme can be changed from release to release quite easily.

Jim.

Reply | Quote

Jim

For A2K, do you know where I can find code to

1. Hold open a table in the backend MDB for the life of the app.

I’ve seem this code posted somewhere.

John Graves

Reply | Quote

John,

It’s nothing fancey. Can be as simple as opening a hidden form that’s bound to a table from the Autoexec macro.

Or you can set a global recordset variable and just open a table through a startup function.

In one case where a relink of all the backend tables was being done (about 50 tables), the relink time dropped from two and a half minutes down to 15 seconds or so when the first table relinked was held open until the relink was finished.

Jim.

Jim.

Reply | Quote

You’re obviously talking about DAO/ODBC links. What about ADO connections … no direct link?

Reply | Quote

Doesn’t matter because you still end up at JET. Only thing this doesn’t apply to is A2000 with MSDE.

Reply | Quote

But I’ve built mdbs with no tables in them and only ADO connections. In that case, what option would I have?

Reply | Quote

ADO Connection to what? Keep in mind that were talking about a split type of app with the “backend” being a native JET .MDB.

If the “backend” is Oracle, SQL Server, etc, then the tip about holding a table open doesn’t apply.

Jim.

Reply | Quote

ADO connection to Jet. Since I’m not necessarily using linked (or local) tables, I’m not sure whether the tip applies. And if you don’t have linked (or local) tables in the mdb, then there’s no way to keep one open.

Reply | Quote

The tip would still apply. Bottom line is your still touching a JET MDB file. Repeated opening and closing under NT is slow. So as long as a recordset is held open against a table (DAO or ADO), that won’t happen and you’ll see a speed up.

Jim.

Reply | Quote

>>But really, from what I’ve heard, A2000 does fine once configured properly and after applying SR1 (bug fix). <>Turn off client side caching of files (opportunistic locking under NT)<<

I assume this is a workstations setting? But what effect will this have on other applications? Especially as it relates to local disk I/O?

Reply | Quote

I’ve actually seen the same thing in my office. In every case, they had an Access 97 backend that hadn’t been converted. They just converted the front end and didn’t realize that that back end needed it, since the application kept working however poorly.

Reply | Quote

It could very well be. I will be sure to ask from now on!

Reply | Quote

No, it’s a registry hack on the NT server. Don’t remember what the setting is under Novell, but it has the same thing to.

Jim.

Reply | Quote