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

[evan1979.2@gmail.com]

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

Dim Cmd As New OleDb.OleDbComm and("ALTER TABLE m_table ADD boolColumn
YesNo", objConn)
Cmd.ExecuteNonQ uery()

, 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]

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

<evan1979.2@gma il.comschrieb

I am creating a new boolean column in a MSAccess .mdb database like
this:
>
Dim Cmd As New OleDb.OleDbComm and("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQ uery()
>
, 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@gmail.com]

Re: How to create a checkbox datatype column in a table in VB .netcode

Thanks Armin,

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

Evan

On May 1, 8:13 pm, "Armin Zingler" <az.nos...@free net.dewrote:

<evan197...@gma il.comschrieb
>

I am creating a new booleancolumnin a MSAccess .mdb database like
this:

>

Dim Cmd As New OleDb.OleDbComm and("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQ uery()

>

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

>

Is there a way to make thecolumndispla y 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/ac...322071033.aspx
>
Armin

[evan1979.2@gmail.com]

Re: How to create a checkbox datatype column in a table in VB .netcode

On May 1, 8:13 pm, "Armin Zingler" <az.nos...@free net.dewrote:

<evan197...@gma il.comschrieb
>

I am creating a new booleancolumnin a MSAccess .mdb database like
this:

>

Dim Cmd As New OleDb.OleDbComm and("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQ uery()

>

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

>

Is there a way to make thecolumndispla y 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/ac...322071033.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.Table Defs("tblCapexC odes").Fields(" ColName")
Set pt = f1.CreateProper ty("DisplayCont rol", dbInteger, acCheckBox)
CurrentDb.Table Defs("tblCapexC odes").Fields(" ColName").Prope rties.Append
pt

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

[evan1979.2@gmail.com]

Re: How to create a checkbox datatype column in a table in VB .netcode

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.Table Defs("tblCapexC odes").Fields(" ColName")
Set pt = f1.CreateProper ty("DisplayCont rol", dbInteger, acCheckBox)
CurrentDb.Table Defs("tblCapexC odes").Fields(" ColName").Prope rties.Append
pt

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

[evan1979.2@gmail.com]

Re: How to create a checkbox datatype column in a table in VB .netcode

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.Table Defs("tblCapexC odes").Fields(" ColName")
Set pt = f1.CreateProper ty("DisplayCont rol", dbInteger, acCheckBox)
CurrentDb.Table Defs("tblCapexC odes").Fields(" ColName").Prope rties.Append
pt

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

[evan1979.2@gmail.com]

Re: How to create a checkbox datatype column in a table in VB .netcode

On May 1, 8:13 pm, "Armin Zingler" <az.nos...@free net.dewrote:

<evan197...@gma il.comschrieb
>

I am creating a new booleancolumnin a MSAccess .mdb database like
this:

>

Dim Cmd As New OleDb.OleDbComm and("ALTER TABLE m_table ADD
boolColumn YesNo", objConn)
Cmd.ExecuteNonQ uery()

>

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

>

Is there a way to make thecolumndispla y 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/ac...322071033.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.Table Defs("tblCapexC odes").Fields(" ColName")
Set pt = f1.CreateProper ty("DisplayCont rol", dbInteger, acCheckBox)
CurrentDb.Table Defs("tblCapexC odes").Fields(" ColName").Prope rties.Append
pt

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

[Armin Zingler]

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

<evan1979.2@gma il.comschrieb

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.Releas eComObject from VB.Net.


Armin

[evan1979.2@gmail.com]

Re: How to create a checkbox datatype column in a table in VB .netcode

On May 2, 10:05 am, "Armin Zingler" <az.nos...@free net.dewrote:

<evan197...@gma il.comschrieb
>

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.Releas eComObject 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(By Val ColumnName As String, ByVal
ConnectionStrin g As String)
Dim objConn As New OleDb.OleDbConn ection(Connecti onString)
objConn.Open()

'get info about the column
Dim schemaTable As DataTable =
objConn.GetOleD bSchemaTable(Ol eDbSchemaGuid.C olumns, New Object()
{Nothing, Nothing, m_table_name, ColumnName})

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

'create the new column
Dim Cmd As New OleDb.OleDbComm and(String.Form at("ALTER TABLE
{0} ADD COLUMN {1} YesNo", m_table_name, ColumnName), objConn)
Cmd.ExecuteNonQ uery()

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

Dim sDbPath As String = "C:\.....md b"
Dim sDbPassword As String = ""

dbs = dbe.OpenDatabas e(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).Fi elds(ColumnName )

dbs.TableDefs(m _table_name).Fi elds(ColumnName ).Properties.Ap pend(fld.Create Property("Displ ayControl",
DAO.DataTypeEnu m.dbInteger, 106))

dbs.TableDefs(m _table_name).Fi elds(ColumnName ).Properties.Ap pend(fld.Create Property("Forma t",
DAO.DataTypeEnu m.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]

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

<evan1979.2@gma il.comschrieb

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