Many thanks, problem solved!

Thank you, too, for pointing out the bug. I did indeed encounter it, but I
attributed it to my lack of familiarity with ADOX techniques.

Gordon

-----Original Message-----
From: Imar Spaanjaars [mailto:Imar@S...]
Sent: December 22, 2000 07:48
To: professional vb
Subject: [pro_vb] Re: Create a database Using ADO/ADOX


Use the Attributes property of the Column object, like this:

First create the columns (code was already present, just repeating it for
clarity):

<OLD>
       With .Columns
          .Append "FirstName", adVarWChar
          .Append "LastName", adVarWChar
          .Append "Phone", adVarWChar
          .Append "Notes", adLongVarWChar
       End With
</old>

then set the attributes for each column you want to change:

<NEW>
       With !FirstName
         .Attributes = adColNullable ' Only set nullable
       End With

       With !LastName
         .Attributes = adColFixed or adColNullable ' Set them both
       End With
</new>

The same is true for setting the properties of the column, like
AutoIncrement:

<PROPERTIES>
       With !ID
         .ParentCatalog = catDB
         .Properties("Autoincrement") = true
       End With
</properties>

Note that you NEED to set the ParentCatalog of the column to the Catalog
you are using, otherwise you don't have access to the dynamic property.

Beware of a strange bug in ADOX. When you try to set both the properties of
a column and the attributes, an error will be generated.
See the following KB article for more information:
http://support.microsoft.com/support/kb/articles/Q272/0/01.ASP

You can also set all this when adding the column, like this:

Dim col As ADOX.Column
Set col = New ADOX.Column
With col
     .ParentCatalog = catDB
     .Type = adInteger
     .Name = "ID2"
     .Properties("Autoincrement") = True
End With
tblNew.Columns.Append col


HtH

Imar


---
You are currently subscribed to pro_vb as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-pro_vb-$subst('Recip.MemberIDChar')@p2p.wrox.com