Lesson 21: The Microsoft Access Object Library

	The Microsoft Access Object Library

Introduction to the Microsoft Access Object Library

The MAOL

Microsoft Access Object Library (MAOL) is a library used by Microsoft Access to create and manage databases. The library is automatically available when you start a new database or if you open an existing database. To check its existence, on the main menu of Microsoft Visual Basic, you can click Tools -> References... In the Available References list of the References dialog box, you can see that the Microsoft Access Object Library is checked:

References

The Microsoft Access Object Library relies on classes to do anything. The most fundamental class in this library is called Object (remember that Microsoft Access is not case-sensitive). Therefore, when using variables that would use this library, you can declare a variable of this class and then initialize it.

One of the most fundamental objects in the Microsoft Access Object Library is named DBEngine. Everything that exists in your database comes directly or indirectly from this object. Because this object is static (it does not change) and is automatically available when you start a new database (it is one of the default objects of the application), you do not need to declare a variable for it. If you want to use it, simply type its name and it is available.

Sessions

When you start a database in Microsoft Access, you are said to have started a session. If one database is not enough for what you are currently doing, you can open another existing database or you can create a new one. In the same way, you can open as many databases as you want. The group of databases opened by a user is referred to as a session:

Session

Workspaces

In Microsoft Access, a session is programmatically considered, or called, a workspace and it is represented by the Workspace object. When you start a new database, you get a default workspace. If necessary, you can create one or more additional workspaces. The group of workspaces you are using is stored in a collection called Workspaces. To identify the workspaces, the DBEngine class is equipped with an indexed property named Workspaces. Therefore, to identify a workspace inside a DBEngine object, access the Workspaces() property and pass either the index or the name of the workspace. The first workspace in the DBEngine object can be identified with DBEngine.Workspace(0). The second workspace can be identified as DBEngine.Workspace(1) and so on.

Microsoft Access Object Library and Databases

Creating a Database

In the Microsoft Access Object Library, a database is an object of type Database. Therefore, if you want to start an object associated with a database, you can declare a variable of type Object or of type Database.

If you declare a variable using the Object class, when using the variable in the Code Editor, the Intellisense will not work. This means that you have to know for sure what you are doing because the Intellisense will not assist you.

To assist you with creating a database, the Workspace object is equipped with a method named CreateDatabase. Its syntax is:

Workspace.CreateDatabase(ByVal Name As String, _
			 ByVal locale As String, _
			 ByVal options As Variant) As Database

The first argument can be the name of the database you want to create. If you provide only the name of the database, it would be created in the same folder where the action was initiated. For example, if you are already working in a database and you create a new database by providing a name, the new database would be created in the same folder where the current database resides. Here is an example:

Private Sub cmdDatabase_Click()
    DBEngine.Workspaces(0).CreateDatabase("Exercise.accdb", . . .)
End Sub

If you want, you can store the new database in a drive and folder of your choice. To do this, provide the complete path and the name of the database as the first argument. This would be done as follows:

Private Sub cmdCreate_Click()
    DBEngine.Workspaces(0).CreateDatabase("C:\Microsoft Access Database Development\Exercise.accdb", . . .)
End Sub

The second argument to this method is required and it specifies the language used for the database. This is also referred to as the collation. This argument is passed as a constant named value and can be one of the following:

Constant	Language Group
dbLangGeneral	English, German, French, Portuguese, Italian, and Modern Spanish
dbLangArabic	Arabic
dbLangCyrillic	Russian
dbLangCzech	Czech
dbLangDutch	Dutch
dbLangGreek	Greek
dbLangHebrew	Hebrew
dbLangHungarian	Hungarian
dbLangIcelandic	Icelandic
dbLangNordic	Nordic languages (Microsoft Jet database engine version 1.0 only)
dbLangNorwdan	Norwegian and Danish
dbLangPolish	Polish
dbLangSpanish	Traditional Spanish
dbLangSwedfin	Swedish and Finnish
dbLangTurkish	Turkish

Here is an example:

