Solve the problem of creating nullable fields in ADOX

A strange problem occurs during work. The "Unicode compression" attribute of a field is automatically set to false, which causes the nvarchar field value with a length of 50 to be automatically filled with spaces, to be a fixed-length field. Because the customer's data cannot be obtained, we can only find a way to solve the problem by programming. The process of solving the following records is full of hardships:

1. Through DDL. ADODB. Connection. Execute "alter table [tablename] add column [newcolname] char (50 );". In this way, the "Unicode compression" attribute of the field is automatically set to false, which cannot solve the problem.

2. Therefore, I thought that, on the basis of 1, I could use the ADOX. Column. propertys ("jet oledb: Compressed Unicode strings") = true method to survive and survive. It may be the "write only" attribute.

3. All of the above methods fail, and I want to do it in pure ADOX programming mode. We encountered another tricky problem in the middle, through ADOX. columns. in append mode, ADOX cannot be set. column. propertys ("nullable") = true or ADOX. column. attributes = adcolnullable.

4. I searched a lot of materials and encountered the same problem. Finally, inspired by a post, I thought that some attributes become read-only after the field is added by append. So I tried again and finally solved the problem. Set "nullable" of the field to true.

 

The following code is used:

Code
Private sub methodname (byval cn as ADODB. Connection)

On Error goto errhandler

Dim adoxcat as ADOX. Catalog
Dim adoxtable as ADOX. Table
Dim adoxcol as ADOX. Column
Dim adoxproperty as ADOX. Property
Dim strallowempty as string
Dim strallownull as string

CN. begintrans
Istrans = true

Set adoxcat = new ADOX. Catalog
Set adoxcat. activeconnection = Cn
Set adoxtable = adoxcat. Tables ("tablename ")
Strallowempty = "jet oledb: Allow zero length"
Strallownull = "nullable"

'1.
'Cn. Execute "alter table [stockproduct] add column [colname] char (50);" 'the "Unicode compression" attribute of the field is automatically set to false and cannot be true.

'2
'Adoxtable. Columns. APPEND "colname", adwchar, 50
'Adoxtable. Columns ("colname"). properties (strallowempty) = true' is valid for some reason.
'Adoxtable. Columns ("colname"). properties (strallownull). value = true' is invalid. Why?
'Adoxtable. Columns ("colname"). Attributes = adcolnullable' is the same as the above sentence, and it is also wrong

'4
Set adoxcol = new ADOX. Column
With adoxcol
. Name = "colname"
. Type = adwchar
. Definedsize = 50
. Attributes = adcolnullable '!! Key: Set adcolnullable before you append the column. Solution !!
'. Properties (strallowempty) = true' before append, the properties set has no element
'. Properties (strallownull) = true' before append, the properties set has no elements
End
Adoxtable. Columns. append adoxcol 'after creating the column object, append
Adoxcol. properties (strallowempty) = true

CN. committrans

Exit sub
 
Errhandler:

Msgbox err. Source & vbcrlf & err. Description, vbcritical

If istrans = true then
CN. rollbacktrans
End if
If not CN is nothing then
If CN. State = adstateopen then
CN. Close
End if
Set Cn = nothing
End if