Can't change Binary format to Text

[fdormoy]

I have Microsoft Access 2003 and a table with over 1 million rows and 980 meg file size.
This table as a field created in Binary format and I want to change it in Text format, but it wont let me do it. I get the message ".. cant change the data type - There is not enough disk space or memory". This is not true, I have plenty of disk space and 8 Gb RAM. So how can I sole this problem ?

[June7]

Binary is not an Access field datatype. Unless you mean Yes/No datatype. What is the datatype of this field?

Google: VBA convert binary to text
http://thydzik.com/vb6vba-functions-...base64-string/
http://www.go4expert.com/forums/showthread.php?t=118

[boblarson]

...and 980 meg file size.

Is that AFTER running COMPACT AND REPAIR?

[fdormoy]

Anser to first remark : It does show in table design mode that the columns are in Binary. So Binary format do exist and/or are created within Access.

Answer to the second remark : Yes of course I did a Compact and Repair before and it did not help

My comment : I just found a solution whch is to create extra columns which are in text format and with a query update fill in that new column with the same value as the old column which is in binary and then erase the old column. It works !!

[June7]

I don't see 'Binary' offered as a datatype choice in table design for Access 2007 and 2010.

Glad you found solution.

[fdormoy]

ou are right for these versions, but I am still working with the old Access 2003 version (as I said in my first post) and may be that old version has this format capability

[fdormoy]

Well, several years passed since my last thread, and I am now using Access 2010, and I have the same problem, when importing a table from one base to another, it transforms certain text columns of the first base into Binary in the new base.
Having to create new field and transform each of these binary fields into text is time consuming every time I want to import tables. Can someone tell me if a solution to this problem was found since 2011 ?

[June7]

Can you provide data for analysis and testing? Follow instructions at bottom of my post.

[fdormoy]

Hello,

The problem is that all this data is confidential. If I only give you a sample, of course you will not have the same problem as having the full 0.7 gig file. Furthermore, if the download is limited to 2 mb in zipped format, I would not go very far in data size and this would be useless. I would need to know the exerience of other people having faced the same problem and how they managed to solve it.

[June7]

Possible options:

1. Create a text field in table and UPDATE it with the value from the original field, delete original field.

2. Test changing the field type with smaller dataset or no data. Make a copy of db and delete most or all records. Are you now able to change data type? Can you now import records from the original db?
Or copy the table in the original db (structure only) and try to change data type. Import records. Delete original table. Rename new table.

[ssanfu]

I'm curious......

I found this site: http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx
that has a note at the bottom of the page:
"Microsoft Access itself doesn't use the BINARY data type. It's recognized only for use in queries on linked tables from other database products that support the BINARY data type."

when importing a table from one base to another, it transforms certain text columns of the first base into Binary in the new base.

SQL server does have a Binary field format. So what database are you importing the table from?

Along that same line, what would the data look like that is in binary format? And the same field in text format?

[fdormoy]

Well, my question s : why Access creates a data format that it does not support ? Why, when importin a table from another database, it transforms some text fields into binary ?
Anyway, what I did was to use option 1 proposed by June7.