Help with ADOX

[Staats]

Good evening all. I am having a problem creating a table with ADOX.
Dim tblTemp As ADOX.Table
Dim cat As ADOX.Catalog
Dim clmTemp As ADOX.Column

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

On Error Resume Next
cat.Tables.Delete "tblTypebyDist"
' ********************************8change after finishing!
On Error GoTo 0
' ^^^^^^^^^^^^^^^6
Set tblTemp = New ADOX.Table
tblTemp.Name = "tblTypeByDist"

tblTemp.Columns.Append "Volume", adInteger
tblTemp.Columns.Append "Length", adInteger
tblTemp.Columns.Append "Class", adInteger
tblTemp.Columns.Append "WIM", adInteger
tblTemp.Columns.Append "PRV", adInteger
tblTemp.Columns.Append "PRL", adInteger
tblTemp.Columns.Append "PRC", adInteger
tblTemp.Columns.Append "PRW", adInteger
tblTemp.Columns.Append "Dist", adVarChar, 10 <-------- ERROR WITH THIS
STATEMENT


cat.Tables.Append tblTemp
cat.Tables.Refresh
For Each clmTemp In tblTemp.Columns
' Debug.Print clmTemp.Name
Next clmTemp

Set tblTemp = Nothing
Set cat = Nothing

********************************************************************************************
In the code above, it will run as long as the TYPEs of columns being
appended are 'adInteger'. I get errors when trying to add a column with
the type as advarchar. Any Ideas?

Thanks in advance.
~Garrett

 

[RoyVidar]

Good evening all. I am having a problem creating a table with ADOX.

Dim tblTemp As ADOX.Table
Dim cat As ADOX.Catalog
Dim clmTemp As ADOX.Column

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

On Error Resume Next
cat.Tables.Delete "tblTypebyDist"
' ********************************8change after finishing!
On Error GoTo 0
' ^^^^^^^^^^^^^^^6
Set tblTemp = New ADOX.Table
tblTemp.Name = "tblTypeByDist"

tblTemp.Columns.Append "Volume", adInteger
tblTemp.Columns.Append "Length", adInteger
tblTemp.Columns.Append "Class", adInteger
tblTemp.Columns.Append "WIM", adInteger
tblTemp.Columns.Append "PRV", adInteger
tblTemp.Columns.Append "PRL", adInteger
tblTemp.Columns.Append "PRC", adInteger
tblTemp.Columns.Append "PRW", adInteger
tblTemp.Columns.Append "Dist", adVarChar, 10 <-------- ERROR WITH
THIS STATEMENT


cat.Tables.Append tblTemp
cat.Tables.Refresh
For Each clmTemp In tblTemp.Columns
' Debug.Print clmTemp.Name
Next clmTemp

Set tblTemp = Nothing
Set cat = Nothing

********************************************************************************************
In the code above, it will run as long as the TYPEs of columns being
appended are 'adInteger'. I get errors when trying to add a column
with the type as advarchar. Any Ideas?

Thanks in advance.
~Garrett

I think perhaps adVarChar maps to Jet 3.51 text fields and SQL server
varchar fields, but try adVarWChar for Jet 4.0 fields.

Here's a little "map" - whatch for linebreaks in the link
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

 

[Staats]

Thanks for the help and the link to the various data types - that is a
great bit of info. I couldn't find a table like that in the white
pages...
~GS