PRB: ADOX Error -2147217859 "Type Is Invalid" When You Append a Table to a Catalog (266302)

The information in this article applies to:

Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0

This article was previously published under Q266302

SYMPTOMS

When you use ADOX to append a new table to an ADOX catalog, you may receive the following error message:

Runtime error: '-2147217859 (80040e3d)':
Type is invalid.

CAUSE

This problem occurs because providers can accept only recognized data types for the columns in the table. Each provider is different and specific to the database format in which it is stored.

RESOLUTION

To resolve this problem, use a data type that the provider supports. The following list includes the data types that each provider supports:

Constant	Value	Jet 3.51	Jet 4.0	SQL 7.0
adBinary	128	Yes	Yes	Yes
adBoolean	11	Yes	Yes	Yes
adChar	129	Yes	No	Yes
adCurrency	6	Yes	Yes	Yes
adDate	7	Yes	Yes	No
adDouble	5	Yes	Yes	Yes
adGUID	72	Yes	Yes	Yes
adInteger	3	Yes	Yes	Yes
adLongVarBinary	205	Yes	Yes	Yes
adLongVarChar	201	Yes	No	Yes
adLongVarWChar	203	No	Yes	Yes
adNumeric	131	No	Yes (with info)*	Yes (with info)*
adSingle	4	Yes	Yes	Yes
adSmallInt	2	Yes	Yes	Yes
adUnsignedTinyInt	17	Yes	Yes	Yes
adVarBinary	204	Yes	Yes	Yes
adVarChar	200	Yes	No	Yes
adVarWChar	202	No	Yes	Yes
adWChar	130	No	Yes	Yes
adDBTimeStamp	135	No	No	Yes

* When you use the adNumeric data type with Microsoft Jet 4.0 and Microsoft SQL Server 7.0, you must set precision.

MORE INFORMATION

Steps to Reproduce Behavior

In Visual Basic, create a new Standard EXE project. Form1 is created by default.
From the Project menu, click References, and then click Microsoft ADO Ext. 2.1 for DDL and Security.
Place a CommandButton control (Command1) onto Form1.

Paste the following code onto the Declarations section of Form1:

Private Sub Command1_Click()

   Set cat = New ADOX.Catalog
   Set tbl = New ADOX.Table
   cat.ActiveConnection = _
      "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Nwind.mdb;"
   tbl.Name = "TestTable"
   tbl.Columns.Append "ValidColumn", adInteger
   tbl.Columns.Append "InvalidColumn", adDBTimeStamp
   cat.Tables.Append tbl
End Sub

Modify the "cat.ActiveConnection" property so that it points to a valid Microsoft Access database file.
Run the project, and press Command1. You receive the above-mentioned error because the Jet provider does not support the adDBTimeStamp data type.

REFERENCES

For more information and code samples about the supported data types for the Jet provider, see the following MSDN white paper:

"Migrating from DAO to ADO: Defining and Retrieving a Database's Schema"
http://msdn.microsoft.com/library/techart/daotoadoupdate.htm

For more information about the supported data types for the SQL Server OLE DB Provider, see the following Microsoft Web site:

"Mapping Data Types"
http://msdn.microsoft.com/library/psdk/sql/adoprg02_294j.htm

Modification Type:	Major	Last Reviewed:	8/23/2001
Keywords:	kbDSupport kbJET kbprb KB266302