| 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. Providing the Code to Create the Database After establishing the necessary reference, enter this code into the new module: Sub CreateDatabaseWithDAO(DatabaseName As String) Dim GoAhead As Boolean GoAhead = True If Dir(DatabaseName) <> "" Then If MsgBox(Prompt:="Delete existing " & DatabaseName & "?", _ Buttons:=vbYesNo, Title:="Naming conflict.") = vbYes Then Kill DatabaseName GoAhead = True 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 End Sub
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 CreateDatabaseWithDAO "Crash Carts.mdb"
The subroutine's logic is as follows: - Check to see whether a database with the name that's been passed already exists in the default path. That's the purpose of the Dir function.
- If that database already exists, display a message box to inform the user and ask whether to delete the existing version.
- If the user says yes by clicking the message box's Yes button, delete the existing version and set a Boolean, GoAhead, to TRUE.
- If the user says no by not clicking the message box's Yes button, don't delete the database. Just display a message box saying that the existing version will be left as is and set the Boolean GoAhead to FALSE.
- If GoAhead is TRUE, create the database.
The critical part of the subroutine is the statement that actually creates the database. Its full syntax is Set DatabaseVariable = Workspace.CreateDatabase _ (Name, Locale, Options)
You would need the Set DatabaseVariable portion only if you had already declared DatabaseVariable as an object variable; for example: Dim DatabaseVariable As DAO.Database
The subroutine CreateDatabaseWithDAO makes no further use of the database beyond creating it, so the subroutine doesn't assign it to an object variable. You would specify a workspace in the CreateDatabase statement if you had already invoked a new one and wanted to use it. By omitting that specification the statement uses the default, active workspace. The CreateDatabase method itself takes three arguments: Name This establishes the name and the extension of the database. You can, optionally, supply a path: Name:="C:\NewDatabase.mdb". If you don't supply a path, the Excel workbook's default path is used. This argument is required. Locale This specifies the order in which text strings are to be sorted. You'll almost always use dbLangGeneral, which subsumes most European languages including English. This argument is required. Options This argument is optional. Omit it to create a new database that is based on your current Office version. You can create a database that's based on an earlier version if you want. For example, suppose that you're running Access 2003 but most of your users are running Access 97. You could use Option:=dbVersion30 to create a database compatible with Jet 3.5, and thus compatible with Access 97.
Creating a Table Using DAO 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. NOTE 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. Figure 10.1. Notice that the maximum length of a value in A2:A20 is 6, and compare with the table design in Figure 10.2. 
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. Figure 10.2. Notice that the field length for the first field is 6 and compare it with Figure 10.1. 
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. | 