Private Sub cmdCreate_Click()
    CreateDatabase("Exercise.accdb", dbLangGreek)
End Sub

The third argument of the CreateDatabase() method is used to pass some options to use when creating the database. This optional argument can be a constant specified as dbEncrypt, if you want the database to be encrypted. If you don't pass this constant, the database would not be encrypted.

If you want to specify the version of Microsoft Jet used in the new database, the options argument can be one of the following values: dbVersion10, dbVersion11, dbVersion20, or dbVersion30. If you don't specify one of these values, the dbVersion30 would be used.

If you want the database to be encrypted and you want to specify the version of Microsoft Jet used in the new database, combine the dbEncrypt constant with one of the version values.

Getting a Reference to a Created Database

When the CreateDatabase() method has finished, it returns a reference to the database that was created. In fact, you must always obtain that reference. The database is recognized as the Database object of the workspace. To get it, first declare a variable of type Database or Object. Here is an example:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Database
    
End Sub

To get a reference to the new database, use the Set operator and assign the returned value of the method to your Database variable. Here is an example that creates a new database named Exercise.accdb in the current folder:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Database
    
    Set dbExercise = DBEngine.Workspaces(0).CreateDatabase("Exercise.accdb", dbLangGeneral)
End Sub

We mentioned that, when you start a database, the DBEngine object is automatically made available to you and it is equipped with a default workspace whose index is 0. Because these are already available, you do not have to include them in your statements if you want to use only the defaults. Therefore, you can omit them when creating a database. The above code could be written as:

Private Sub cmdDatabase_Click()
    Dim dbExercise As Database
    
    Set dbExercise = CreateDatabase("Exercise.accdb", dbLangGeneral)
End Sub

And it would produce the same effect.

Opening a Database

Once a database exists, you can open it. To support the ability to open a database, the Workspace class is equipped with a method named OpenDatabase. Its syntax is:

Public Function Workspace.OpenDatabase(ByVal Name As String, _
				       ByVal Options As Boolean, _
				       ByVal ReadOnly As Boolean, _
				       ByVal Connect As String) As Database

Only the first argument is required and it is passed as a string. You can pass the name of the database file with its extension. Here is an example:

Private Sub cmdOpenDatabase_Click()
    DBEngine.Workspaces(0).OpenDatabase "Example.accdb"
End Sub

After opening the database, if you want to work on it, you should get a reference to it when opening it. To allow you to do this, the OpenDatabase() method returns a Database object. Here is an example:

Private Sub cmdOpenDatabase_Click()
    Dim dbExample As Database
    
    Rem Open the Example.accdb database and get a reference to it
    Set dbExample = DBEngine.Workspaces(0).OpenDatabase("Example.accdb")
    
    . . . Use the dbExample database as you see fit

End Sub

When opening the database, you can lock it so that other people or applications cannot access it at the same time with you. To prevent other items (they are called processes) from accessing the database, pass a second argument as True. On the other hand, you may want to allow other people or applications to be able to access the same database. To specify this option, pass the second argument as False.

Like the second argument, the third is optional. If you are opening the database and want to do something on it, such as modifying it, pass the third argument as False. If you don't want to perform any modification on the database, pass the third argument as True.

The fourth argument allows you to provide connection information.

Closing a Database

While a database is being used, it consumes computer resources such as memory. After using it, you should close the database to free the resources it was using and make them available to other applications.

To provide the ability to close a database, the Database class is equipped with a method named Close. Its syntax is:

Database.Close()

As you can see, this method does not take any argument. Before calling it, make sure you get a reference to the database to be closed. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim dbExercise As Database
    
    Set dbExercise = CreateDatabase("Exercises1.accdb", dbLangGeneral)
    dbExercise.Close
End Sub

When calling this method, if you try to close a database that is currently closed, you would receive a 2467 error.

The Tables of a MAOL Dadabase

Introduction

To programmatically create a table using the Microsoft Access Object Library, you can first declare a variable of type Object and then initialize it with the CreateTableDef() method of the current database object. This method takes as argument the name of the new table. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
End Sub

