Update Access column property to checkbox from VB.NET

[desmond]

Does anyone know how I can update the column properties in Access from VB.NET?

I am trying to make the column a check-box after I create the column.

Here is my code:

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Test.accdb;Persist Security Info=False;"

sql = "ALTER TABLE TestName ADD COLUMN [Name] TEXT(255)"
Cmd = New OleDbCommand(sql, con)
con.Open()
ObjCmd = New OleDbCommand(sql, con)
Try
ObjCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, vbExclamation)
Exit Sub
End Try
con.Close()

sql = "ALTER TABLE TestName ADD COLUMN [TestFlag] YesNo"
Cmd = New OleDbCommand(sql, con)
con.Open()
ObjCmd = New OleDbCommand(sql, con)
Try
ObjCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, vbExclamation)
Exit Sub
End Try
con.Close()


The columns get added to the database successfully, but I now want to make the 'TestFlag' column a check-box.

In vb code, I would use this:

Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim DB As Database
Set DB = CurrentDb
Set tdf = DB.TableDefs("TestName")

Set fld = tdf.Fields("TestFlag")
Set prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
fld.Properties.Append prp

Does anyone know how to do this from VB.NET?

I'm well and truly stuck...

 

[MarkK]

You can set a reference to DAO in VB.NET. Then you can use your code, but you won't have the use of CurrentDB, which is provided by Access.

Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Dim prp As DAO.Property

Set tdf = DBEngine(0)(0).TableDefs("TestName")

Set fld = tdf.Fields("TestFlag")
Set prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
fld.Properties.Append prp

Some people think this is bad form, but if you were working in Access VBA you'd also need to set a reference to DAO to run that code.

 

[desmond]

You can set a reference to DAO in VB.NET. Then you can use your code, but you won't have the use of CurrentDB, which is provided by Access.

Dim fld As DAO.Field
Dim tdf As DAO.TableDef
Dim prp As DAO.Property
 
Set tdf = DBEngine(0)(0).TableDefs("TestName")
 
Set fld = tdf.Fields("TestFlag")
Set prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
fld.Properties.Append prp

Some people think this is bad form, but if you were working in Access VBA you'd also need to set a reference to DAO to run that code.

I tried the following code:

Dim fld As dao.Field
Dim tdf As dao.TableDef
Dim prp As dao.Property
Dim dbE As dao.DBEngine
Dim dbI As SqlDbType

tdf = dbE(0)(0).TableDefs("TestName")
fld = tdf.Fields("TestFlag")
prp = fld.CreateProperty("DisplayControl", dbI, 106)
fld.Properties.Append(prp)

But it still doesn't work.

It initially didn't like the dbengine, but now it has a null reference exception.

What should I be setting the dbE to / how?

 

[MarkK]

Imports dao
Imports dao.DataTypeEnum

Public Class Form1

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim dbe As New dao.DBEngine
    Dim dbs As dao.Database
    Dim fld As dao.Field
    Dim prp As dao.Property

    dbs = dbe.OpenDatabase("YourMDB.mdb")
    fld = dbs.TableDefs("YourTable").Fields("YourField")
    prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
    Try
      fld.Properties.Append(prp)
    Catch ex As Exception
      MsgBox(ex.Message, MsgBoxStyle.Information)
    End Try
  End Sub

End Class

 

[desmond]

Imports dao
Imports dao.DataTypeEnum
 
Public Class Form1
 
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim dbe As New dao.DBEngine
    Dim dbs As dao.Database
    Dim fld As dao.Field
    Dim prp As dao.Property
 
    dbs = dbe.OpenDatabase("YourMDB.mdb")
    fld = dbs.TableDefs("YourTable").Fields("YourField")
    prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
    Try
      fld.Properties.Append(prp)
    Catch ex As Exception
      MsgBox(ex.Message, MsgBoxStyle.Information)
    End Try
  End Sub
 
End Class

Thanks for the response Lagbolt. Because I'm using Access 2007, I cannot use .mdb. And if I simply change this to .accdb, it doesn't work (unrecognized database format). I've even tried it with
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..." and it doesn't work.

Any ideas?

 

[MarkK]

Well then you can set a reference to ACE.

Microsoft Office 12.0 Access database Object Library

This is the new DAO!!!

 

[desmond]

Well then you can set a reference to ACE.

Microsoft Office 12.0 Access database Object Library

This is the new DAO!!!

Hi Lagbolt - thanks for the quick response. I already did a search and added this as a reference, but I still get the same issue. My code now looks like this:

