asked on

ADOX adding columns

I'm very new to ADOX and I'm having some trouble.

I can't work out how to add new columns to a table with certain properties.

Just in case it matters, I'm creating a new Access 97 database.

The only thing I've managed to get to work is something like this:
'FieldX
Set objNewColumn = New ADOX.Column
objNewColumn.Name = "FieldX"
objNewColumn.DefinedSize = 15
objNewColumn.Attributes = adColNullable 'also objNewColumn.Attributes =
adColFixed
objNewTable.Columns.Append objNewColumn

1 - When Attributes = adColFixed, it won't allow me to leave the field
blank. If adColNullable, it allows NULL values. I would like to be able to
leave the field blank *without* allowing NULL values. How?

2 - I can't seem to be able to use the Type property. Any time I use it it
throws errors.... How can I make a column numeric this way?

Thanks

Pino

TimCottee

Caraf_g, here is an example of creating an access database using ADOX

Dim catAccess As New ADOX.Catalog
Dim tblValExp As New ADOX.Table
Dim cnnAccess As New ADODB.Connection
Dim rstValExp As New ADODB.Recordset
catAccess.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
catAccess.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb"
With tblValExp
.Name = "tblValExp1"
.Columns.Append "Stock Name", adVarWChar, 40
.Columns.Append "Stock Symbol", adVarWChar, 6 '1
.Columns.Append "Stock Price", adSingle '2
.Columns.Append "Est Date", adVarWChar, 10 '3
.Columns.Append "Est Year", adVarWChar, 6 '4
.Columns.Append "Price High(0)", adSingle '5
.Columns.Append "Price High(1)", adSingle '6
.Columns.Append "Price High(2)", adSingle '7
.Columns.Append "Price High(3)", adSingle '8
.Columns.Append "Price High(4)", adSingle '9
.Columns.Append "Price High(5)", adSingle '10
End With
catAccess.Tables.Append tblValExp
Set tblValExp = Nothing
Set catAccess = Nothing
cnnAccess.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\testbed\test1.mdb;Persist Security Info=False"
rstValExp.Open "SELECT * FROM tblValExp1", cnnAccess, adOpenKeyset, adLockOptimistic
rstValExp.AddNew
rstValExp.Fields(0).Value = "Name"
rstValExp.Fields(1).Value = "Symbol"
rstValExp.Fields(2).Value = 1.5
rstValExp.Fields(3).Value = Format(Now(), "dd/mm/yyyy")
rstValExp.Fields(4).Value = "2000"
rstValExp.Fields(5).Value = 1.5
rstValExp.Fields(6).Value = 1.5
rstValExp.Fields(7).Value = 1.5
rstValExp.Fields(8).Value = 1.5
rstValExp.Fields(9).Value = 1.5
rstValExp.Fields(10).Value = 1.5
rstValExp.Update
rstValExp.MoveFirst
Set rstValExp = Nothing
Set cnnAccess = Nothing

caraf_g

ASKER

The problem is not how to create a table with ADOX. I can do that.

The problem is how exactly can I define a column, so that it will allow the user to leave the contents blank, but without leaving the value NULL.

In other words, if nothing is entered, I want the column to have the value "", *not* NULL.

My guess is that I can do this through the Properties property of the ADOX.Column object. But HOW?

ASKER CERTIFIED SOLUTION

TimCottee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

caraf_g

ASKER

Woohoo!

This DOES work:

'Additional Information
Set objNewColumn = New ADOX.Column
objNewColumn.Name = "Additional Information"
objNewColumn.Attributes = adColFixed And Not adColNullable
objNewColumn.Type = adVarWChar
objNewColumn.DefinedSize = 30
objNewTable.Columns.Append objNewColumn
objNewColumn.Properties("Jet OLEDB:Allow Zero Length").Value = True
objNewColumn.Properties("Default").Value = """"""

Don't ask. Just DON'T ASK.

<giggling maniacally>

caraf_g

ASKER

Anyway - thanks for the effort. I'll give you an "A" for your trouble.

caraf_g

ASKER

Don't you just love the """""" for the default value.

You'd think that "" would do. Wouldn't you? WOULDN'T YOU? But NO. That'd be WAY too intuitive. We can't have that, now, can we!

Ha!

caraf_g

ASKER

End of rant