SET TEXT COLUMN IN TABLE TO ALLOW ZERO LENGTH 1

[PeterWallace]

I am trying to Make all the Text fields in a Table "ALLOW ZERO LENGTH"

This sample code is supposed to do just That

SOME TEXT COLS in the Table already allow Zero Length Some DO NOT

BUT ALL Text Columns on the Table report That COLUMN.Attribute = adColNullable ( I E 2 ) No Matter what ACCESS says

Sub ALLOWZEROLEN()
Dim Cat As ADOX.Catalog
Dim Table As ADOX.Table
Dim Mstr As String
Dim I As Integer
Set Cat = New ADOX.Catalog
Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & TheDatabase & ";"

'TableExists = False
For Each Table In Cat.Tables
'Ok = True
Ok = False
Mstr = UCase$(Table.Name)
If Left(Mstr, 4) = "MSYS" Then Ok = False
If Mstr = "CREDITOR" Then Ok = True
If Ok = True Then


For I = 0 To Table.Columns.Count - 1
If Table.Columns(I).Type = adVarWChar Then
Debug.Print " Col "; Table.Columns(I).Name; " ";
Debug.Print " WAS attrib "; Table.Columns(I).Attributes;
If Table.Columns(I).Attributes = adColNullable Then
' thats whts wanted
Debug.Print
Else
Table.Columns(I).Attributes = adColNullable
Debug.Print " NOW attrib "; Table.Columns(I).Attributes, " adColNullable= "; adColNullable

End If
End If
Next
End If
Next
Set Cat = Nothing
End Sub

 

[dilettante]

Stop shouting (all caps). Stop posting without markup (your code blobs are difficult to read).

Nullable has nothing to do with zero length. What you need to do is clearly documented:

DBPROP_JETOLEDB_COL_ALLOWZEROLENGTH

Type: VT_BOOL
Typical R/W: R/W

Description: Jet OLEDB:Allow Zero Length

Determines whether zero-length strings can be inserted into this field. Ignored for data types that are not strings. This is similar to the OLE DB standard property DBPROP_COL_NULLABLE but distinct. Zero-length strings are not NULLs in Jet.

This value is an extended property of the ADOX Column object.

No, I am not going to feed you copy/paste code. This forum is for professionals.

 

[PeterWallace]

dilettante Thank You for the assistance in solving my problem

apology for caps lock .... ( did not actually realise it was on )

apology for LayOut but Preview looked allright ... will do better next time

but for those who may come latter and might appreciate the code then

DIM ColName as string
DIM TableName as string
ColName = "STREET"
TableName = "Creditor"



Set Cat = New ADOX.Catalog
Cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & TheDatabase & ";"
Cat.Tables(TableName).Columns(ColName).Properties("Jet OLEDB:Allow Zero Length").Value = True
Set Cat = Nothing

 

[fredericofonseca]

Peter - by now you should already know how to use this forum. Better go and search for all its functionality with regards to posting.

tip: while I am writing this there is a line with lots of options like B for bold, I for italic and so on... try all the options on that line and you will eventually find the ones that are useful and that you should use ... often



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886