create table query, bit value not updateable

[evaleah]

I have a create table query I execute from VBA code that includes a BIT column. All the columns work perfectly normally except the bit. No matter what I do to attempt to update the value in the new table it stays -1. When I open the table in design view and change the format to Yes/No then it works. Is there some way to set the format in a create table statement?

Thanks!

 

[genomon]

Do you mean a yes/no datatype in access, or are you trying to do something with linked tables?
Please post back your UPDATE SQL.

Cogito eggo sum – I think, therefore I am a waffle.

 

[kjv1611]

You say it's a create table query, but in your context, it sounds like it's either an Append or Update table.

Please do post your VBA/SQL, so we can know for sure what you are trying to do.

--

"If to err is human, then I must be some kind of human!" -Me

 

[evaleah]

First I execute the first step via DAO:

 With qdf
        'create columndefinitions table
      .sql = "CREATE TABLE " & strTableName & "_ColumnDefinitions (tname text(30) not null, fldpos number not null, ctlpage number null, ctlpos number not null, ctlname text(30) not null, ctldesc text(255), fldtype text(50) null, ctlshow text(5) not null, ctllookup text(50) null, ctltype number null, fldsize number null, ctlcaption text(255) null, accept bit)"
      .ReturnsRecords = False
      .Execute
   End With

The table is created but the value of the accept for each and every record is -1. Later I attempt to update the value to 1 or 0 and it doesn't update until I go into the Design view and set the Format property on the column. I cannot do that as I need to be able to do the creation and updating via code only. I am not doing this manually every.

 

[PWise]

how about



currentdb.execute "Update " & strTableName & " Set accept =0"

note in jet(access) a bit filed can only be -1 or 0