SUMMARY
The ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) gives ActiveX Data Objects (ADO) developers the capabilities that are present in Data Access Objects (DAO) to manipulate databases, tables, fields and other database objects programmatically. Since the release of Microsoft OLE DB Provider for Jet version 4.0, which supports the ISAM drivers that the Jet engine uses with desktop databases other than Microsoft Access, ADO developers have been able to use both ADO and ADOX with Microsoft Excel workbooks and worksheets.
However, because an Excel worksheet is not an ordinary database, ADO has some limitations when it is used with Excel.
In particular, many ADOX methods either do not work or give unexpected results when they are used with Excel. This article documents many of the limitations of ADOX and Excel. For additional information about the use of ADO with Excel, click the article number below
to view the article in the Microsoft Knowledge Base:
257819 How To Use ADO with Excel Data from Visual Basic or VBA
This article is divided into the following sections:
NOTE: The testing for this article was done on Microsoft Windows 2000 Server Service Pack 2 (SP2) with Microsoft Data Access Components (MDAC) 2.6 SP1, Microsoft Visual Basic 6 SP5, and Microsoft Excel 2002 (XP). This article may not acknowledge or discuss differences in behavior that users may observe with different versions of Windows, MDAC, Visual Basic, or Excel.
MORE INFORMATION
ADOX Catalogs
In the ADOX object model, the
Catalog object represents a database or, in the case of Excel, a workbook.
Creating a Catalog
You cannot use the
Create method of the ADOX
Catalog object to create a new Excel workbook. If you try to do so, you receive the following error message:
Operation is not supported for this type of object.
You can, however, create a new Excel workbook by specifying a new workbook filename for your Catalog and appending at least one Table to the new Catalog, using the techniques discussed below. This method will create a new workbook with only the worksheets you have created as ADOX tables; in other words, the default number of blank sheets specified for a new Excel workbook through the Excel
Options dialog box will not be added.
ADOX Tables
The ADOX
Table object corresponds to either of the Excel objects (that is, a worksheet or a range) that can serve as a container for data.
Creating a Table
You can use ADOX to create new Tables in Excel. Because you cannot create a Table without also creating Columns, the code sample appears in the "
ADOX Columns" section.
Deleting a Table
You cannot use ADOX to delete a table (worksheet) or named range in Excel, whether or not ADOX created it.
ADOX Columns
ADOX columns correspond to the columns or fields in a database and neatly correspond to the columns of an Excel worksheet.
ADO Data Types Used with Excel
ADO recognizes six data types in an Excel datasource that you can use to create columns:
- adDouble, type 5
- adDate, type 7
- adCurrency, type 6
- adBoolean, type 11
- adVarWChar, type 202
- adLongVarWChar ("memo"), type 203
Columns in General
- You can append additional columns to a previously created table as long as the table does not contain any rows of data.
- You can specify column names that include spaces.
- ADOX always creates column headings that are preceded by a single quotation mark ('), for example, 'F1, 'Col1, 'Col2. However, this does not appear to cause a problem later because the name is retrieved without the quotation mark.
Creating Columns When Creating a Table
Creating columns works best when it is done at the same time that a new table is created using ADOX.
The following code creates a new table (worksheet) that contains two columns (one numeric and one text):
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\book1.xls;Extended Properties=Excel 8.0"
Set tbl = New ADOX.Table
tbl.Name = "TestTable"
Set col = New ADOX.Column
With col
.Name = "Col1"
.Type = adDouble
End With
tbl.Columns.Append col
Set col = Nothing
Set col = New ADOX.Column
With col
.Name = "Col2"
.Type = adVarWChar
End With
tbl.Columns.Append col
cat.Tables.Append tbl
Creating Columns in an Existing Worksheet
Appending columns to a pre-existing, empty worksheet generates unusual results. When the first ADOX column is appended to the table's Columns collection, ADOX first creates a column header named F1 in Column A of the worksheet and then creates the new ADOX column header in Column B of the worksheet. Thus, ADOX code that appends two new columns results in three column headers. If you try to delete this unwanted, first column from the Columns collection before you append the first ADOX column, it appears to succeed, but the unwanted column is created nonetheless.
Deleting Columns
You cannot delete a column from an ADOX table in Excel.
- If the column has no data, this appears to succeed without error, but the column and column header remain.
- If the column contains data, the delete operation fails with the following error message:
Invalid operation.
Changing the Data Type of an Existing Column
After a column has been appended to a table, if you try to set its .Type property to a different data type, you receive the following error message:
Operation is not allowed in this context.
Adding Data to Excel Tables and Columns
When you are testing ADO code with Excel in the Visual Basic design environment, the first time that you run a recordset operation, you may receive the following error message:
Selected collating sequence not supported by the operating system.
You can disregard this error message; this error does not occur after the application is compiled. For additional information, click the article number below
to view the article in the Microsoft Knowledge Base:
246167 PRB: Collating Sequence Error Opening ADODB Recordset the First Time Against an Excel XLS
Adding Data to Excel Columns That Are Created by ADOX
When you use ADO to insert or modify data in the tables and columns that you have created in Excel using ADOX, ADO respects the data types that you specified for those columns, although it is not clear where this information is stored.
Adding Data to Ordinary Excel Columns
When you use ADO to insert or modify data in Excel worksheets or ranges that are not created or modified using ADOX, no restrictions are enforced on the type of data that you can insert.
- Text strings are stored without the preceding single quotation mark.
- Numbers appear left-aligned. In Excel 2002, numbers are flagged with the Smart Tag that warns "Numbers stored as text". This may cause a problem later when you are working with the data, especially if the data has been stored in Excel for numerical analysis.
Using SQL CREATE, ALTER, and DROP Statements
You can run Microsoft SQL Data Definition Language (DDL) statements such as CREATE, ALTER, and DROP against Excel database objects over an ADO
Connection to yield the same results as the corresponding ADOX methods that are described above.
Conclusion
- ADOX works best with Excel when a new table and its columns are all created at the same time.
- ADO works best with Excel data when that data is stored in a worksheet that is created using ADOX.