M$ Access - Alter Table - AllowZeroLength

[TimSNL]

Hello,

My appliaction needs to create some new text fields in an Access database table during a version upgrade.

I am using dbExpressADO.
The ALTER TABLE command works fine and adds the fields I need to the table.
The problem is that the AllowZeroLength property of each new field is set to NO, this needs to be YES.

Does anyone know how I can create a new text field with an SQL statement, and set AllowZeroLength=YES?

Another question ...
I have looked over the web for this and come up with an Access Module that will fix the problem. When run, it goes over the whole database and sets all text fields AllowZeroLength=YES.

Public Function SNL_AllowZeroLength() As Integer
    Dim db As Database, tdf As TableDef, fld As Field
    Set db = CurrentDb
    For Each tdf In db.TableDefs
     If tdf.Attributes = 0 Then
        For Each fld In tdf.Fields
            If fld.Type = dbText Or fld.Type = dbMemo Then
                If fld.AllowZeroLength <> True Then fld.AllowZeroLength = True
            End If
        Next fld
     End If
    Next tdf
    SNL_AllowZeroLength = 1
End Function

Using this module would solve my problem mentioned above.
But I can't call it using an SQL Statement.

Does anyone know how I can call this M$Access Function via my ADO connection?

Thankyou for considering my questions.
Tim

 

[ByzantianX]

Well, perhaps I could not answer you in a way that would help you the most, anyway... Perhaps, it would not be a bad idea to use TAccessApplication component and connect to Access database, then use "Run" or "DoCmd" method, disconnect from Access database and the task could be completed?

 

[TimSNL]

Thankyou ByzantianX for you suggestion

I have played with TAccessApplication and yes, it will do what I need. BUT, I need this to be able to run on a system that does not have M$Acces installed! So this solution is no good for me, bummer.

Looks like this is a very difficult problem to fix.

Any more ideas?
Tim

 

[TimSNL]

I found a solution, and post it FYI

Use ADOX.

Import Type Library "Microsoft Ado Ext 2.x for DLL and se...", and create a pas file, include it in your unit. The code below will then set AllowZeroLength!

procedure TForm1.Button1Click(Sender: TObject);
var
   MyCat: _Catalog;
begin
   MyCat := CoCatalog.Create;
   MyCat.Set_ActiveConnection('Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\db4.mdb;');
   MyCat.Tables['TableName'].Columns['ColumnName'].Properties['Jet OLEDB:Allow Zero Length'].Value := true;
end;

ADOX only requires MDAC to be installed, I have tested it on systems without M$Access and it works fine

(Thanks to Amir Mahfoozi from Iran who replied to a post on another froum)

After so much searching ... the solution seems so easy.
Well, I suppose thats just hoe the cookie crumbles.

Tim
SNL Computing