Dim dbe As New dao.DBEngine
Dim dbs As dao.Database
Dim fld As dao.Field
Dim prp As dao.Property

dbs = dbe.OpenDatabase("C:\TestDB.accdb")
fld = dbs.TableDefs("TestName").Fields("TestFlag")
prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
Try
fld.Properties.Append(prp)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information)
End Try

It has a COMException unhandled on line dbs = dbe.OpenDatabase("C:\TestDB.accdb") with Unrecognized database format.

Am I missing something?

 

[boblarson]

Access 2007/2010 use a different engine than previous versions. You need to use the version (ACE) which is not the same as DAO 3.6.

 

[MarkK]

And you've confirmed that you can open "C:\TestDB.accdb" normally from windows explorer?

 

[boblarson]

I'm not that good with VB.NET at this point, but wouldn't you need to set a reference to this:

Microsoft.Office.Interop.Access.dao

Version 12.0

The one that is

C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Microsoft.Office.Interop.dao.dll

(9.0 or 10.0 or whatever VStudio you have)

 

[lution]

Are you building/running this on a 64-bit machine? If so, you need to set your project to compile x86 only. My thread with the problem I was having and the solution are at http://www.access-programmers.co.uk/forums/showthread.php?t=199196

-Lution

 

[desmond]

And you've confirmed that you can open "C:\TestDB.accdb" normally from windows explorer?

Yes, and I can also open from the prior bit of code:

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\TestDB.accdb;Persist Security Info=False;"
sql = "ALTER TABLE TestName ADD COLUMN [TestFlag] YesNo"
Cmd = New OleDbCommand(sql, con)
con.Open()
ObjCmd = New OleDbCommand(sql, con)
Try
ObjCmd.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message, vbExclamation)
ExitSub
EndTry
con.Close()

I also have the reference to Microsoft.Office.Interop.Access.Dao

I guess this is something to do with Access 2007. Is there another way I can update the properties of an Access 2007 db?

 

[boblarson]

I also have the reference to Microsoft.Office.Interop.Access.Dao

You have to make sure that you have the reference set to the right one. On my computer I have 3 of the same Microsoft.Office.Interop.Access.Dao references but each is a different Office version. You need to select 12 or greater.

 

[MarkK]

I believe that the most reliable way to add a COM reference is select it from the COM tab of the Visual Studio Add References dialog. In that case .NET automatically wraps the object in a .NET assembly, which is what that interop file is. Look how small it is, the interop file.

 

[boblarson]

I believe that the most reliable way to add a COM reference is select it from the COM tab of the Visual Studio Add References dialog. In that case .NET automatically wraps the object in a .NET assembly, which is what that interop file is. Look how small it is, the interop file.

Yes, that's what I'm talking about. But the big thing is that you need to have the right version selected because any DAO reference for Office Versions 11 and prior will have no knowledge of the ACCDB file format. You need to select the version 12 or 14 in order for it to not have an unrecognized database error.

 

[desmond]

Yes, that's what I'm talking about. But the big thing is that you need to have the right version selected because any DAO reference for Office Versions 11 and prior will have no knowledge of the ACCDB file format. You need to select the version 12 or 14 in order for it to not have an unrecognized database error.

I added a COM reference to Microsoft Office 12.0 Object Library, and Microsoft Office 12.0 Access Database Engine Object Library 12.0.

It made no difference at all.

It still fails on this line (unrecognizable database format):
dbs = dbe.OpenDatabase("C:\TestDB.accdb")

After scouring the net, I tried this:

'dbs = dbe.OpenDatabase("C:\TestDB.accdb", False, False, "MSAccess;")

but it failed with 'could not find installable ISAM'.

Is there anything else I can try?

 

[desmond]

Well I got this to work for anyone who's having the same issue. I don't understand why this small difference worked, but it did:

Dim dbe As Microsoft.Office.Interop.Access.Dao.DBEngine
Dim dbs As Microsoft.Office.Interop.Access.Dao.Database
Dim fld As dao.Field
Dim prp As dao.Property

dbe = New Microsoft.Office.Interop.Access.Dao.DBEngine()
dbs = dbe.OpenDatabase("C:\TestDB.accdb")
fld = dbs.TableDefs("TestName").Fields("TestFlag")
prp = fld.CreateProperty("DisplayControl", dbInteger, 106)

Try
fld.Properties.Append(prp)
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Information)
End Try

Why referencing dbe as Microsoft.Office.Interop.Access.Dao.DBEngine instead of dao.dbengine with the reference set, and setting dbe to a new instance is beyond me, but it works perfectly.

Thanks to Lagbolt and Boblarson for their help.