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
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.
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.
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.
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
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
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
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.
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
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.
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:
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...:
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.
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.
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:
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.
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.
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.
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
