How to create a checkbox datatype column in a table in VB .net code

[evan1979.2]

I am creating a new boolean column in a MSAccess .mdb database like
this:

Dim Cmd As New OleDb.OleDbCommand("ALTER TABLE m_table ADD boolColumn
YesNo", objConn)
Cmd.ExecuteNonQuery()

, which works fine. But... when the .mdb file is opened in Access the
values for the new column are shown as "-1" and "0"...

Is there a way to make the column display in Access as checkboxes? I
want to be able to do this at runtime in my .net program.

thanks.

 

[Armin Zingler]

I am creating a new boolean column in a MSAccess .mdb database like
this:

Dim Cmd As New OleDb.OleDbCommand("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQuery()

, which works fine. But... when the .mdb file is opened in Access
the values for the new column are shown as "-1" and "0"...

Is there a way to make the column display in Access as checkboxes? I
want to be able to do this at runtime in my .net program.

No, not using OleDb. AFAIK you have to use ADOX (or even DAO) to alter
the format used by the Access aplication to display data. Note that this
is Access specific whereas OleDb only refers to the database part which
does not know a user interface.

Maybe interesting:
http://office.microsoft.com/en-us/access/HP010322071033.aspx


Armin

 

[evan1979.2]

Thanks Armin,

Do you have an example of how to do this using DAO?

Evan

 

[evan1979.2]

No, not using OleDb. AFAIK you have to use ADOX (or even DAO) to alter
the format used by the Access aplication to display data. Note that this
is Access specific whereas OleDb only refers to the database part which
does not know a user interface.

Maybe interesting:http://office.microsoft.com/en-us/access/HP010322071033.aspx

Armin

Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColName")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColName").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan

 

[evan1979.2]

Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColName")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColName").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan

 

[evan1979.2]

Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColName")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColName").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan

 

[evan1979.2]

No, not using OleDb. AFAIK you have to use ADOX (or even DAO) to alter
the format used by the Access aplication to display data. Note that this
is Access specific whereas OleDb only refers to the database part which
does not know a user interface.

Maybe interesting:http://office.microsoft.com/en-us/access/HP010322071033.aspx

Armin

Thanks Armin,

Do you have an example of how to do this in .net? Basically, I want to
do this (I think this example is VB6):

Dim f1 As DAO.Field, pt As DAO.Property
Set f1 = CurrentDb.TableDefs("tblCapexCodes").Fields("ColName")
Set pt = f1.CreateProperty("DisplayControl", dbInteger, acCheckBox)
CurrentDb.TableDefs("tblCapexCodes").Fields("ColName").Properties.Append
pt

(but cannot figure out how to implement it in .net)
Evan

 

[Armin Zingler]

Thanks Armin,

Do you have an example of how to do this using DAO?

http://support.microsoft.com/kb/304274
Look for "Displaycontrol" property. Note it's VBA/VB6 code, and you may
additionally need to call
System.Runtime.InteropServices.Marshal.ReleaseComObject from VB.Net.


Armin

 

[evan1979.2]

http://support.microsoft.com/kb/304274
Look for "Displaycontrol" property. Note it's VBA/VB6 code, and you may
additionally need to call
System.Runtime.InteropServices.Marshal.ReleaseComObject from VB.Net.

Armin

I've got it now, thanks Armin.

My main problem was that I needed to include "Microsoft DAO 3.6 Object
Library" into the .net references so that I can use the DAO routines.

Here is my solution for anyone interested (it looks for the column
requested and if it can't find it, creates a bool field/column and
adds the extra parameters using DAO routines to make the field/column
a checkbox):

Public Sub CreateColumn(ByVal ColumnName As String, ByVal
ConnectionString As String)
Dim objConn As New OleDb.OleDbConnection(ConnectionString)
objConn.Open()

'get info about the column
Dim schemaTable As DataTable =
objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, New Object()
{Nothing, Nothing, m_table_name, ColumnName})

'add the requested column if it does not exist
If schemaTable.Rows.Count = 0 Then

'create the new column
Dim Cmd As New OleDb.OleDbCommand(String.Format("ALTER TABLE
{0} ADD COLUMN {1} YesNo", m_table_name, ColumnName), objConn)
Cmd.ExecuteNonQuery()

Dim dbs As DAO.Database
Dim dbe As New DAO.DBEngine

Dim sDbPath As String = "C:\.....mdb"
Dim sDbPassword As String = ""

dbs = dbe.OpenDatabase(sDbPath, False, False, "MS
Access;PWD=" & sDbPassword & ";")

'make the new column a checkbox field (or else the field
displays as "-1" and "0" as "Yes/No")
Dim fld As DAO.Field
fld = dbs.TableDefs(m_table_name).Fields(ColumnName)

dbs.TableDefs(m_table_name).Fields(ColumnName).Properties.Append(fld.CreateProperty("DisplayControl",
DAO.DataTypeEnum.dbInteger, 106))

dbs.TableDefs(m_table_name).Fields(ColumnName).Properties.Append(fld.CreateProperty("Format",
DAO.DataTypeEnum.dbText, "Yes/No"))
dbs.Close()

'cleanup
dbs = Nothing
dbe = Nothing
fld = Nothing
End If

objConn.Close()
End Sub


Note: The "DisplayControl" and "Format" properties are used when
looking at the database in MSAccess

 

[Armin Zingler]

Here is my solution for anyone interested (it looks for the column
requested and if it can't find it, creates a bool field/column and
adds the extra parameters using DAO routines to make the
field/column a checkbox):

Thanks for posting the code. If anybody will stumble across the COM
reference counting issue (if there is any), he will still be able to
ask. (I currently can't post a resolution in advance.)


Armin