Suppose that you want to automate the creation of a new Access database. With a new Excel workbook
open
, begin by establishing a VBA module. Choose Tools, Macro, Visual Basic Editor. With the VBE active, create a new module by choosing Insert, Module.
To create a database using DAO, you need to establish a reference to the DAO object library. Choose Tools, References and scroll down until you see a Microsoft DAO Object Library. The highest-numbered version is the one to use, and the versions available on your system depend on which version of Office you have installed. For Office 2003, that's Microsoft DAO 3.6 Object Library; for Office 1997, it's DAO 3.5. Fill its check box and click OK.
After establishing the necessary reference, enter this code into the new module:
You would call this subroutine by means of another statement, one that both invokes the subroutine and that
passes
the database name to it. For example, if you wanted the database to be named
Crash Carts.mdb
, the statement might be
The critical part of the subroutine is the statement that actually creates the database. Its full syntax is
After they've used your code to create a new database, your users will surely need to put one or more tables in it. The approach shown in this section works for any existing Access database, whether or not you've just created it.
As you'll see, you can't save a new table in an existing database without putting at least one field in the table. However, you can save a new database without
putting
any tables in it. This is consistent with the process of creating a database using the Access interface: An empty database is
perfectly
legal, but a table devoid of any fields isn't.
As usual, you'll need a module that has a reference set for a DAO library. With the reference set, you enter the following code in a module. The code shown here is the minimum code needed to create a table in an existing database using DAO:
Sub MakeNewTableWithDAO(DatabaseName As String, _
TableName As String, FieldName As String)
Dim dbBackupData As DAO.Database
Dim tdBackupTable As DAO.TableDef
Dim fldBackupField As DAO.Field
Set dbBackupData = OpenDatabase(DatabaseName)
Set tdBackupTable = dbBackupData.CreateTableDef(TableName)
Set fldBackupField = tdBackupTable.CreateField(FieldName, _
dbText, 45)
tdBackupTable.Fields.Append fldBackupField
dbBackupData.TableDefs.Append tdBackupTable
End Sub
You might well want to integrate this code with the code that creates the database itself. For example
DatabaseName="SalesBackup.mdb"
TableName="GiftShopSales"
FieldName="SalesAmount"
CreateDatabaseWithDAO DatabaseName
MakeNewTableWithDAO DatabaseName, TableName, FieldName
This code passes the same database name to both procedures, ensuring that when the tables are created and the fields appended, they go into the database that was created with the
CreateDatabaseWithDAO
subroutine.
NOTE
Suppose that your Access database is open when you run the code that creates a new table, and that the Tables tab is active. If you now switch to Access, you might not see the name of the new table. This is because you need to refresh the list of tables in the database. Click another tab, such as Queries, and then return to the Tables tab to see the new table.
Getting
Names
from the User
Of course, you avoid the static-name approach in the workbook you have under development. It's seldom wise to assign static values to string variables (or to
variables
of any other data type, for that matter) in the code itself. But the code shown earlier does just that to the names of the database, the table, and the table's field.
One good alternative, which allows the user some realistic flexibility in naming the objects, is to pick up the names from the user's workbook. You could obtain the name of the database from the name of the workbook, the table name from the name of the active worksheet, and the name of the field from a column header.
Eventually the user will enter data on a worksheet in the workbook you're developing. Suppose that the worksheet appears as shown in Figure 10.1.
Now you could use code like this to tailor the database to the way that the user has structured the worksheet:
Option Explicit
Option Base 1
Option Base 1
is one way to ensure that VBA arrays refer to their first element as 1. The default is 0, so unless you arrange
otherwise
, the first element of an array is its zero-th. If you use
Option Base 1
at the top of a VBA module, all arrays declared in that module will refer to their first element as element number 1.
Type FieldType
FieldName As String
FieldLength As Integer
End Type
VBA supports
user-defined data types
. That is, you can declare a variable as something other than
String
,
Integer
,
Date
, or
Variant
. If you've already defined your own type, you can subsequently declare a variable to be of that type.
In many cases, this comes about because you want to mix two incompatible data types in an array. In the current example, it will be useful to have an array that contains information about the names of fields (text data) and about the length of each field (numeric data).
An alternative is to declare a variable of type
Variant
, which can store any mix of data types. But that's usually a lazy approach and it denies you one of the benefits of declaring a type: named elements.
By declaring a type -here,
FieldType
-at the outset, the code can later declare an array of type
FieldType
. There are several advantages to doing so. In this case, the two most important reasons are
-
It's possible to have a single array that consists of one column of text data and one column of numeric data.
-
It's easy and
self-documenting
to refer to an element called
FieldName
and to one called
FieldLength
. In contrast, if you declared the array as
Variant
, you'd have to refer to the first column and to the second column by number instead of by name, and you'd have to remember which is which. A year or two from now, someone else might have to maintain this code. Considering the difficulty you'd be saddling him with, you'd do well to stay out of dark alleys.
After declaring the options that are in effect and a user-defined data type, you continue by developing the subroutines
themselves
. You use a subroutine named
Driver
to initiate the processing. Its code follows:
Sub Driver()
Dim DatabaseName As String
Dim LockFileName As String
Dim TableName As String
Dim FieldCount As Integer
Dim i As Integer
Dim GoAhead As Boolean
Dim FieldArray() As FieldType
Note that
FieldArray
is dimensioned with a pair of empty parentheses, and declared as
FieldType
. The implications of this, along with
ReDim
, are discussed later in this section.
After declaring several variables, you arrange to store the names picked up from the Excel workbook in those variables. This approach avoids the problems created by working with static, constant names.
DatabaseName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
DatabaseName = ThisWorkbook.Path & "\" & DatabaseName & ".mdb"
LockFileName = ThisWorkbook.Path & "\" & DatabaseName & ".ldb"
You store the name of the workbook in the
DatabaseName
variable. Notice that the
Left
function is used in conjunction with the
Len
function to strip off the four rightmost characters in the workbook's name, and the remaining
characters
are stored in
DatabaseName
. So, if the workbook's name is DBTemplate.xls, the characters ".xls" are removed and the remaining string,
DBTemplate
, is stored in
DatabaseName
.
Also, the path of the workbook that contains the code, as well as a backslash, are prepended to the database name. This ensures that whatever path Excel currently considers to be the default, the database will be created in the same path as the workbook.
Then
LockFileName
is assigned the value of
DatabaseName
plus the characters ".ldb" and the characters ".mdb" are appended to the end of
DatabaseName
. A later procedure uses these values to test for the existence of a database with the current name (for example,
DBTemplate.mdb
).
TableName = ActiveSheet.Name
You assign the name of the active sheet to the variable
TableName
. If the active sheet's name is
Sheet1
, the database table created by the code will also be
Sheet1
.
Counting Fields
You now need to determine the number of fields to put in the table. Begin with the following statement:
FieldCount = ActiveSheet.Cells(1, 256).End(xlToLeft).Column
The number of fields that will be put in the database table is determined by the number of the rightmost column in row 1 that has a value.
NOTE
This statement isn't quite true. The
End(xlToLeft)
method emulates what happens when you select a cell, hold down the Ctrl key, and press the left arrow. Suppose that you start with cell IV1. If IV1 is empty, Excel goes left until it finds a nonempty
cell
and stops there. If IV1 isn't empty but IU1 is empty, Excel heads left until it finds a
nonempty
cell. If IV1 isn't empty and
neither
is IU1, Excel heads left until it finds the last contiguous nonempty cell. The code makes the assumption (a valid one in most real-life situations) that cell IV1 is empty; therefore, Excel heads left until it finds the first nonempty cell -assumed here to be in the rightmost column of a list.
Note the assignment statement for
FieldCount
. It instructs VBA to start in the first row, 256th column of the active sheet and go left until it finds a used cell. The assumption is that the number of the column containing that cell gives the number of fields to create.
Ideally, the user has created a list that occupies, say,
columns
A through G and an indeterminate number of rows. Then the code finds that cell G1 isn't empty. Because G is the seventh column,
FieldCount
is assigned the value 7.
Maintaining Flexibility with
ReDim
The
next
statement redimensions
FieldArray
according to the value of
FieldCount
:
ReDim FieldArray(FieldCount)
ReDim
is an interesting and useful command in VBA. Its value
stems
in part from the fact that you cannot use a variable as an argument to a
Dim
statement.
For example, suppose -as is the case -that the code has by this point determined the value of
FieldCount
, the number of fields to put in the database table. The next task is to put the names of those fields into the memory array
FieldArray
. But it has not yet dimensioned
FieldArray
; that is, the code has not yet stated that
FieldArray
contains three rows, or six rows, or some other number of rows.
If the code now dimensioned
FieldArray
with this statement:
Dim FieldArray(FieldCount)
the code would fail with an error:
Constant expression required
. In other words, a constant value is needed in place of the variable
FieldCount
.
But
ReDim
is intended
specifically
for this
sort
of situation. This statement
ReDim FieldArray(FieldCount)
assigns
as many columns to the array
FieldArray
as the value of
FieldCount
.
TIP
Before you can redimension an array with
ReDim
, you must have already declared the array with
Dim
. Furthermore, in the
Dim
statement, the array's name must be followed by a pair of empty parentheses.
For i = 1 To FieldCount
FieldArray(i).FieldName = ActiveSheet.Cells(1, i)
Next i
This loop puts each value in the worksheet list's header row into the
FieldName
element of
FieldArray
. (Recall that the first row of a list in a worksheet contains the names of the variables, or fields, that when taken together make a list.)
Completing the Field Definitions
The next task is to determine the length of each field in the database. This implies that each field is a
Text
field. It would be possible to include tests to determine whether a field in the list is numeric, and if so, whether it is single or double precision, a date, currency, and so on. So doing would introduce considerable complications, and you decide to assume that all the user's fields will contain text. (A
Text
field in an Access table can contain numeric types and
subtypes
, although it stores them
as
text. This is analogous to typing
'4
in an Excel cell: It looks like a number, but it's stored as text.)
The following loop checks for the maximum length of each field in the Excel list and stores it in the
FieldLength
element of
FieldArray
, where it's associated directly with the name of the field.
For i = 1 To FieldCount
RecordCount = ActiveSheet.Cells(65536, i).End(xlUp).Row
For j = 2 To RecordCount
If Len(ActiveSheet.Cells(j, i)) > FieldArray(i).FieldLength Then
FieldArray(i).FieldLength = Len(ActiveSheet.Cells(j, i))
End If
Next j
Next i
You use this nested loop to get the number of records in each field of the worksheet list. Suppose that the final record (or even the final two or three records) in the list is missing a value on one or more fields. In that case, checking the number of rows used in only one column might result in an undercount of the number of records in another column. Therefore, you arrange for the loop to check the number of rows used in each column and store that value in
RecordCount
.
The inner loop uses the current value of
RecordCount
to determine how many cells to check in the current column. The length of each cell is checked by means of the
Len
function. If the result is larger than the current value of that field's
FieldLength
element, it
replaces
that current value. By the time the loop has
looked
at the final cell in the current column, it has determined the length of the longest value in the column and stored it in the
FieldLength
element of
FieldArray
.
CreateDatabaseWithDAO DatabaseName, LockFileName, GoAhead
Now the main
Driver
subroutine calls the subroutine named
CreateDatabaseWithDAO
, discussed in the next section. Notice the use of the Boolean variable
GoAhead
as an argument that's passed to the subroutine. Thus far, your
Driver
subroutine has done nothing with the variable. But it's passed to
CreateDatabaseWithDAO
, which might modify its value. When control returns to the
Driver
subroutine,
GoAhead
can be used to determine whether to continue by adding a table to a database.
The point to bear in mind is that the value of a variable can be modified by a called subroutine. It is that modified value that is subsequently available to the calling subroutine.
NOTE
This behavior is the default, and is termed
passing by reference
. When, as here, a variable is passed by reference, its value may be modified by a called procedure. The modified value is returned to the calling procedure. If you override the default behavior and pass the variable
by value
, the called procedure might modify the variable's value but the changed value is not returned to the calling procedure.
After the new database has been created, the
Driver
subroutine concludes with the following statements:
If GoAhead Then
MakeNewTableWithDAO DatabaseName, TableName, FieldName()
End If
End Sub
Your code first checks the value of
GoAhead
. If it's TRUE, that means a new database has been created and the code can continue by putting a new table into it. After that's accomplished, you end the main
Driver
subroutine and processing has finished.
The
Driver
subroutine is a lengthy one. It's shown here in its entirety to make it easier to follow its structure by viewing it all at once, instead of broken up by
explanations
.
Sub Driver()
Dim DatabaseName As String x
Dim LockFileName As String x
Dim TableName As String x
Dim FieldArray() As FieldType x
Dim FieldCount As Integer x
Dim RecordCount As Long x
Dim i As Integer x
Dim j As Integer x
Dim GoAhead As Boolean x
DatabaseName = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
LockFileName = ThisWorkbook.Path & "\" & DatabaseName & ".ldb"
DatabaseName = ThisWorkbook.Path & "\" & DatabaseName & ".mdb"
TableName = ActiveSheet.Name
FieldCount = ActiveSheet.Cells(1, 256).End(xlToLeft).Column
ReDim FieldArray(FieldCount)
For i = 1 To FieldCount
FieldArray(i).FieldName = ActiveSheet.Cells(1, i)
Next i
For i = 1 To FieldCount
RecordCount = ActiveSheet.Cells(65536, i).End(xlUp).Row
For j = 2 To RecordCount
If Len(ActiveSheet.Cells(j, i)) > FieldArray(i).FieldLength Then
FieldArray(i).FieldLength = Len(ActiveSheet.Cells(j, i))
End If
Next j
Next i
CreateDatabaseWithDAO DatabaseName, LockFileName, GoAhead
If GoAhead Then
MakeNewTableWithDAO DatabaseName, TableName, FieldArray()
End If
End Sub
Checking for a Preexisting Database
Your
Driver
subroutine calls another subroutine named
CreateDatabaseWithDAO
.
Sub CreateDatabaseWithDAO(DatabaseName As String, _
LockFileName As String, GoAhead As Boolean)
It calls this subroutine with three arguments:
-
DatabaseName
- This is the name with which the database will be saved, including the
.mdb
extension. As you've seen in the
Driver
subroutine,
DatabaseName
is based on the name of the active Excel workbook.
-
LockFileName
- This is the name of the database with the extension
.ldb
instead of
.mdb
. Under most circumstances, when an Access database is open, a lock file also exists. The lock file's name is the name of the database and the extension
.ldb
. This subroutine checks for the existence of a lock file before it attempts to erase an existing database.
-
GoAhead
- This Boolean variable is used to determine whether it's okay to create the new database. If
GoAhead
is FALSE, the database won't be created. The user can set
GoAhead
to FALSE by declining to allow the subroutine to create the database; the code can set it to FALSE if the database already exists and is open.
NOTE
The sole instance in which Access opens an Access database without also creating a lock file (or using an existing lock file) is when a user has opened the database as read only and in Exclusive mode.
There is a situation in which no one is using an Access database, and yet the lock file still exists. That occurs when the final user has closed the database, and that user doesn't have Delete permission for files in its folder. In that case, the lock file remains in place until some other user, one who does have Delete permission, is the final user to close the database.
The next several lines of code determine what to do if a file with a name identical to that stored in the variable
DatabaseName
already exists. Begin by setting the
GoAhead
Boolean variable to TRUE. Unless the code changes its value to FALSE, the database will be created at the end of the subroutine.
GoAhead = True
The
Dir
function checks to see whether a file matching its argument's value already exists in the current directory. If such a file does exist,
Dir
returns the filename; if one does not exist,
Dir
returns a null string (represented in code as an empty pair of quote marks). So, if the value that
Dir
returns is something other than a null string, the file already exists and further handling is necessary.
If Dir(DatabaseName) <> "" Then
If MsgBox(Prompt:="Delete existing " & DatabaseName & "?", _
Buttons:=vbYesNo, Title:="Naming conflict.") = vbYes Then
If the
Dir
function finds a file named DatabaseName in the current directory -that is, if it returns a value other than a null string -then the code displays a message box asking the user whether to delete the existing file. A message box can return a value. In this case, the buttons displayed in the message box are a Yes button and a No button. If the user clicks the Yes button, the value returned by the message box is
vbYes
; if the user clicks the No button, the message box returns
vbNo
.
If the user does click the Yes button, the code continues by checking for the existence of a lock file. The reason is that if the existing file named DatabaseName is an Access database, and if it's open, the code will terminate with an error if it
tries
to delete the file. By checking for the existence of a file named LockFileName, it's very likely that the file named DatabaseName can be deleted without
causing
an error.
"Very likely" isn't quite good enough, though, and the code includes an error handler.
If Dir(LockFileName) <> "" Then
MsgBox Prompt:=DatabaseName & " is already open and " _
& "cannot be erased. Taking no action.", _
Title:="File already open."
GoAhead = False
The code finds that the lock file exists and therefore the database not only exists but is open. It notifies the user and sets
GoAhead
to FALSE.
Else
On Error GoTo Recover
Kill DatabaseName
On Error GoTo 0
GoAhead = True
End If
If the code does not find the lock file, it prepares to delete the file named DatabaseName. However, as noted earlier, it's possible that the database is open even if no lock file is found. Therefore, an error handler is established.
The code is instructed, by means of the
On Error GoTo
statement, to transfer control to the code following the
Recover
label if an error occurs. This would come about if the code should attempt in vain to delete the file DatabaseName in the
Kill
statement. In that event, the code shown at the end of the subroutine executes.
Notice the
On Error GoTo 0
statement. This cancels the directive of the
preceding
On Error
statement. The error handler following the
Recover
label is intended only for an error caused by the
Kill
statement. If the code gets past that statement without an error, the handler is irrelevant and so error handling is returned to its normal status.
Else
MsgBox Prompt:="Okay, leaving existing " & DatabaseName _
& " alone.", Title:="Taking no action."
GoAhead = False
End If
End If
The prior six statements complete the logic of the first
If
block, which asks the user what to do if an instance of
DatabaseName
already exists. If your user clicks the No button in the first message box, it means to leave the file alone, and the
GoAhead
variable is set to FALSE.
If GoAhead Then
CreateDatabase Name:=DatabaseName, Locale:=dbLangGeneral
End If
Exit Sub
The code checks the value of
GoAhead
. If it's still TRUE, a database is created with the name
DatabaseName
. It is as yet empty. Note the
Exit Sub
statement following the
End If
. If this statement is reached, control returns immediately to the procedure that called the subroutine (as you've seen, that procedure is the
Driver
subroutine).
The reason for departing the subroutine via the
Exit Sub
statement is that the remaining code is the error handler. It shouldn't run unless an error has occurred, so in the normal course of events, control returns to
Driver
before the error handler executes. But if an error did occur, the following code runs:
Recover:
MsgBox Prompt:="Could not delete " & DatabaseName & _
". It's likely that a user has opened it in exclusive " _
& "mode and read only. Taking no action.", Title:= _
"File already open."
GoAhead = False
End Sub
The error handler simply informs the user that the code could not delete the existing instance of the file named DatabaseName, and then sets
GoAhead
to FALSE. It's worth noting that
Recover:
isn't an executable statement, but is merely a line label. As such, it must begin in the first column (labels cannot be indented), must be the only instance of that particular label in the procedure, and must end with a
colon
.
Here's the full procedure:
Sub CreateDatabaseWithDAO(DatabaseName As String, _
LockFileName As String, GoAhead As Boolean)
GoAhead = True
If Dir(DatabaseName) <> "" Then
If MsgBox(Prompt:="Delete existing " & DatabaseName & "?", _
Buttons:=vbYesNo, Title:="Naming conflict.") = vbYes Then
If Dir(LockFileName) <> "" Then
MsgBox Prompt:=DatabaseName & " is already open and " _
& "cannot be erased. Taking no action.", _
Title:="File already open."
GoAhead = False
Else
On Error GoTo Recover
Kill DatabaseName
On Error GoTo 0
GoAhead = True
End If
Else
MsgBox Prompt:="Okay, leaving existing " & DatabaseName _
& " alone.", Title:="Taking no action."
GoAhead = False
End If
End If
If GoAhead Then
CreateDatabase Name:=DatabaseName, Locale:=dbLangGeneral
End If
Exit Sub
Recover:
MsgBox Prompt:="Could not delete " & DatabaseName & _
". It's likely that a user has opened it in exclusive " _
& "mode and read only. Taking no action.", Title:= _
"File already open."
GoAhead = False
End Sub
Creating a New Table in the Database with DAO
By now your code has created the new database using the
CreateDatabaseWithDAO
subroutine. Your
Driver
subroutine has collected information about the table the database will contain and the fields the table will contain. It's time to actually create the table and append the new fields.
As you saw at the end of the "Completing the Field Definitions" section, when the
CreateDatabaseWithDAO
subroutine has completed, control returns to the
Driver
subroutine.
Driver
then checks the value of
GoAhead
and if it's TRUE, calls the subroutine that follows:
Sub MakeNewTableWithDAO(DatabaseName As String, _
TableName As String, FieldArray() As FieldType)
This procedure accepts three arguments: a string that provides the name of the database, the name of the table to be inserted in the database, and an array of field names to be inserted in the table.
Dim dbDataFile As DAO.Database
Dim tdDataTable As DAO.TableDef
Dim fldDataField As DAO.Field
Dim FieldCount As Integer
Dim i As Integer
In contrast to the
CreateDatabaseWithDAO
subroutine, the
MakeNewTableWithDAO
subroutine makes use of several object variables. They're declared in the
Dim
statements. Notice that the first three variables are declared as objects whose type (
Database
,
TableDef
, and
Field
) are qualified as DAO objects. This is to protect against the possibility of another object library, referenced by the module, with its own
Database
,
TableDef
or
Field
type.
Set dbDataFile = OpenDatabase(DatabaseName)
Set tdDataTable = dbDataFile.CreateTableDef(TableName)
The database was created by the
CreateDatabaseWithDAO
subroutine, but it wasn't opened. By opening it using
OpenDatabase
, it's established as an object and is assigned to the
dbDataFile
object variable. Then the
CreateTableDef
method is used in conjunction with the string variable
TableName
to create the table and assign it to the object variable
tdDataTable
.
With the table established, it's time to insert the fields. Begin by determining how many field names the
Driver
subroutine put in the array. The code determines that by using the
UBound
function on the array.
UBound
returns the number of the final element in an array. It is for this reason that
Option Base 1
was used at the head of the module. Otherwise, the developer would have had to remember to add 1 to whatever number
UBound
returned.
FieldCount = UBound(FieldArray)
For i = 1 To FieldCount
Set fldDataField = tdDataTable.CreateField(FieldArray(i).FieldName, _
dbText, FieldArray(i).FieldLength)
tdDataTable.Fields.Append fldDataField
Next i
A loop runs from
1
to
FieldCount
. For each record in
FieldArray
, the
CreateField
method is used to create a new field in the data table. Each field is named according to the value in the
FieldName
element. The field is typed as
Text
by means of the
dbText
argument. The field's length is set according to the value in the
FieldLength
element.
Note that it's necessary to explicitly append each field to the table using the
Append
method of the
Fields
collection. Simply creating the field is not enough.
dbDataFile.TableDefs.Append tdDataTable
End Sub
When all the fields have been created, named, and given a type and length, your code appends the table itself to the collection of tables in the database. Again, the
Append
method is used, but this time on the
TableDefs
collection instead of the
Fields
collection.
The full procedure is as follows:
Sub MakeNewTableWithDAO(DatabaseName As String, _
TableName As String, FieldArray() As FieldType)
Dim dbDataFile As DAO.Database
Dim tdDataTable As DAO.TableDef
Dim fldDataField As DAO.Field
Dim FieldCount As Integer
Dim i As Integer
Set dbDataFile = OpenDatabase(DatabaseName)
Set tdDataTable = dbDataFile.CreateTableDef(TableName)
FieldCount = UBound(FieldArray)
For i = 1 To FieldCount
Set fldDataField = tdDataTable.CreateField _
(FieldArray(i).FieldName, _
dbText, FieldArray(i).FieldLength)
tdDataTable.Fields.Append fldDataField
Next i
dbDataFile.TableDefs.Append tdDataTable
End Sub
If the database created by the code discussed in this section were based on the worksheet shown in Figure 10.1, it would look like the one shown in Figure 10.2.
It remains to populate the table with the records on the Excel worksheet. That process is taken up in the section "Declaring and Using Recordsets." First, though, it's useful to examine a slightly different approach to creating the tables and fields in an existing database.