Converting from Access 1 or 2 to Access 95 or 97

        2 votes: *****     2,147 views      No comments
by Allen Browne, 20 April 2005    (for Access v1, v2, 95, 97)

Issues in converting from Access 2


Crucial Issues:

  1. A later version database cannot "save as" a version 2 database again. Only tables can be exported from Access 95 or 97 to previous versions. Access 2000 and 2002 can revert to the Access 97 format, but not to Access 2.
  2. Immediately after converting, open any module, and compile (Debug or Run menu). Using the database without resolving compilation issues is inviting disaster.
  3. Any 16-bit API calls need replacing, including data types for DDE channels, hWnd, etc. Use the specific declarations from "win32api.txt" distributed with Visual Basic.
  4. Module Names: Access 1 and 2 permitted a procedure with the same name as a module. Later versions do not, presumably because the module name can be specified as part of the function name (enabling you to call functions in code-behind-form modules). So if a converted database complains about an invalid call to one of your functions, rename the module or the function.
  5. Constants: The 32-bit versions use a different style of constant. The Object Browser (on the code window toolbar) lists these.
  6. Library References: Access 2 used the DAO Library. Access 2000 and 2002 do not reference this library by default. Make sure you have a reference to the "Microsoft DAO Library" suited to your version. If you also have a reference to the ADO library, you may need to disambiguate by explicitly referencing DAO, e.g. Dim rs As DAO.Recordset
  7. Parent property: if you used this property for attached labels or buttons in an option group, change your code: the Parent is no longer the form.

Optional Issues:

  1. SendKeys: Do not use SendKeys to reference menus, as menu references change between versions. VBA (Access 95 onwards) has a bug that toggles NumLock off, and SendKeys frequently triggers that bug. Use new methods in place of SendKeys, e.g.
    • To drop combos down, use the new DropDown method instead of SendKeys "{F4}".
    • To set program options, use SetOption.
    • In Access 97, 2000, or 2002, use the Undo method of a form or control rather than SendKeys "{Esc}".
  2. OwnerAccessOption: This option is no longer the default for queries.
  3. Data Types: You may wish to modify code to take advantage of the Date type (instead of Variant), or Boolean (instead of Integer).
  4. Speed: Access 97, 2000 and 2002 support lightweight forms and reports. You can speed up your database by arranging code astutely. You can also save memory and therefore increase speed by using only current constants and methods and removing any unused libraries from Tools | References (when a module is open.)

Problem Issues:

The worst problems are those that yield no error message, but return results different from the previous version.

  1. Access Basic considered a space and an empty string equivalent (i.e. ? "" = " " returned True). The same expression in VBA returns False. Watch this in any code that parses strings.
  2. International dates: VBA treats dates differently from Access Basic, ignoring the Regional Settings if a literal date makes sense in American format. For example, with a British date setting (dd/mm/yy) in Control Panel, enter these two calculations in the debug window:
        ? #23/12/96# - #20/12/96#
        ? #13/12/96# - #10/12/96#
    Believe it or not, the first yields 3 days, but the second 62. VBA interprets #13/12/96# as 13-Dec-96, but #10/12/96# as 12-Oct-96. Access Basic (ver. 1, 2) always respected the Control Panel setting and did not have this inconsistency.

Home Index of tips Top

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Converting from Access 1 or 2 to Access 95 or 97'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).