Unwanted Binary Data Type

[Guest]

In Access 2003, we have run into a problem with binary field types when using
make table queries. It occurs when we want specific data in a table to be set
later with an update query. To do this, we leave the parameter blank in the
make table query, or fill it with the word “setâ€. After the table is
updated, it has binary data types in the fields initially populated with
nulls or “setâ€.

The binary fields take up a lot of memory, causing data retrieval to take a
very long time. As a temporary solution, we have been going into table
design view, and manually changing the data types from binary to text or long
integer. However, it has gotten to a point where this is no longer working
because the database is so large that there is not enough memory to allow us
to change the data types.

We ultimately would like the fields that are currently being set to
“binaryâ€, to be long integer or text. Is there any way to disable the
default binary data types or to change the default to be text or long integer
data types?

Also, through my research I saw the suggestion to use delete and append
queries because they allow you to set the data fields. Although a good
suggestion, our database is vast and it would be very time consuming to
convert all the make table queries to append/delete queries.

 

[Gary Walter]

Hi Alicia,

One clever workaround is to use
an IIF construct in your make table
query as Michel once demonstrated:

NewField: IIf(True,Null,#1/1/1900#)


-- you end up with date/time field, all nulls


-- works for text and number also
NewField: IIf(True,Null," ") <--get type text(255), all null
NewField: IIf(True,Null,0) <--get type Long, all null


-- or use Cxxx functions
NewField: IIf(True,Null,CCur(0)) <--get type Currency, all null
NewField: IIf(True,Null,CDbl(0)) <--get type Double, all null


So clever...and eliminates "binary type" bugaboo
when you had used


NewField: Null


Good luck,

gary