How to create a YesNo column in ADOX
John Mills
Does anybody know how to do this? Every time I try it keeps coming back with
errors.
Here's the code I'm using:
'ConBriefCase is an active Access 2000 db
oBriefCaseCat.ActiveConnection = ConnBriefCase
Set oNewTable = New ADOX.Table
Set oNewTable.ParentCatalog = oBriefCaseCat
oNewTable.Name = "testing"
Set oNewCol = New ADOX.Column
Set oNewCol.ParentCatalog = oBriefCaseCat
oNewCol.DefinedSize = 2
oNewCol.Name = "theBitField"
oNewCol.Type = adBoolean
oNewCol.Attributes = adColNullable
oNewTable.Columns.Append oNewCol
Set oNewCol = Nothing
oBriefCaseCat.Tables.Append oNewTable ' <== this is where the error
occurs!
Set oNewTable = Nothing
Sheir Rahman Ali
Here is what I think you should try.
For Boolean do not specify the DefinedSize.
After you name your table object (& set its parentCatalog), add it to the
catalog object.
THEN create your boolean column object (w/o the defined size) and append
that
to the table object.
I think this is also related to my problem. I am trying to build the table
object with
columns (bool, autonumber, etc...), add indices and keys; then when I try to
append the table object to the Catalog object, I get a run-time error.
My errors are either ...
3219 Operation is not allowed in this context
or
-214721887
Multiple step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
or
-214721859 Type is invalid (when try to append table to catalog)
I think we both have to do operations one at a time
(ie don't append a bunch of columns at one time.)
This code here gives me the (-214721887) error ...
******************************************************
'Define the table with columns
With objTable
'must create a table with a name
.Name = "tblDummy"
' Assign the new table to the previously created objCatalogalog
' so we can use its dynamic props (ie to create Autonumber columns)
Set .ParentCatalog = objCatalog
' Create fields and append them to the new Table object.
.Columns.Append "ContactId", adInteger 'long int
' Make the ContactId column and auto incrementing column
.Columns("ContactId").Properties("AutoIncrement") = True
.Columns.Append "CustomerID", adSmallInt 'integer
' Make the column Required=False
.Columns("CustomerID").Attributes = adColNullable
.Columns.Append "CustomerName", adVarWChar, 50 'text
.Columns("CustomerName").Attributes = adColNullable
.Columns.Append "AgeCustomer", adUnsignedTinyInt 'byte
.Columns("AgeCustomer").Attributes = adColNullable
.Columns.Append "SingleType", adSingle 'single
.Columns("SingleType").Attributes = adColNullable
.Columns.Append "DoubleType", adDouble 'double
.Columns("DoubleType").Attributes = adColNullable
.Columns.Append "AnnualIncome", adCurrency 'currency
.Columns("AnnualIncome").Attributes = adColNullable
.Columns.Append "bAlive", adBoolean 'bool
.Columns("bAlive").Attributes = adColNullable
.Columns.Append "Birthdate", adDate 'date/time
.Columns("Birthdate").Attributes = adColNullable
.Columns.Append "Notes", adLongVarWChar 'memo
.Columns("Notes").Attributes = adColNullable
End With 'With objTable
'Append the table to the Catalog (database)
objCatalog.Tables.Append objTable
objCatalog.Tables.Refresh 'refresh the collection of tables
*************************************************************
I once came across a website dealing with ADO run-time errors, but I
forgot to bookmark it. Dam it all.
Anyway I hope my ramblings help.
If you figure out a better way to do things, please let me know.
Later
Sheir
(sh...@magma.ca)
"John Mills" <jmi...@roadmap-tech.com.NoSpam> wrote in message
news:ez2HS2ihAHA.1364@tkmsftngp04...
John Mills
errors
-214721887
Multiple step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.
or
-214721859 Type is invalid (when try to append table to catalog)
I think I've found a bug in MDAC - Actuall I've run into this several times
in the past but chalked it up to something I was doing wrong.
Anyway the bug is that if you try to explicitly set Column.Attributes =
adColNullable
you will get the "Multiple step OLE DB operation ..." error. If you leave
off adColNullable to make it a Required=Yes field, Jet forces it to
Required=No (but does NOT make an error).
I came up with a routine to copy the structure of a table in SQL Server 7 to
Access 2K using the Ole-DB Providers only -- not ODBC:
===================================== cut here
==========================================
#Const MakeColsAsTables = 0 ' to debug indiv. fields
Public Sub CopyStructToMDB(TableName As String)
Dim oCat As New ADOX.Catalog
Dim oBriefCaseCat As New ADOX.Catalog
Dim oTable As ADOX.Table
Dim oNewTable As ADOX.Table
Dim oCol As ADOX.Column
Dim oNewCol As ADOX.Column
'both of these DBs MUST exist before running this routine!
oCat.ActiveConnection = "the source ConnString" ' the source
oBriefCaseCat.ActiveConnection = "the target ConnString" ' the target
Set oTable = oCat.Tables(TableName)
#If MakeColsAsTables Then ' to debug indiv. fields
For Each oCol In oTable.Columns
#End If
Set oNewTable = New ADOX.Table
' Create a new Table object.
Set oNewTable.ParentCatalog = oBriefCaseCat
#If MakeColsAsTables Then ' to debug indiv. fields
oNewTable.Name = TableName & "_" & oCol.Name
#Else
oNewTable.Name = TableName
#End If
' Create fields and append them to the
' Columns collection of the new Table object.
Dim X As Integer
Dim nType As Integer
Dim nAttribs As Integer
#If Not MakeColsAsTables Then ' to debug indiv. fields
For Each oCol In oTable.Columns
#End If
Set oNewCol = New ADOX.Column
Set oNewCol.ParentCatalog = oBriefCaseCat
'change SQL Server char to nchar
oNewCol.Type = IIf(oCol.Type = adChar, adWChar, oCol.Type)
oNewCol.DefinedSize = oCol.DefinedSize
oNewCol.NumericScale = oCol.NumericScale
oNewCol.Precision = oCol.Precision
oNewCol.Attributes = oCol.Attributes
If oNewCol.Type = adVarWChar Then ' make any nvarchar over 255 a memo
If oCol.DefinedSize > 255 Then
oNewCol.Type = adLongVarWChar 'Memo type
oNewCol.DefinedSize = 0
End If
ElseIf oNewCol.Type = adDBTimeStamp Then 'SQL Server Prov. thinks datetime
is DBTimestamp!
oNewCol.Type = adDate ' so fix it for Jet 4
ElseIf oNewCol.Type = adBoolean Then
oNewCol.Attributes = oCol.Attributes
'workaround for Jet 4 adBoolean bug
If (oCol.Attributes And adColNullable) = adColNullable Then
oNewCol.Attributes = oCol.Attributes And Not (adColNullable)
End If
ElseIf oNewCol.Type = adLongVarWChar Then
oNewCol.DefinedSize = 0
End If
oNewCol.Name = oCol.Name
oNewTable.Columns.Append oNewCol
Set oNewCol = Nothing
#If Not MakeColsAsTables Then ' to debug indiv. fields
Next
#End If
On Error Resume Next
' Add the new Table to the Tables collection of the database.
oBriefCaseCat.Tables.Append oNewTable
If Err <> 0 Then
MsgBox "Database error (" & Hex(Err) & "): " & Error, vbInformation,
App.Title
End If
Set oNewTable = Nothing
#If MakeColsAsTables Then ' to debug indiv. fields
Next
#End If
End Sub
===================================== cut here
==========================================
"Sheir Rahman Ali" <sh...@magma.ca> wrote in message
news:euQlO$thAHA.692@tkmsftngp04...