After creating the table, you must add it to the current database. To support this, the CurrentDb object is equipped with the TableDefs property. TableDefs is in fact a collection. The TableDefs collection is equipped with the Append() method that is used to add a new table to the current database. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    . . .
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

A Reference to a Table

In most cases, before performing an operation on a table, you will need to indicate what table you are referring to. This is usually easy to the user who can visually see the table. As for you as the database developer, you can first programmatically get a reference to the table you intend to work on.

If a table exists already, to get a reference to it using the Microsoft Access Object library, first declare a variable of type Object. Then, assign the TableDefs property of the current database to the variable. TableDefs is a collection of the tables of the current database. To specify what table you are referring to, you can pass its name, as a string to the TableDefs indexed property. Here is an example:

Private Sub cmdGetReference_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Students
    Set tblStudents = curDatabase.TableDefs("Students")
    
End Sub

Instead of using its name, you can also pass the numeric index of the table to the TableDefs property.

Deleting a Table

To delete a table using the Microsoft Access Object Library, pass the name of the undesired table to the Detele() method of the TableDefs property of the database. Here is an example from the Microsoft Access Object Library:

Private Sub cmdDeleteTable_Click()
    Dim curDatabase As Object

    Set curDatabase = CurrentDb

    curDatabase.TableDefs.Delete "Books"
End Sub

Creating a Query

To programmatically create a query using the Microsoft Access Object Library, you can use the CreateQueryDef() method of the Database (or Object) object. The syntax of this method is:

CreateQueryDef(NewQueryName, SQLStatement)

The first argument is the name you will give to the new query. The second argument is a SQL statement that specifies the contents of the query. Here is an example:

Private Sub cmdCreateQuery_Click()
    Dim curDatabase As Object
    Dim qryCustomers As Object
    Dim strStatement As String

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    strStatement = "SQL Statement"
    
    ' Create a new query named CustomersRecords
    Set qryCustomers = curDatabase.CreateQueryDef("CustomersRecords", strStatement)
    
    Set qryCustomers = Nothing
End Sub

Introduction to the Columns of a Table

A Column as a Field

As mentioned already, a table is a list of categories of values. A category is also called a field. To give you access to the collection of fields of a table, in the Microsoft Access Object Library, the TableDef object is equipped with a property named Fields that is of type Fields and each one of its items is an object of type Field.

Creating a Field

We saw how to start a programmatic creation of a table in the Microsoft Access Object Library as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    . . .
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

To support the creation of a column using the Microsoft Access Object Library, its table is equipped with a method named CreateField. This method takes three arguments and its syntax is:

Set field = object.CreateField(ByValye ColumnName As String, _
			       ByVal DataType As FieldType, _
			       ByVal Size As Integer)

Before calling this method, declare a variable of type Object for the new column. When calling this method, get a reference to it and assign it to the variable you would have created for the column. This would be done as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField(ColumnName, DataType, Size)
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

None of the arguments to the CreateField() method is required.

The first argument of the CreateField() method, name, specifies the name of the new column. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

If you call the method without specifying the name of the column, at one time or another before actually creating the field, you will have to set its name. Otherwise you will receive an error. To support this, the Field class has a property named Name. Therefore, to specify the name of the column, access its Name property and assign the desired string. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students1")
    
    Set fldFullName = tblStudents.CreateField
    fldFullName.Name = "FullName"
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

The second argument to the CreateField() method specifies the data type that would be applied on the column. We will review data types in other sections. Once again, the argument is optional. If you omit it when calling the method, you must specify a data type before actually creating the field. To support this, the Field class is equipped with a property named Type. Therefore, to specify the data type of a column, assign the desired type to its Type property.

The third argument to the CreateField() method holds a piece of information that has to do with either the computer memory or the number of characters (later).

After creating a column, you must add it to the table. To support this, the table object is equipped with a property named Fields, which is a collection. We saw that the collections classes are equipped with a method named Add. The Fields collection of the table is equipped with an equivalent method but named Append. This method takes as argument an object that represents the collection. Here is an example

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

