Welcome to dbFreaks.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

ADOX and Catalog and default field value

 
   Database Help (Home) -> Visual Basic -> ADO RSS
Next:  grouping in tuple relational calculus  
Author Message
Wart

External


Since: Jul 28, 2004
Posts: 7



(Msg. 1) Posted: Tue Feb 15, 2005 6:13 pm
Post subject: ADOX and Catalog and default field value
Archived from groups: microsoft>public>vb>database>ado (more info?)

I am using VB6 and ADOX 2.7.
I need to add a field (column) to a Catalog's Table's Column's collection
with a default value. Is there a way to do this? I don't see it in the
properties collection when it is instantiated nor when it has been appended
to the columns collection.
Any help would be appreciated.
CF
Some further info:
This is a row from an ADO recordset opened with adSchemaColumns saved with
adPersistXML
<z:row TABLE_NAME="ChartParms" COLUMN_NAME="RecordID"
ORDINAL_POSITION="12" COLUMN_HASDEFAULT="True" COLUMN_DEFAULT="0"
COLUMN_FLAGS="122" IS_NULLABLE="True" DATA_TYPE="3" NUMERIC_PRECISION="10"
DESCRIPTION="Record Number for parm" />


I I need to add this column to a database (Access 2003) that does not have
it. I can add it but I can not set the default value.
SSet lADOColumn = New adox.Column
lADOColumn.Name = prsTargetSchema.Fields("COLUMN_NAME")
lADOColumn.Type = prsTargetSchema.Fields("DATA_TYPE"
If Not IsNull(prsTargetSchema.Fields("NUMERIC_PRECISION")) Then
lADOColumn.NumericScale = prsTargetSchema.Field("NUMERIC_PRECISION"
End If
IIf Not IsNull(prsTargetSchema.Fields("CHARACTER_MAXIMUM_LENGTH"))Then
lADOColumn.DefinedSize = prsTargetSchema.Fields("CHARACTER_MAXIMUM_LENGTH")
End If
If prsTargetSchema.Fields("IS_NULLABLE") = True Then
lADOColumn.Attributes = 2
End If

' If Not
IsNull(prsTargetSchema.Fields("Column_hasDefault")) Then
' If
prsTargetSchema.Fields("Column_hasDefault") = True Then
' lADOColumn.Properties(Cool =
prsTargetSchema.Fields("Column_Default")
' End If
' End If
lADOCat.Tables(prsTargetSchema.Fields(2).Value).Columns.Append lADOColumn

At this point, the Properties count for the field is 0. The properties count
for an existing field is 15 or so. So the commented out section attempting
to set some property errors out.

I

 >> Stay informed about: ADOX and Catalog and default field value 
Back to top
Login to vote
Paul Clement

External


Since: Sep 02, 2003
Posts: 236



(Msg. 2) Posted: Wed Feb 16, 2005 10:20 am
Post subject: Re: ADOX and Catalog and default field value [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

On Tue, 15 Feb 2005 17:13:54 -0500, "Wart" wrote:

¤ I am using VB6 and ADOX 2.7.
¤ I need to add a field (column) to a Catalog's Table's Column's collection
¤ with a default value. Is there a way to do this? I don't see it in the
¤ properties collection when it is instantiated nor when it has been appended
¤ to the columns collection.

If I remember correctly the property isn't available until after the column has been added to the
collection.

Dim tbl As New ADOX.Table
Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

Set cat.ActiveConnection = cnn

Set tbl = cat.Tables("Table1")
tbl.Columns("SomeField").Properties("Default") = 5


Paul ~~~ pclement DeleteThis @ameritech.net
Microsoft MVP (Visual Basic)

 >> Stay informed about: ADOX and Catalog and default field value 
Back to top
Login to vote
Wart

External


Since: Jul 28, 2004
Posts: 7



(Msg. 3) Posted: Wed Feb 16, 2005 1:28 pm
Post subject: Re: ADOX and Catalog and default field value [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Paul,
Thanks for the reply.
What you say is true, but I have found that you have to disconnect and then
reconnect the Catalog to the connection for the properties to become
available in the catalog - boring. Refreshing the Tables and/or Columns
collections does not help.
So, using yur example:

Dim tbl As New ADOX.Table
Dim col as New ADOX.Column
Dim cat As New ADOX.Catalog
Dim cnn As New ADODB.Connection

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;"

Set cat.ActiveConnection = cnn

Set tbl = cat.Tables("Table1")

col.name = "SomeField"
col.Type = adChar
col.DefinedSize = 50
tbl.Columns.Append col

'The following will throw a "property doesn't exist" error at this point. If
you try to look at the "Default" property
'in the immediate window instead, it will not exist.
tbl.Columns("SomeField").Properties("Default") = 5

'Disconnect the Catalog and reconnect
Set cat.ActiveConnection = Nothing
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables("Table1")

'The following will work now
tbl.Columns("SomeField").Properties("Default") = 5

'If you want to use this field in a not null index, you have to update 'the
field with the default value. Otherwise the index creation will fail.
cnn.Execute "Update Table1 set SomeField = 5"

CF

"Paul Clement" wrote in message


 >
 > ¤ I am using VB6 and ADOX 2.7.
 > ¤ I need to add a field (column) to a Catalog's Table's Column's
 > collection
 > ¤ with a default value. Is there a way to do this? I don't see it in the
 > ¤ properties collection when it is instantiated nor when it has been
 > appended
 > ¤ to the columns collection.
 >
 > If I remember correctly the property isn't available until after the
 > column has been added to the
 > collection.
 >
 > Dim tbl As New ADOX.Table
 > Dim cat As New ADOX.Catalog
 > Dim cnn As New ADODB.Connection
 >
 > cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 > "Data Source=e:\My Documents\db1.mdb;" & _
 > "Jet OLEDB:Engine Type=4;"
 >
 > Set cat.ActiveConnection = cnn
 >
 > Set tbl = cat.Tables("Table1")
 > tbl.Columns("SomeField").Properties("Default") = 5
 >
 >

 > Microsoft MVP (Visual Basic)
 >> Stay informed about: ADOX and Catalog and default field value 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
Adding a column to an existing ADOX Catalog's table's key - Does anyone know how (or whether it is possible) to add a column to an existing key for a column using ADOX? pseudo code: Dim cn as ADODB.Connection Dim lCat as new ADOX.Catalog Dim ltbl as new ADOX.Table Dim lKey as New ADOX.Key 'open the cn Set..

SQL server image field - I have a problem related to a field in a SQL table whcih is an "image" datatype. The powers that be here claim it is a bmp. I think it is not, or that it is encrypted in some way. I would like to test my theory by reading the data in the fie...

runtime error 3021 and user permissions - I have a VB6 application that uses ADO 2.7 to access an Access2000 database. I have found on Windows XP and 2000, that if the user is not part of the administrators group, they receive: "runtime error 3021. Either BOF or EOF is true, or the current ...

updatable ado-recordset - Hi, In the code below i have an updatable ado-recordset. However it is not working how i thought it would be. Maybe someone can help me out here? The global connection (Thanks to Brendan): Option Compare Database Option Explicit Public mconn As..

ADO Connection Access Database VB - I have been accessing a database on a local drive thru an ADO connection. The following is the code that I have been using. cnCejco.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Y:\Cejco.mdb;Persist Security Info=False" The problem is n...
   Database Help (Home) -> Visual Basic -> ADO All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]