Databases - MS Access XP

Warning: Code examples for MS Access 97 won't work with MS Access XP (aka MS Access 2002)

That's right, you can convert an MS Access 97 application so that it will work with MS Access XP, but you can not create a new MS Access XP application and use the MS Access 97 code examples.

And the help on getting from here to there - there isn't any (what a joke).

Despite many problems, I have frequently recommended MS Access 97 to people that want to teach themselves about databases. Mainly, this is because their machines already have the program, but also because the built-in help is some of the best Microsoft has ever produced. I my opinion, MS Access XP is a major step backwards.

Application Help | ADO Help | DAO Help | Current path | Loops

Application Help

By default, the help application REQUIRES that the CD be in the machine. It only took a few hours to fix this "problem" (after the CD was found).

The trick is to install the help via

Installed on first use

If you select Run from my computer (which is supposed to install it), then help will still require the CD.

Well, once you have the help, its of limited use because many of the commands don't work.

Typically, when you are writing code, you use the help by clicking on a key word and pressing F1. For the following lines of code

Dim conDatabase As ADODB.Connection ' this works, but has no help
Dim dbs As Database                 ' this fails, but has great (worthless) help

Click on Dim and press F1 - Super help is displayed
Click on ADODB and press F1 - This is one of the most important commands, but there is no help at all ... just an error
Click on Database and press F1 - Help is displayed, but it has nothing to do with the command

If you search the help Answer Wizard for Database, there is a ton of help - the problem is that it is for the old (97) version of Access and that NONE of the examples work. (Using Index produces similar results.)

If you search the help Answer Wizard for ADODB, there is absolutely no help at all.

ADO Help

Actually the ADO help was installed at

C:\Program Files\Common Files\Microsoft Shared\Office10\1033\ADO210.CHM

you just have to know where to look (I ran InCtrl before installing the help).

For useful example code, check out ADO / code examples in VB. Notice that ADODB is still not in the "index".

For more details and a code example see Databases - Referencing Tables

Note: Unfortunately, InCtrl is no longer available for free.

More Info

I originally found the necessary ADO help on the internet (via google) and on Microsoft's web pages, not in the local MS Access help files. It took many hours of searching and reading before I figured any of this out. At first I found other people with the same problems I was having and saw that the responses used ADODB variables ... and then one thing led to another.

Basically, as a somewhat experienced MS Access 97 programmer, it has taken me several days of hard work to get a simple button to read one record out of an MS Access XP table.

Of particular help was Microsoft Access Code Samples which eventually got me to Fundamental Microsoft Jet SQL for Access 2000 which had a short subroutine at the bottom of the page. Apparently, MS Access 2000 has the same problems.

DAO Help

Just to make things a little more confusing, the MS Access XP help contains information on DAO. Of course, the code excerpt below fails (the code is from the help, I added the 2 comments)

Dim dbs As DAO.Database  ' not allowed
Dim tdf As DAO.TableDef  ' not allowed
Set dbs = CurrentDb

The undocumented trick is to add a reference to the DAO library (dao360.dll).

I feel very safe in saying that MS Access XP has the worst help that Microsoft has ever written.

Actually, 2 copies of DAO help are available on my system (you just have to know where to look)

  html help - DAO360.CHM

Normal help - DAO35.HLP

Current path

I have a form that uses the following command to load a table from an *.csv file.

   DoCmd.TransferText acImportDelim, "Aliases_tbl_Import_Specification", _
                           "Aliases_tbl", Filename1

Both of these work ok

 Filename1 = ".\" + Filename1
 Filename1 = CurrentProject.Path + "\Alias_Configurations\" + Filename1

but this fails

 Filename1 = ".\Alias_Configurations\" + Filename1

with something like

'.\Alias_Configurations\' is not a valid path.

Searching the worthless help for path only said that it was a property. No examples, of course. Searching for current, I noticed that CurrentProject was an Object. Well, I guessed and discovered that CurrentProject.path produced the results I needed. Well, perhaps the help wasn't all that bad ... but I never found the command in the help. I also had to read through a lot of crap before I could put it together. (I really hate MS Access 2000 help.) Well ... I went back to the help and wouldn't you know it, the exact example that I needed was there all along (under path) ... I just had to scroll the window sideways to see it. (I still find the help hard to use.)

This applies to both MS Access 2000 and 2002 SP1 (aka MS Access XP).

Loops

All I wanted was a simple loop.

I searched the help index for do and until ... nothing was found. I typed do until in the editor and pressed F1 - help on do loops was displayed.

It turns out that searching for loops would have worked.

This help sucks!

Author: Robert Clemenzi - clemenzi@cpcug.org