ADO / Access 97 / VB6 / "Allow Zero Length"
Posted on 2000-02-21
I need to create a table in an Access 97 .MDB, and append the fields I want. That's easy enough. But when I view the table's design in Access, I want the 'Allow Zero Length' property to be "YES", not "NO". In short, these newly-created fields must allow zero-length values.
As you can see from the code snippet below, I'm trying to set the col's Attributes property to 'adColNullable', with no effect. The 'Allow Zero Length' property in the resultant table is still 'No'.
These fields are text type fields, and they will not be part of the table's key. Here is the current code I am using:
Dim n As Long
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Dim key1 As ADOX.Key
Dim cat As ADOX.Catalog
. . .
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=" & MSJ_OLEDB_40 & ";" & "Data Source=" & sDBPath
. . .
Set tbl = New ADOX.Table
tbl.Name = "Test1"
'** Add columns to the new table
Call tbl.Columns.Append("ID", adDouble)
Call tbl.Columns.Append("Description", adVarWChar, 100)
Call tbl.Columns.Append("Suburb", adVarWChar, 50)
. . . Add other columns . . .
For n = 1 To 20
Call tbl.Columns.Append("Field_" & n, adVarWChar, 50)
'** Set keys, attributes on all columns just added.
For Each col In tbl.Columns
Select Case (UCase(col.Name))
Case "ID", "DESCRIPTION"
'** For these fields only, create a Primary Key
Set key1 = tbl.Keys("MyKey")
If (key1 Is Nothing) Then
tbl.Keys.Append "MyKey", adKeyPrimary, col.Name
'** THESE FIELDS (say, 20 or 30) MUST ALLOW ZERO-LENGTH VALUES!
'** This isn't working...
col.Attributes = adColNullable
'** Add this new table to the Catalog. Works fine.
How can I do this, using ADO v2.1, VB6, and Access 97? The right answer can't be far off from what I've got here--by which I mean, I shouldn't have to turn back to DAO or something else for a solution. ADO should be able to do this.
Thanks in advance for a helpful solution.