Display Bit (YesNo) Data Type as Checkbox in Access Table

Question

While googling how to convert a BIT (YESNO) column in an access database to a checkbox programmatically, a possible solution I found was to copy an existing database's structure by using DoCmd. I have added a reference to Microsoft.Office.Interop.Access and added those 2 lines in my code:

using Access = Microsoft.Office.Interop.Access;    
Access.Application myAccessInstance = new Access.Application();

In the try block where I open a connection to my database I'm executing Do.Cmd.TransferDatabase:

myAccessInstance.DoCmd.TransferDatabase(0, "Microsoft Access", "C:\\Users\\user\\Desktop\\Merge\\playlists.MDB", 0, "PlaylistNames",
                "PlaylistNamesCopy", true);

and I get a System.Runtime.InteropServices.COMException:

This operation requires an open database.

Does anyone know what is going wrong?

Ok, I had to open a connection to the database: myAccessInstance.OpenCurrentDatabase("C:\\Users\\user\\Desktop\\Merge2\\playlists.MDB"); — Pantelis
– Pantelis, Commented Dec 19, 2011 at 10:35
You can use DAO to add a format, this is VBA but it should be possible to translate to c#: wiki.lessthandot.com/index.php/… — Fionnuala
– Fionnuala, Commented Dec 19, 2011 at 11:24
Thanks. I'll try that. "Set db = CurrentDb" What does this line do? I have a problem with the method I posted. By calling OpenCurrentDatabase the .MDB file actually opens. Thats not the behavior I want. I just need to establish a connection to the database. — Pantelis
– Pantelis, Commented Dec 19, 2011 at 11:56
Run in Access VBA, set db=currentdb sets the variable db to an instance of the current database. You must remember that Access comes in two parts, the database side in Jet or ACE and the forms etc in the Access side. When you say you want to connect to the database, it all depends on what you want to do. If you want to manipulate tables and queries, you can use ADO and DAO. However, you wish to run VBA in Access, as far as I can see, which means you need a version of Access open. I do not think this is going to be the best approach for what you seem to wish to do. — Fionnuala
– Fionnuala, Commented Dec 19, 2011 at 12:03
What I originally wanted to do was to make a column be displayed as a checkbox control. By searching on google I found out that there is no way to do this programmatically, only with design view. In my program I'm merging records of client and server access 97' databases, by dropping and recreating the tables. One of the tables has a checkbox YESNO field. As it is not possible to set a YESNO field programmatically as a checkbox, I next tried to copy the structure of the table I'm dropping. Is there any other way I can do it? — Pantelis
– Pantelis, Commented Dec 19, 2011 at 12:22

Fionnuala · Accepted Answer · 2011-12-19 13:02:56Z

1

I do not know C#, so this is a rough example of how to set a field (column) in a table to a checkbox display, which is what I believe this question to be about.

//Added reference to COM Microsoft DAO 3.6

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            DAO.DBEngine dbEng = new DAO.DBEngine(); 
            DAO.Workspace ws = dbEng.CreateWorkspace("", "admin", "", DAO.WorkspaceTypeEnum.dbUseJet); 
            DAO.Database db = ws.OpenDatabase("z:\\docs\\dbfrom.mdb", false, false, "");
            DAO.TableDef tdf = db.TableDefs["Test"];

            DAO.Field fld = tdf.Fields["AYesNo"];
            //dbInteger  3 
            //accheckbox  106 
            DAO.Property prp = fld.CreateProperty("DisplayControl", 3,106);
            fld.Properties.Append(prp);
        }
    }
}

answered Dec 19, 2011 at 13:02

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pantelis Over a year ago

Thanks I'll try that. The OpenDatabase() isn't what I want to establish a connection with the database but I know I can use OpenConnection with a Workspace object.

Pantelis Over a year ago

Hey man, thanks a bunch! That worked. Btw I was wrong at my previous post, the OpenConnection method is for ODBC data source.

Fionnuala Over a year ago

@Pantelis I am going to change the title of this post to more accurately reflect the content.

Collectives™ on Stack Overflow

Display Bit (YesNo) Data Type as Checkbox in Access Table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related