Deleting a Column

If you have a column you don't need anymore on a table, you can remove that column. To programmatically delete a column, if you are using the Microsoft Access Object Library, call the Delete() method of the TableDef object and pass it the name of the column. The syntax of this method is:

TableDef.Fields.Delete ColumnName

In this formula, replace ColumnName with the name of the column you want to delete. Here is an example:

Private Sub cmdModifyPersons_Click()
    Dim curDatabase As Object
    Dim tblPersons As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Get a reference to a table named Customers
    Set tblPersons = curDatabase.TableDefs("Persons")
    
    tblPersons.Fields.Delete "DateHired"
End Sub

Before deleting a column, make sure it exists, otherwise, you would receive a 3265 error:

Error 3265

Even if the column exists, before deleting a column, make sure its table is closed. Otherwise, you would receive a 3211 error. You can check these issues using error handling.

The Data Type of a Column

Text-Based Fields

We saw how to start the creation of a field in the Microsoft Access Object Library as follows:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", DataType, Size)
    
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

The second argument of the CreateField() method, which is required, must be the data type applied on the column. To create a text-based field, the second argument can be dbText. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("colFullName", dbText)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

You can also specify the data type as DB_TEXT. The dbText or DB_TEXT type is equivalent to the Text data type used in Microsoft Access.

To specify the maximum number of characters a text-based column can hold, provide a third argument to the CreateField() method and enter the desired number. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("FullName", dbText, 120)
    tblStudents.Fields.Append fldFullName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

Creating Integral Fields

If you are creating a field that will hold small natural numbers, set its data type as either DB_BYTE or dbByte. If you are creating a field that would general natural numbers, you can set its data type as DB_INTEGER or dbInteger. If you are creating a field that can contain very large numbers, set its data type as DB_LONG or dbLong.

Creating a Boolean Field in the Microsoft Access Object Library

If you are programmatically creating the column on a table using the Microsoft Access Object Library and the column would be Boolean-based, set its data type as dbBoolean. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colIsMarried As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    Set colIsMarried = tblEmployees.CreateField("IsMarried", dbBoolean)
    tblEmployees.Fields.Append colIsMarried
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblEmployees
End Sub

You can also use the DB_BOOLEAN type.

Single-Precision Numeric Fields

If you are creating a table using the Microsoft Access Object Library and you want a field that can handle regular decimal values without regards to precision, specify its data type as dbSingle or DB_SINGLE.

Double-Precision Numeric Value Fields

To create a field that can hold decimal numbers with double-precision, specify its data type as dbDouble or DB_DOUBLE. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

Binary Fields in Microsoft Access Object Library

If you are creating a field that would accept binary values (such as hexadecimal numbers), specify its data type as DB_BINARY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblBooks As Object
    Dim colShelf As Object

    Set curDatabase = CurrentDb
    Set tblBooks = curDatabase.CreateTableDef("Books")
    
    Set colShelf = tblBooks.CreateField("Shelf", DB_BINARY)
    tblBooks.Fields.Append colShelf
    
    curDatabase.TableDefs.Append tblBooks
End Sub

Currency Fields

If you are programmatically creating the column in Microsoft Access Object Library and you want it to hold monetary values, set its data type as dbCurrency or DB_CURRENCY. Here is an example:

Private Sub cmdTableCreator_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colWeeklyHours As Object
    Dim colHourlySalary As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT)
    tblEmployees.Fields.Append colFullName
    Set colWeeklyHours = tblEmployees.CreateField("WeeklyHours", DB_DOUBLE)
    tblEmployees.Fields.Append colWeeklyHours
    Set colHourlySalary = tblEmployees.CreateField("HourlySalary", DB_CURRENCY)
    tblEmployees.Fields.Append colHourlySalary
    
    curDatabase.TableDefs.Append tblEmployees
End Sub

Date/Time Fields

