Create table with Yes/no field From VB6 1

[dipgohil79]

hi to all
I want to crate a table ('tblStatus") in which there is a field ( fldCheck ). I want to make that field Yes/No type. I am trying to do this with following code :
dim strSQL as string
strSQL = "CREATE TABLE tblStatus ( " & _
"fldCheck yesno )"

con.Execute strSQL

Here con is my ADO connection.


When i open the Access table Design it will show me the fields with yes/no type but in the data mode it show me "yes" or "no" in the fields. I want Checkbox view in the yes/no fields.


Thanks in advance

 

[Remou]

This may help.

Sub AddDisplayControl()
'Requires reference to Microsoft DAO 3.6 Object Library
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim db As Database
Dim strSQL As String

    Set db = CurrentDb
    
    'Create a table ...
    strSQL = "Create Table tblLTD (TheYesNoCheck YesNo, TheYesNoCombo YesNo)"
    db.Execute strSQL
    
    'It is now in the table collection, so ...
    Set tdf = db.TableDefs("tblLTD")
    
    'Change the way the YesNo fields display.
    'A Checkbox
    Set fld = tdf.Fields("TheYesNoCheck")
    Set prp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
    fld.Properties.Append prp
    
    'A combobox
    Set fld = tdf.Fields("TheYesNoCombo")
    Set prp = fld.CreateProperty("DisplayControl", dbInteger, acComboBox)
    fld.Properties.Append prp
    'We will need a format
    Set prp = fld.CreateProperty("Format", dbText, "Yes/No")
    fld.Properties.Append prp

End Sub

From:

http://wiki.lessthandot.com/index.php/Add_a_Display_Control_(Checkbox,_Combobox)_to_a_YesNo_Field

 

[dipgohil79]

to Remou

from your ans there is one line :
Set db = CurrentDb
can you tell me what is this "CurrentDb"
I have my database in C:\data.mdb how can i refere to this?

 

[Remou]

The code is VBA and intended to run in an open database, CurrentDB is just that, the database that you are running the code in. You can refer to another database, that that is a different issue. Where did you intend to run this code?

 

[dipgohil79]

to Remou
I want to run this code in my VB application. I want to create that table at runtime.

 

[Remou]

If you are using an Access back-end with VB, why should the format of a table field matter? This is an Access forum, the VB forum is forum222.

 

[Golom]

You can use Remou's code to create the field with the appropriate DisplayControl property. Just use

Set db = DAO.DBengine(0).Opendatabase("C:\data.mdb")

If you want to display the table in VB however (rather than Access) then you will need a grid control of some sort (e.g. DBGrid, FlexGrid) and they don't support a checkbox as a display option.