MS Office Update to Access 2003

[i_j_hutch]

Hi all--

Not sure if this is the right ng, but here goes...

Previous to applying a recent patch to MS Office Access 2003, I was able to
execute the following SQL query which would change a field in the table from
a Yes/No to a Long integer:
ALTER TABLE <TableName> ALTER <FieldName> Long
This would automatically change the data values in the table from checkboxes
to 0's and -1's.

But now (since applying this patch), the type is changed to Long in the
Design View, but when looking at the table in the Datasheet View, the data
still shows up as checkboxes!

I came up with a six step process as a workaround:
1. Creating a temp field
2. Copying the data from the orig to the temp field
3. Deleting the orig field
4. Creating the orig field
5. Copying the data from the temp to the orig field
6. Deleting the temp field


....but this is a PITA.

So MVPs and Microsoft folks, can you confirm that this is now a problem with
Access in the latest patch? If so, will it be fixed anytime "soon"?

Thanks.

* Ian *

P.S. BTW, I have to be able to update a bunch of different databases on a
moment's notice. If it were a onesie-twosie thing, no big deal, but this is
many databases, with many tables, with lots of data. I already have a scheme
in place that (using vba) loops through each selected database, and applies
the appropriate SQL call to the appropriate tables.

 

[Douglas J. Steele]

The "problem" is cause by a couple of factors, neither of which,
unfortunately, can be changed through DDL.

After you change the field from Boolean to Long, go into the table in design
mode, and take a look at the Format property of the field. It's set to
Yes/No. Also, take a look at on the Lookup tab: you'll see that the
DisplayControl property is set to 106 (which happens to be a checkbox)

It is possible to change both of these properties in code, using DAO.

CurrentDb.TableDefs("NameOfTable").Fields("NameOfField").Properties("Format")
= "General Number"

will correct the format, while

CurrentDb.TableDefs("NameOfTable").Fields("NameOfField").Properties("DisplayControl")
= 109

will make the DisplayControl property a textbox, which is what you need.

It's necessary to make both changes.

 

[i_j_hutch]

Hi Douglas--

It works as you said (although I'm not sure why it was working just how I
wanted it to before this latest patch).

I understand that DAO would give me a programming option (with VB6) to
change those properties, but is there a way to do it with ADOX?

Thanks for your reply.

* ijhutch *

 

[Douglas J. Steele]

I don't believe there is a way to do it with ADOX, as these aren't really
database properties, which is all ADOX knows about.

What's wrong with using DAO? It's the preferred method for communicating
with Jet databases.