In Microsoft Access 95, Visual Basic for Applications replaced Access Basic, which was used in versions 1.x and 2.0 of Microsoft Access. In most respects, Visual Basic is identical to Access Basic, and Microsoft Access makes most necessary conversions to your code automatically when you convert your database.
However, the conversion process makes some changes to your code that you need to be aware of, and there are some additional changes that you must make yourself in order for your application to run successfully in Microsoft Access 2000.
Note The /runtime command-line switch is not available in the stand-alone or Office Professional Edition versions of Microsoft Access 2000. However, the /runtime command-line switch is available in the Office Developer Edition version of Microsoft Access 2000.
Calling the Windows Application Programming Interface
If your existing version 1.x or 2.0 Access Basic code makes calls to the Windows application programming interface (API), you may need to modify these calls when you convert your database. Versions 1.x and 2.0 of Microsoft Access are 16-bit applications and run on 16-bit versions of Windows. Microsoft Access 2000 is a 32-bit application and runs on 32-bit versions of Windows — Windows 95 and the Windows NT platform.
Check any Declare statements to ensure that they refer to the correct 32-bit dynamic-link libraries (DLLs). For example, 32-bit versions of Windows include User32.dll; in earlier versions of Windows, this DLL is called User.dll. For more information, see Convert Code That Calls a DLL.
The names of some functions in the Windows API have changed. Additionally, functions in the 32-bit Windows API are case-sensitive. For a complete reference, see the Microsoft Win32 Software Development Kit.
Some Properties and Optional Arguments Are Strictly Typed
Certain properties that returned Variant values in previous versions of Microsoft Access now return strings. Code that includes these properties will now run faster; however, when you convert a database from a previous version of Microsoft Access to Microsoft Access 2000, you should verify that no errors are introduced by these new return values.
In addition, some optional arguments which were of type Variant in previous versions of Microsoft Access are now strictly typed. Affected methods, properties, and functions include the GoToPage method of a Form object, the OpenCurrentDatabase, Echo, Quit, CreateForm, CreateReport, CreateControl, and CreateReportControl methods of the Application object, the Column property of a combo box or list box, and the domain aggregate functions. If your code includes any of these elements, you may want to verify that no errors are introduced when you convert your database.
To check for errors, first compile all code in the database. Next check for run-time errors when you run your code. The errors most likely to occur are a "Type mismatch" error, or an "Invalid use of Null" error.
You should also check for silent errors that may be introduced into your code when you convert a database. For example, the RecordSource property of a control previously returned a Variant value, but now returns a string. A string can't contain a Null value, although a Variant can. Code in a converted database that checks whether the value of the RecordSource property is Null will no longer run properly, because the value of the RecordSource property will never be Null. The following procedure is an example of code that checks whether the value of the RecordSource property of a text box is Null.
Private Sub Form_Load()
If IsNull(Me.RecordSource) Then
Me.Recordsource = "Employees"
End If
End Sub
In Microsoft Access 2000, the IsNull function will never return True for the value of the RecordSource property. If the RecordSource property has not already been set in the previous example, its value is an empty string, the value returned by the IsNull function is False, and the procedure will never enter the If...Then construct to set the RecordSource property.
To correct the code in the previous example, use the Len function to check the length of the string returned by the RecordSource property to determine whether it is an empty string. The following line corrects the code in the previous example:
If Len(Me.RecordSource) = 0 Then
' Remainder of procedure here.Changes to Modules
In versions 1.x and 2.0 of Microsoft Access, modules containing general procedures not specific to any form or report module are called global modules. In Microsoft Access 2000, these are called standard modules.
Microsoft Access 2000 includes class modules, which you can use to create custom objects. Any public procedures in a class module become methods and properties of a custom object when you create a new instance of the class. Class modules exist both independently and in association with forms and reports. For more information about class modules, see Program with Class Modules.
The CurrentDb Function Versus DBEngine(0)(0)
Use the CurrentDb method instead of DBEngine(0)(0) to return a Connection object variable that points to the current database. The CurrentDb function creates another instance of the current database, whereas DBEngine(0)(0) refers to the open copy of the current database. Using DBEngine(0)(0) limits your ability to use more than one variable of type Database that refers to the current database.
The DBEngine(0)(0) syntax is still supported, so Microsoft Access doesn't change your code during the conversion process. However, you should consider making this modification to your code in order to avoid possible conflicts in a multiuser environment.
The CurDir Function
The CurDir function behaves differently than it does in version 1.x or 2.0 due to the way that applications interact with Windows 95. Since each application has its own current folder, setting the current folder in Windows 98 by double-clicking an icon doesn't affect the current folder in Microsoft Access. The CurDir function in Microsoft Access 2000 always returns the current path.
Assigning an OLE Object to a Variable
If you manipulate OLE objects or other binary data in your code, you should use an array of bytes to store binary data. In versions 1.x and 2.0 of Microsoft Access, you assign OLE objects, or other binary data less than 64K in size, to string variables when you need to manipulate the objects or data in code. You also assign the data returned by the GetChunk method to string variables. However, Visual Basic supplies a Byte data type and byte functions such as LeftB and RightB. In Microsoft Access 2000, you should store binary data in an array of bytes instead of a string variable, and you should use the byte functions to manipulate that data.
Declare DDE Channels as Variant or Long
If you use the DDEInitiate function to open a DDE channel, you can declare the variable that stores the channel number, which is a Long value, as either a Variant or Long value. In versions 1.x and 2.0 of Microsoft Access, the channel number is an Integer value, so you must modify any Declaration statements in your code that create variables of type Integer to store the channel number.
String Conversion Fails If the String Contains a Percent Sign (%)
You can't assign a string containing a percent sign to a variable or a field that has a numeric data type, as in the following example:
Dim intX As Double
intX = "10" ' This works.
intX = "10%" ' This returns an error.The hWnd Property
If you use the hWnd property in your code to pass a window handle of a form or a report to a Windows routine, you should pass the value directly to the routine. You shouldn't assign the value of this property to a variable. For example:
If Not IsZoomed(Screen.ActiveForm.hWnd) Then
DoCmd.Maximize
EndIfIn versions 1.x and 2.0 of Microsoft Access, the hWnd property of a form or report is an Integer value. In Microsoft Access 2000, the hWnd property is a Long value, which you must change your code to accept.
DAO Property Objects vs. Microsoft Access Property Objects
You can't use an object variable in your code to refer to a Category property. The Category property is no longer supported for Form, Report, and Control objects.
The Parent Property
In Microsoft Access 2000, if you use the Parent property of a control in code or in an expression on a form or report, it typically returns the Form or Report object that contains the control. For example, if CategoryID is a text box on the Categories form, the code Forms!Categories!CategoryID.Parent returns a reference to the Categories form. There are two exceptions: for attached labels, the Parent property now returns the control the label is attached to; and for controls in an option group, it now returns the option group control.
Calling Code in Microsoft Access Wizards
If code in your version 1.x or 2.0 applications calls procedures in Microsoft Access wizards, after you convert your application, you must establish a reference from your application to the wizard database that contains the procedures that you call. For more information on establishing references, see Set References to Type Libraries.
In Microsoft Access version 2.0, there's no distinction between wizards and libraries, so their public code is always available to the current database. In Microsoft Access 2000, wizards and other add-ins are no longer treated as libraries. In addition, because wizards may change a great deal from one version of Microsoft Access to the next, you may need to rewrite some of your code to adapt to the changes after upgrading to a new version of Microsoft Access.
For code that's no longer provided in the Microsoft Access wizards, such as the AutoDialer, functionality has been added to Utility.mda, a special library database that's provided with Microsoft Access. A reference to this library is automatically added when you convert a database to Microsoft Access 2000.
Setting a Reference to a Microsoft Access Database
You cannot set a reference to a database created with a prior version of Microsoft Access from Microsoft Access 2000. You must convert the database to a Microsoft Access 2000 (version 9.0) database in order to set a reference to it.
Handling Automation Errors
In versions 1.x and 2.0 of Microsoft Access, COM components that support Automation (formerly called OLE Automation servers) return a single error. Visual Basic now enables a COM component to return error information specific to the error that has occurred. If your existing databases include code to handle a single Automation error, you may have to update that code to handle the more-specific errors that will now be returned.
Microsoft Access Errors and the Error Function
In Microsoft Access 2000, you use the properties of the Err object to obtain information about errors.
When a Microsoft Access error occurs, you can get the error number and the error description by using the Err object's Number and Description properties, as in the following example:
Debug.Print Err.Number, Err.DescriptionTo return information about Microsoft Access, Visual Basic, or DAO errors, use the AccessError method. With the AccessError method, you can obtain error information regardless of whether the error has actually occurred.
Exclamation Point vs. Dot Operator When Referencing DAO Fields
If you used the . (dot) operator syntax when referring to a Field object of a Recordset object in applications created in versions 1.x or 2.0 of Microsoft Access, you must modify those references to use the ! (exclamation point) operator syntax. Or, if you want to continue using the . (dot) operator syntax, you must establish a reference to the Microsoft DAO 2.5/3.5 compatibility library in the References dialog box, available by clicking References on the Tools menu while in module Design view.
Converted ActiveX Controls
If your application contains ActiveX controls (formerly called OLE, or custom, controls) that were set up in version 2.0, you may need to insert the ByVal keyword in front of arguments that are passed to event procedures called from ActiveX control events, as in the following example:
Sub ChangeMonth_Click(ByVal intCurrentYear As Integer)In order to determine whether an argument needs to be passed by value, click Compile All Modules on the Debug menu in module Design view. If you receive the following error message, you need to insert the ByVal keyword in front of the argument: "Event procedure declaration doesn't match description of event having the same name."
Because type checking of arguments is improved in Microsoft Access 2000, new event procedures created for ActiveX controls automatically have the ByVal keyword inserted when it's needed.
Calling Procedures in Form and Report Modules
In versions 1.x and 2.0 of Microsoft Access, you couldn't call a procedure defined in a form or report from anywhere but within that form or report module. In Microsoft Access 2000, you can call a public procedure in a form or report module from any procedure in the current database. You must qualify the procedure with the class name of the form or report module. For instance, to call a procedure named DisplayMessage that's defined in the module of the Orders form, you would use the following syntax:
Form_Orders.DisplayMessageIt's better to place procedures that you will call from outside a form or report in a standard module if possible, rather than in a form or report module.
Referring to Forms, Reports, and Controls in the Debug Window
When you're testing and debugging code, you must fully qualify all references to objects that you use in the Debug window, unless you've suspended execution in a form or report module. This means that in the Immediate pane of the Debug window, you must use the code Forms!Categories!CategoryID to refer to the CategoryID control on the Categories form in Form view, instead of just CategoryID, even when the Categories form is the current form.
Also, you can't use the Me keyword in the Debug window to refer to an object on a form or report when that form or report is in Design view unless you've suspended the execution of code in the form or report.
Converting Forms and Reports That Contain ActiveX Controls
When you convert a Microsoft Access 2.0 database for use in Microsoft Access 2000 (version 9.0), ActiveX controls on forms and reports may not be converted automatically. Microsoft Access 2.0 supports 16-bit OLE controls, while version 9.0 supports only 32-bit ActiveX controls.
If you're converting a database that contains a form or report that has a 16-bit version of an ActiveX control, and the 32-bit version doesn't yet exist on your system, Microsoft Access will generate an error message. You must obtain a 32-bit version of each ActiveX control that you wish to update and register it in the system registry. You should then save the form or report in the converted database and close and reopen the database.
Enabling a Database Created with a Previous Version of Microsoft Access
When you enable a large database created with version 1.x or 2.0 of Microsoft Access, but don't convert it to Microsoft Access 2000, you may need to increase the maximum buffer size beyond the default. To change this setting, open the system registry and navigate to the \HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.0\Engines\Jet 2.x subkey. Create a new subkey named ISAM, and within this subkey create a new DWORD value called MaxBufferSize. Set this value to 1024, decimal base. For more information about editing the registry, see your Microsoft Windows documentation.
Renaming a Database
When you rename a database, compiled code in the database will be decompiled. To recompile code and save all modules in a compiled state, open the database, open a module in Design view, and click Compile And Save All Modules on the Debug menu.
Time Values in Query Criteria
When you convert a version 1.x or 2.0 database to Microsoft Access 2000, queries that contain criteria based on specific time values in Date/Time fields may return different results than they do in earlier versions. This behavior may also occur if you link tables from a version 1.x or 2.0 database to a Microsoft Access 2000 database. Only the time portion of Date/Time fields is affected.
Visual Basic Functions That Are Not Supported in Expressions
The following six Visual Basic functions can't be used in expressions outside a user-defined Sub or Function procedure:
| EOF | Loc |
| FileAttr | LOF |
| FreeFile | Seek |
If you need to use one of these functions in an expression outside a procedure, you can call the function from within a user-defined function that you call from the expression.
Line Numbers in Visual Basic Procedures
You can't assign line numbers greater than 65,529 to statements in your Visual Basic procedures. If your converted version 1.x or 2.0 Microsoft Access application contains line numbers greater than 65,529, you must modify them to fall within the acceptable range.
The Next Procedure and Previous Procedure Buttons
The Next Procedure and Previous Procedure buttons that are on the Module toolbar in versions 1.x and 2.0 aren't available in Microsoft Access 2000. If you convert a version 1.x or 2.0 database with a custom toolbar that contains one of these buttons, you won't receive an error, but the buttons will have no effect when clicked.
"Out of Memory" Error When Converting Large Databases
Visual Basic for Applications has a limit of 1,082 modules per database, and forms and reports each contain one module. To resolve this problem, reduce the number of objects in your database or consider dividing your application into multiple databases. If you have modules with a large amount of code, consider using library databases to store the code.
Formatting Null Values and Zero-Length Strings
In version 2.0 of Microsoft Access, you can use the Format function to return one value for a zero-length string and another for a Null value, and you can similarly use the Format property to automatically format fields in table Datasheet view or controls on a form or report. For example, you can use a format expression such as the following with the Format function to return the appropriate string value from code:
Dim var As Variant, strX As String
' Assign some value to strX and pass to Format function.
var = Format(strX, "@;ZLS;Null")In Microsoft Access 2000, you must test separately for the Null value case and then return the appropriate value based on the result. For example, you could use the IIf function in an expression with the Format function, such as the following:
var = IIf(IsNull(strX),"Null", Format(strX, "@;ZLS"))This change applies only when you use the Format function to format a value depending on whether it's a zero-length string or a Null value. Other format expressions used with the Format function continue to work as they do in previous versions.
If you convert a database from version 2.0 of Microsoft Access to Microsoft Access 2000, you'll need to change any code or property settings to use these methods. You can't use the Format property in table Datasheet view to distinguish between Null values and zero-length strings.