Help with ADOX

[salzan]

I have the following code:

With tblTemp
    .Name = tmpTable_Name
    .Columns.Append "DeptId", adSingle
    .Columns.Append "DeptName", adVarWChar, 50
    .Columns.Append "CatName", adVarWChar, 50
    .Columns.Append "StartDate", adDate
    .Columns.Append "EndDate", adDate
    .Columns.Append "Hours", adSingle
    .Columns.Append "TypeHrs", adVarWChar, 1
End With
catCatalog.Tables.Append tblTemp

Table gets created okaty but all the Required properties for the columns are set to Yes. I need a way to change the columns' Required property to No.

Thank you in advance for yuor help.
Salzan

[ADezii]

I have the following code:

With tblTemp
.Name = tmpTable_Name
.Columns.Append "DeptId", adSingle
.Columns.Append "DeptName", adVarWChar, 50
.Columns.Append "CatName", adVarWChar, 50
.Columns.Append "StartDate" , adDate
.Columns.Append "EndDate", adDate
.Columns.Append "Hours", adSingle
.Columns.Append "TypeHrs", adVarWChar, 1
End With
catCatalog.Tabl es.Append tblTemp

Table gets created okaty but all the Required properties for the columns are set to Yes. I need a way to change the columns' Required property to No.

Thank you in advance for yuor help.
Salzan

There does not appear to be an easy way to accomplish this in ADOX, but it can easily be done once the Table and Fields are created with ADOX, by using DAO:
[CODE=vb]
Dim intCounter As Integer

For intCounter = 0 To CurrentDb.Table Defs("tmpTable_ Name").Fields.C ount - 1
CurrentDb.Table Defs("tmpTable_ Name").Fields(i ntCounter).Requ ired = False
Next[/CODE]

[salzan]

Thank you. I assume the columns are in order I created them. Also I can't believe MS didn't include such a method/property in ADOX.

[NeoPa]

Thank you. I assume the columns are in order I created them. Also I can't believe MS didn't include such a method/property in ADOX.

Principally because, in the context of the method supplied (Append), it would make no sense. Append applies generally to collections.

BTW Another way to code the loop would be to say :

Dim fldThis As ADODB.Field

For Each fldThis In CurrentDb.TableDefs("tmpTable_Name").Fields
  fldThis.Required = False
Next fldThis

[ADezii]

Principally because, in the context of the method supplied (Append), it would make no sense. Append applies generally to collections.

BTW Another way to code the loop would be to say :

Dim fldThis As ADODB.Field

For Each fldThis In CurrentDb.TableDefs("tmpTable_Name").Fields
  fldThis.Required = False
Next fldThis

I don't think that the above syntax will work, NeoPa, but I'm probably wrong.

[NeoPa]

I know the .Fields collection exists. What I'm not sure of is whether it's a collection of ADODB.Fields or not. If it is, then the code SHOULD work.