how do i convert binary values obtained from sql server 2005 to varchar and display in ms access 2007 queries

Posted on 2008-10-16
Last Modified: 2010-04-21
i am trying to create a query in ms access where in i am obtaining a binary value from sql server 2005. i am not able to display the same in ms access 2007. it is showing some junk values. i actually want to convert this to varchar so that i can link that field to another varchar field in the tables to display the values. please help me out. i am trying to create a custom function in ms access which convert binary to varchar so that i can use that varchar in queries
Question by:karthik_kota
  • 5
  • 2

Expert Comment

by:Sander Stad
ID: 22730668
There is a function in MS SQL Server 2005 that converts it for you. This is the function: fn_varbintohexstr
Look at this for more information:

Expert Comment

by:Sander Stad
ID: 22730677
In conjunction with the method you could use a linked server to the access database to get the data from the MS SQL server to the table in MS Access

Author Comment

ID: 22730842
yes i do know about the function fn_varbintohexstr. also there is dbo.hex function. but i dont want to make any changes in sql 2005. i am actually looking for a custom function(vb script) in ms access which converts binary to varchar and displays the binary value properly(instead of using junk values) when used in ms access queries and should be able to link that field to another varchar field to obtain values
Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.


Author Comment

ID: 22730930
the binary data is like this "0x174795BED6B1B141B55628E500FC50FC". this is of binary datatype. i want to convert this to varchar format by creating a custom function in ms access. please help me out. i dont want to make any changes in sql

Author Comment

ID: 22730937
is there any function in ms access which does that.

Author Comment

ID: 22737827
ok i will make it simple. i have persid(varchar) and dbo.chg. uuid(binary). both are from different tables. i have imported these tables from sql server 2005. now i want to link these two fields in ms access 2007 query ( when contact.persid = chg.uuid) and get the results displayed. but since both are of different datatypes. i am not able to get the results from querry. please help me out in having a custom function to convert binary to varchar. ultimately i want to see the results from the querry overcoming the problem in datatype

Accepted Solution

jchevali earned 500 total points
ID: 23166280
There is a way:

Navigate to this address and borrow the TSToHex() function:

Change the function parameter to be a String instead of a rdocolumn.
Change the For command in TSToHex() to read like this:
For b = 1 To LenB(sBinRep)

In Access, add the function to a public code module.
Add a linked table to the SQL table containing your binary(16) field.
Add a new Access query, switch to SQL view, and enter the following:
SELECT TSToHex(LeftB(uuid,16)) FROM dbo_chg;
Switch to data view.

The contents of the binary field should now be displayed as a hex string.

Author Closing Comment

ID: 31506702
perfect sollution

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pass through dll 2 91
Problem to open text file 11 128
VB Script: create txt file with DateStamp and IP Adress 6 27
VB script to continue despite error 2 16
Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

829 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