To programmatically create a column that would hold date/time values using the Microsoft Access Object Library, set its data type to DB_DATE. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colFullName As Object
    Dim colDateHired As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colFullName = tblEmployees.CreateField("FullName", DB_TEXT, 80)
    tblEmployees.Fields.Append colFullName
    Set colDateHired = tblEmployees.CreateField("DateHired", DB_DATE)
    tblEmployees.Fields.Append colDateHired
    
    curDatabase.TableDefs.Append tblEmployees
    
    MsgBox "A table named Employees has been created."
End Sub

You can also use the dbDate data type.

Other Topics on Fields

Automatically Incrementing the Value of a Field

To support the ability to automatically increment the integral values of a field, the Attributes property of the Field class of the Microsoft Access Object Library, through the Object class provides a constant named dbAutoIncrField. To apply this attribute, first create the field. Then, access its Attributes property and assign dbAutoIncrField to it. Here is an example:

Private Sub cmdTable_Click()
    Dim curDatabase As Object
    Dim tblEmployees As Object
    Dim colEmployeeID As Object
    Dim colFullName As Object

    Set curDatabase = CurrentDb
    Set tblEmployees = curDatabase.CreateTableDef("Employees")
    
    Set colEmployeeID = tblEmployees.CreateField("EmployeeID", dbLong)
    colEmployeeID.Attributes = dbAutoIncrField
    tblEmployees.Fields.Append colEmployeeID
    Set colFullName = tblEmployees.CreateField("FullName", dbText)
    tblEmployees.Fields.Append colFullName
    
    curDatabase.TableDefs.Append tblEmployees
    MsgBox "A table named Employees has been created"
End Sub

Setting a Default Value for a Field

To support the default value, the Field class of the Microsoft Access Object Library is equipped with a property named DefaultValue. When creating a field, get its reference from calling the TableDef.CreateField() method. With that reference, access its DefaultValue property and assign the desired value to it. The value must be a string for a text-based field. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFullName As Object
    Dim fldGender As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFullName = tblStudents.CreateField("FullName", dbText, 120)
    tblStudents.Fields.Append fldFullName
    
    Set fldGender = tblStudents.CreateField("Gender", dbText, 20)
    fldGender.DefaultValue = "Female"
    tblStudents.Fields.Append fldGender
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

After specifying a default value for a column, during data entry, you or the user can skip that field. If you skip the field, the field would receive the default value as its value.

The Nullity of a Field

To support the nullity of a value, the Field class of the Microsoft Access Object Library is equipped with a Boolean property named Required. By default, the value of this property is False, which means the user can skip the field during data entry. If you set this property to True, the user must enter a value for the field. Here is an example:

Private Sub cmdCreateTable_Click()
    Dim curDatabase As Object
    Dim tblStudents As Object
    Dim fldFirstName As Object, fldLastName As Object

    ' Get a reference to the current database
    Set curDatabase = CurrentDb
    ' Create a new table named Students
    Set tblStudents = curDatabase.CreateTableDef("Students")
    
    Set fldFirstName = tblStudents.CreateField("FirstName", dbText)
    tblStudents.Fields.Append fldFirstName
    
    Set fldLastName = tblStudents.CreateField("LastName", dbText)
    fldLastName.Required = True
    tblStudents.Fields.Append fldLastName
    
    ' Add the Students table to the current database
    curDatabase.TableDefs.Append tblStudents
End Sub

During data entry, if a value is not provided for the field, the compiler would produce a 3314 error:

Error 3314

Microsoft Office Access Database Engine Object

Introduction

Microsoft Office 2010 ships with a library that can be used to perform all types of operations on a database and used throughout all applications of the Microsoft Office family. This library is called Microsoft Office Access Database Engine Object (We will refer to it as MOADE). Like every library, it has a version. In Microsoft Office 2010, it is the Microsoft Office 14.0 Access Database Engine Object Library.

The Microsoft Office 14.0 Access Database Engine Library is loaded by default when you start a new database in Microsoft Access. To check it, on the main menu of Microsoft Visual Basic, click Tools -> References...:

