How to set input mask for a field using ADOX

[Catennacio]

Hi all,

My project requires writing a VBA program to update our customer database structure, including changing the "Allow Zero Length" property of a column and also change Input Mask for that column to "PASSWORD".

I know how to set the "Allow Zero length" property for a column but I have researched everywhere but could not find a way to set the "Input Mask" property for that column.

--------
Here is my code:

Set objDB = CreateObject("ADODB.Connection")
objDB.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & installFolder & "MyDB.mdb;"

Dim cat As Catalog
Dim tbl As Table
Set cat = CreateObject("ADOX.Catalog")
Set cat.ActiveConnection = objDB

Set tbl = cat.Tables("MyTB")
tbl.Columns("Mycol").Properties("Jet OLEDB:Allow Zero Length") = True
tbl.Columns("Mycol").Properties("Input Mask") = "PASSWORD"

Set tbl = Nothing
Set cat = Nothing

objDB.Close
Set objDB = Nothing
--------

The tbl.Columns("Mycol").Properties("Input Mask") = "PASSWORD" does not work when I run the program to update my db.

My question is how to set that column (currently type Text)'s Input mask to "PASSWORD" programmatically?

Any help would be very appreciated!

-Caten

 

[ByteMyzer]

You don't need ADOX for that; you can use DAO, thus (substitute highlighted table and field names as appropriate):

[COLOR="Navy"]Dim[/COLOR] db [COLOR="navy"]As[/COLOR] DAO.Database
[COLOR="navy"]Dim[/COLOR] td [COLOR="navy"]As[/COLOR] DAO.TableDef
[COLOR="navy"]Dim[/COLOR] fd [COLOR="navy"]As[/COLOR] DAO.Field
[COLOR="navy"]Dim[/COLOR] prp [COLOR="navy"]As[/COLOR] DAO.Property

[COLOR="navy"]Set[/COLOR] db = CurrentDb
[COLOR="navy"]Set[/COLOR] td = db.TableDefs("[b][i]MyTable[/i][/b]")
[COLOR="navy"]Set[/COLOR] fd = td.Fields("[b][i]MyField[/i][/b]")
[COLOR="navy"]Set[/COLOR] prp = fd.CreateProperty
prp.Name = "InputMask"
prp.Type = dbText
prp.Value = "Password"
fd.Properties.Append prp
fd.Properties.Refresh
td.Fields.Refresh

[COLOR="navy"]Set[/COLOR] prp = [COLOR="navy"]Nothing
Set[/COLOR] fd = [COLOR="navy"]Nothing
Set[/COLOR] td = [COLOR="navy"]Nothing[/COLOR]
db.Close
[COLOR="navy"]Set[/COLOR] db = [COLOR="navy"]Nothing[/COLOR]

 

[Catennacio]

Thanks for your quick reply. I'm new to DAO, so I have a few questions:
- Does your code create a new property in the existing column or does it update an existing column with Input Mask = ""?
- How do I set up the "CurrentDb"?

I want to update an existing column making it a password column, so not sure if your method works... cause I see
fd.CreateProperty and .Append prp...

 

[ByteMyzer]

Does your code create a new property in the existing column or does it update an existing column with Input Mask = ""?

The code adds the InputMask to the existing field, in the existing table.

How do I set up the "CurrentDb"?

You don't; it already exists, and is a reference to the currently open database.

I want to update an existing column making it a password column, so not sure if your method works... cause I see
fd.CreateProperty and .Append prp...

fd.CreateProperty means creating a new property for the existing field; .Append prp means we are actually appending the new property to the existing field.

Why not humor me and actually test the code to see if it works?

 

[Catennacio]

Thanks for your reply, I tested the code and got compile error "Variable not defined" at the CurrentDb.

Probably because I haven't set the DAO object yet.. how to create and point it to the desired database? (I said I'm very new to this project and just jumped in as backup when someone is on vacation... sorry if I ask dummy questions...)

 

[Premy]

Try this: Open the vb editor, go to tools>references, look for Microsoft DAO 3.6 Library, select it.

HTH

 

[Catennacio]

Try this: Open the vb editor, go to tools>references, look for Microsoft DAO 3.6 Library, select it.

HTH

That library has already been included..

 

[boblarson]

Are you trying to do this inside of Access or from another application?

 

[Catennacio]

Are you trying to do this inside of Access or from another application?

No.. I want to write a stand alone VB app to modify the current structure of my DB...

 

[ByteMyzer]

