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

  • Thread starter Thread starter evan1979.2
  • Start date Start date
E

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.
 
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.
Click to expand...

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
 
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
Click to expand...

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
 
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
 
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
 
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
Click to expand...

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
 
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
Click to expand...

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
 
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):
Click to expand...

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
 
Back
Top