References

If you don't want to use the library, you can remove it from your database. To do that, simply remove the check mark on its check box.

Using the MOADE Library

Like the other database libraries, the Microsoft Office 14.0 Access Database Engine Library relies on many objects and collections. The most top object of the Microsoft Office 14.0 Access Database Engine Library is called Access. One of the properties of the Access object is called Application. To reference the Microsoft Office 14.0 Access Database Engine Library in your application, declare a variable of type Access.Application. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
End Sub

After declaring the variable, you must initialize it using the Set operator. To do this, call the CreateObject() function. Its syntax is:

CreateObject(class,[servername])

This method takes two arguments. The first argument is the name of a class. For Microsoft Access, the class is Microsoft.Access. This class can be followed by the version number. For example, Microsoft Office Access 2010's version is 14. In this case, the class would be specified as Access.Application.14. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application.14")
End Sub

In reality, you can pass the argument as Access.Application without the version. If you do this, the database engine would find out about the latest installation of Microsoft Access in your computer using the drivers installed.

When calling the CreateObject() method, only the first argument is required. If you are trying to use a database that resides on a server, pass the name of that server as the second argument.

MOADE and Databases

Creating a Database

To support the creation of a database, the Access.Application object is equipped with a method named NewCurrentDatabase. Its syntax is:

Access.Application.NewCurrentDatabase(filepath, FileFormat, Template, SiteAddress, ListID)

Only the first argument is required. You can pass it as the name of the new database. Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.NewCurrentDatabase "Red Oak High School"
End Sub

If you do not specify an extension, the database engine would find out the latest version of Microsoft Access that is installed in your computer. If it is Microsoft Access 2007 or 2010, a new database with extension .accdb would be created. Still, if you want, you can add an extension. If you have Microsoft Access 2010 but want to specify the version of database you want to create, pass the second argument. The second argument is a member of the AcNewDatabaseFormat enumeration. The available values are:

AcNewDatabaseFormat Member	Constant Value	Description
acNewDatabaseFormatUserDefault	0	The database engine will use the current installation of Microsoft Access
acNewDatabaseFormatAccess2000	9	A Microsoft Access database compatible with versions 2.0, 95, 97, or 2000 will be created with the extension .mdb
acNewDatabaseFormatAccess2002	10	A Microsoft Access 2002-2003 compatible database will be created with the extension .mdb
acNewDatabaseFormatAccess12	12	A Microsoft Office Access 2007 compatible database will be created with the extension .accdb
acNewDatabaseFormatAccess14	14	A Microsoft Office Access 2010 compatible database will be created with the extension .accdb

Here is an example:

Private Sub cmdCreateDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.NewCurrentDatabase "Red Oak High School", acNewDatabaseFormatAccess2010
End Sub

The other arguments are optional.

Opening a Database

Once a database has been created, in order to use it, you must open it. To open a database in the Microsoft Office 14.0 Access Database Engine Library, you can call the OpenCurrentDatabase() method of the Access.Application class. Its syntax is:

Access.Application.OpenCurrentDatabase(filepath, Exclusive, bstrPassword)

The first argument can be the name of the database. If you provide (only) the name of the database, the engine would look for it in the same directory as the application that called it. Otherwise, you should provide the complete path to the database.

Closing a Database

After using a database, you should close it to free the resources it was using. To close a database using the Microsoft Office 14.0 Access Database Engine Library, call the CloseCurrentDatabase() method. Its syntax is:

Access.Application.CloseCurrentDatabase()

As you can see, this method takes no argument. After calling this method, set its variable to Nothing. Here is an example:

Private Sub cmdCloseDatabase_Click()
    Dim ROSH As Access.Application
    
    Set ROSH = CreateObject("Access.Application")
    ROSH.CloseCurrentDatabase
    
    Set ROSH = Nothing
End Sub

When calling this method, the database must have been previously opened. If the database was not opened or it was already closed, you would receive a 2467 error.

Error