As a developer, you may be familiar with creating a new Microsoft Jet database file either interactively through the Microsoft Access user interface, or through the Visual Data Manager utility (Visdata.exe) that ships with Visual Basic. You can also create a new Microsoft Jet database file programmatically by using DAO.
For example, your application may need to build a new database when it’s first installed, or perhaps your application needs to create a database to archive or export data. The following code fragment creates a new database by using Visual Basic, where strNewDbName is the path and name for the new database:
Dim dbs As Database Set dbs = CreateDatabase(strNewDbName, dbLangGeneral, dbVersion30)
Note The previous example is taken from the CreateNewDatabase function, which is available on the companion CD-ROM.
This example uses the CreateDatabase method to build a new, empty database. The arguments to CreateDatabase specify the path and name for the new database, the language used for sorting and string comparison, and the version of the new database.
The version argument is specified as dbVersion30 when a Microsoft Jet version 3.5 database is created because it uses the same file and table format as Microsoft Jet version 3.0. However, databases created with the Microsoft Access 97 user interface can’t be opened with Microsoft Access 95. This is because a Microsoft Access 97 database’s Visual Basic for Applications project (the set of all code modules in a database, which includes modules associated with forms and reports, as well as standard and class modules) can’t run in the version of Visual Basic for Applications used in Microsoft Access 95. Additionally, some database objects created with Microsoft Access 97 may use application-defined properties and have features that are not supported in earlier versions of Microsoft Access.
If you use the CreateDatabase method to create a database specified as dbVersion30 and use only DAO methods to create tables, relationships, and queries, both Microsoft Access 95 and 97 treat the database as a native database the first time it’s opened. However, both versions of Microsoft Access add an application-defined property named AccessVersion to the Properties collection of a database the first time it’s opened. Microsoft Access 97 also adds a system table named MSysModules2 to contain the database’s Visual Basic for Applications project, which is treated as an invalid database object by Microsoft Access 95. After the first time a database is opened in either version, it’s no longer treated as a native database in the other version.
For a Microsoft Access version 2.0 database, the AccessVersion property returns “02.00.” For a Microsoft Access 95 database, the AccessVersion property returns a string that always begins with “06” followed by a period and two digits. For a Microsoft Access 97 database, the AccessVersion property always begins with “07” followed by a period and two digits. In both Microsoft Access 95 and 97, the final two digits returned by the AccessVersion property may vary depending on the dynamic-link library (DLL) used to create a database’s Visual Basic for Applications project. For this reason, if you want to determine the version that produced or is using a database, your code should ignore the final two digits. The following function returns the version of Microsoft Access used to create or open a database by examining only the first two digits returned for the AccessVersion property. This function takes a String argument that specifies the path to the database.
Function FindVersion(strDbPath As String) As String
Dim dbs As Database
Dim strVersion As String
Const conPropertyNotFound As Integer = 3270
On Error GoTo Err_FindVersion
' Open the database and return a reference to it.
Set dbs = OpenDatabase(strDbPath)
' Check the value of the AccessVersion property.
strVersion = dbs.Properties("AccessVersion")
' Return the two leftmost digits of the value of the AccessVersion property.
strVersion = Left(strVersion, 2)
' Based on the value of the AccessVersion property, return a string indicating
' the version of Microsoft Access used to create or open the database.
Select Case strVersion
Case "02"
FindVersion = "2.0"
Case "06"
FindVersion = "7.0"
Case "07"
FindVersion = "8.0"
End Select
Exit_FindVersion:
On Error Resume Next
dbs.Close
Set dbs = Nothing
Exit Function
Err_FindVersion:
If Err.Number = conPropertyNotFound Then
MsgBox "This database hasn't previously been opened with Microsoft Access."
Else
MsgBox "Error: " & Err & vbCrLf & Err.Description
End If
Resume Exit_FindVersion
End Function
If you create a dbVersion30 database by using DAO methods and then add forms and reports by using the CreateForm or CreateReport functions (these functions are available only in code run from Microsoft Access), a Visual Basic for Applications project is added that corresponds to the version of Microsoft Access used to run the code. You can also add modules and insert code in a database by using Visual Basic code in Microsoft Access 97. Doing so also creates a Visual Basic for Applications project that is specific to Microsoft Access 97.