Solved

How can I convert a Binary field to Long binary data?

Posted on 2007-03-17
22
201 Views
Last Modified: 2007-04-10
Hello,

The title says it all...

Please help. Thanks.
0
Comment
Question by:i950
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
  • 3
22 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18742341
is this field in an access table?
0
 

Author Comment

by:i950
ID: 18742350
Yes.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 18742354
you can use

currentdb.execute "alter table NameOfTable alter column binaryfieldname longbinary"

just change  
     NameOfTable  to actual name of table
     binaryfieldname  to actual name of binary field
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:i950
ID: 18742375
I'm a beginner... where should I run that code?

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18742409
you can place the codes in a  click of a command button in a form

private sub command0_click()

currentdb.execute "alter table NameOfTable alter column binaryfieldname longbinary"

end sub

do you know how to create button in a form?
what is the name of the table? the name of the field?


0
 

Author Comment

by:i950
ID: 18742462
I think I was able to run the code but nothing changed. It still showing "Binary" not "long binary data"

Thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18742470
can you post the codes that you use, all of it
0
 

Author Comment

by:i950
ID: 18742480
Here it is:

Option Compare Database

Private Sub Option4_Click()

currentdb.execute "alter table ev1 alter column binp longbinary"

End Sub

It's on the Click event or a radio button.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18742499
you should see  Ole Object in the data type column when you open the table in design view.

check again.
0
 

Author Comment

by:i950
ID: 18742514
I do.. but when I double click the table it's showing "Binary" only.

The reason I want to change it to long binary data is the long binary data is byte by byte the same as the file that was entered in the database. When a file is saved as Binary only, chars are being added at the beginning of the file and when I'm extracting the files it's being corrupted.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18742525
The LONGBINARY data field is used to store graphic image data and allows any alphanumeric data to be entered and saved.

don't double click the table.
select the table and click Design from the database objects menu.
now look for the field  {binp } and see what is written in the Data Type column
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 100 total points
ID: 18742794
Cap ... doesn't sound like the code is executing for him?  I know the code works because I tried it ... changed a Text field to LongBinary and then to Binary.

It's interesting that you can set a field Data Type to Binary in code ... but Binary is not an option as a Data Type to pick in table design view ... but will show as Binary if you set it in code. Also, when you set it to Binary, the Field Size is set to 510 (255*2).  But ... if you try to change the field size to say 500 or 509 or anything less than 510 but greater than 255 ... it won't let you !

i950:  How are you loading the Binary data into that field?

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18752654
try

Option Compare Database

Private Sub Option4_Click()

currentdb.execute "alter table ev1 alter column binp longbinary"

msgbox "Code was run"         '<--- you should get this message

End Sub
0
 

Author Comment

by:i950
ID: 18754043
> don't double click the table.
> select the table and click Design from the database objects menu.
> now look for the field  {binp } and see what is written in the Data Type column

It's written OLE Object

> msgbox "Code was run"         '<--- you should get this message

I got that message

Please help. Thank you.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18754051
It's written OLE Object  <--- this is Long Binary
0
 

Author Comment

by:i950
ID: 18754067
When I double click it, it says "Binary", I have tables when I double click on them I see "long binary data" and those work with my application while "Binary only doesn't work.

Any idea how convert them "completely" to "long binary data"?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18754084
well, i guess you my have lost some information when the data was entered to the binary field.
try the conversion of the field to Long Binary in an empty table and fill the field with long binary data.
0
 

Author Comment

by:i950
ID: 18754092
I didn't lose anything, but Binary field adds some additional info at the beginning of the file, and when I read it using a programming language and then write it to disk I get unnecessary information at the beginning of the file that is corrupting it :(
0
 
LVL 75
ID: 18831411
Did we get a resolution to this question?

mx
0
 
LVL 75
ID: 18871801
??
0
 

Author Comment

by:i950
ID: 18874730
?
0
 

Author Comment

by:i950
ID: 18874731
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Access Query: Text to Date 10 32
ORDER BY 7 38
What are my options to share data with field staff 16 56
Delete table fields 3 15
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question