No.. I want to write a stand alone VB app to modify the current structure of my DB...

In that case, it was misleading to initiate this thread in Microsoft Access Discussion > Modules & VBA. You might have done better to post it to Apps and Windows > Visual Basic, here: http://www.access-programmers.co.uk/forums/forumdisplay.php?f=68


However, now that we know that you are calling this from a separate VB App, you will want to change:

Set db = CurrentDb

...to

Set db = DAO.OpenDatabase(installFolder & "MyDB.mdb")

 

[Catennacio]

In that case, it was misleading to initiate this thread in Microsoft Access Discussion > Modules & VBA. You might have done better to post it to Apps and Windows > Visual Basic, here: http://www.access-programmers.co.uk/forums/forumdisplay.php?f=68


However, now that we know that you are calling this from a separate VB App, you will want to change:

Set db = CurrentDb

...to

Set db = DAO.OpenDatabase(installFolder & "MyDB.mdb")

Guys sorry for the confusion... and thanks for your replies..

I tried this and it worked... but now I want to use this approach for the Allow Zero Length string property too...

I tried:
Set prp1 = fd.CreateProperty
prp1.Name = "Allow Zero Length"
prp1.Type = dbBoolean
prp1.Value = true
fd.Properties.Append prp1

It didn't work... I also try prp1.Name = "AllowZeroLength" it didn't work either.. any hint for me on this property?

 

[ByteMyzer]

This might simplify things for you a little. Try including the following Sub in your code:

[COLOR="navy"]Public Sub[/COLOR] SetProperty([COLOR="navy"]ByVal[/COLOR] sDatabase [COLOR="navy"]As String[/COLOR], _
    [COLOR="navy"]ByVal[/COLOR] sTable [COLOR="navy"]As String[/COLOR], _
    [COLOR="navy"]ByVal[/COLOR] sField [COLOR="navy"]As String[/COLOR], _
    [COLOR="navy"]ByVal[/COLOR] vPropertyName [COLOR="navy"]As Variant[/COLOR], _
    [COLOR="navy"]ByVal[/COLOR] vPropertyType [COLOR="navy"]As Variant[/COLOR], _
    [COLOR="navy"]ByVal[/COLOR] vPropertyValue [COLOR="navy"]As Variant[/COLOR])


    [COLOR="navy"]Dim[/COLOR] db [COLOR="navy"]As[/COLOR] DAO.Database
    [COLOR="navy"]Dim[/COLOR] td [COLOR="navy"]As[/COLOR] DAO.TableDef
    [COLOR="navy"]Dim[/COLOR] fd [COLOR="navy"]As[/COLOR] DAO.Field
    [COLOR="navy"]Dim[/COLOR] prp [COLOR="navy"]As[/COLOR] DAO.Property

    [COLOR="navy"]Set[/COLOR] db = DAO.OpenDatabase(sDatabase)
    [COLOR="navy"]Set[/COLOR] td = db.TableDefs(sTable)

    [COLOR="navy"]Set[/COLOR] fd = td.Fields(sField)

    [COLOR="navy"]On Error Resume Next
    Set[/COLOR] prp = fd.Properties(vPropertyName)
    [COLOR="navy"]If[/COLOR] Err.Number <> 0 [COLOR="navy"]Then[/COLOR]
        Err.Clear
        [COLOR="navy"]Set[/COLOR] prp = fd.CreateProperty
        prp.Name = vPropertyName
        prp.Type = vPropertyType
        prp.Value = vPropertyValue
        fd.Properties.Append prp
    [COLOR="navy"]Else[/COLOR]
        prp.Value = vPropertyValue
    [COLOR="navy"]End If
    On Error GoTo 0[/COLOR]

    fd.Properties.Refresh
    td.Fields.Refresh

    [COLOR="navy"]Set[/COLOR] prp = [COLOR="navy"]Nothing[/COLOR]
    [COLOR="navy"]Set[/COLOR] fd = [COLOR="navy"]Nothing[/COLOR]
    [COLOR="navy"]Set[/COLOR] td = [COLOR="navy"]Nothing[/COLOR]
    db.Close
    [COLOR="navy"]Set[/COLOR] db = [COLOR="navy"]Nothing

End Sub[/COLOR]

Then you can call it from your main code. For example:

SetProperty installFolder & "MyDB.mdb", "MyTable", "MyField", "AllowZeroLength", dbBoolean, False
SetProperty installFolder & "MyDB.mdb", "MyTable", "MyField", "InputMask", dbText, "Password"