473,222 Members | 1,738 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,222 software developers and data experts.

Using DAO to access binary data in Sql Server 2005?

(Appologies if this group isn't the best place for this post)

Is it possible to use DAO 3.6 to access binary data (varbinary(max)) in
Sql Server 2005? I have images and sound in a Sql 2005 DB that I need
to retrieve (and write) with DAO (ADO and ADO.Net are not options as
this is legacy code that can't be changed).

Thx,
Marcus

Aug 29 '06 #1
2 8170
Marcus wrote:
Is it possible to use DAO 3.6 to access binary data (varbinary(max)) in
Sql Server 2005? I have images and sound in a Sql 2005 DB that I need
to retrieve (and write) with DAO
The following works for me...
Public Sub BinaryTest()
Dim wks As DAO.Workspace, con As DAO.Connection, _
rst As DAO.Recordset
Dim foo As Variant, b As Variant

Set wks = CreateWorkspace("wks1", "admin", "", dbUseODBC)
Set con = wks.OpenConnection("Escona", , , _
"ODBC;DSN=MAS_EsconaTutorial;UID=sa;PWD=whatever;" )

Set rst = con.OpenRecordset( _
"SELECT TextCol FROM AMGR_Letters_Tbl " & _
"WHERE Record_Id=44", _
dbOpenSnapshot)

foo = rst!TextCol ' array of bytes

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
wks.Close
Set wks = Nothing

Open "C:\__tmp\ding.wav" For Binary As #1
For Each b In foo
Put #1, , CByte(b)
Next
Close #1

End Sub

Aug 29 '06 #2
Thanks, Gord. For some reason I had trouble connecting with
Workspace.OpenConnection. I was using a DSN-Less connection. My code
looked like this:

Public Sub BinaryTest2()
Dim wks As DAO.Workspace
Dim con As DAO.Connection
Dim rsMyRS As DAO.Recordset
Dim data() As Byte

Set wks = CreateWorkspace("wks1", "admin", "", dbUseODBC)
'Set con = wks.OpenConnection("Escona", , , "Driver={SQL Native
Client};Server=marcus\sqlexpress;Database=test_bin ary;UID=sa;PWD=1qaz2wsx;")
Set con = wks.OpenConnection("test", , , "ODBC;Driver={SQL
Server};Server=marcus\sqlexpress;Database=test_bin ary;Uid=sa;Pwd=1qaz2wsx")

Set rsMyRS = con.OpenRecordset("SELECT location from Devices ",
dbOpenSnapshot)

data = rsMyRS("GraphicEnabled") ' array of bytes

rsMyRS.Close
Set rsMyRS = Nothing
con.Close
Set con = Nothing
wks.Close
Set wks = Nothing

Do While Not rsMyRS.EOF
Debug.Print rsMyRS("GraphicEnabled")
data = rsMyRS("GraphicEnabled_BINARY")

Dim fileName As String
fileName = rsMyRS("GraphicEnabled")

Open fileName For Binary As #1
Put #1, , data
Close #1
rsMyRS.MoveNext
Loop
End Sub
When it hit the OpenConnection line, I got a runtime error "ODBC --
call failed". So I changed it from OpenConnection to OpenDatabase, like
this, and it worked fine (for retrieving data anyway):
Public Sub BinaryTest1()
Dim strConn As String
Dim dbMyDb As Database
Dim rsMyRS As Recordset
Dim data() As Byte

strConn = "ODBC;Driver={SQL
Server};Server=192.168.1.143\sqlexpress;Database=t est_binary;Uid=sa;Pwd=1qaz2wsx"
'strConn = "Driver={SQL Native
Client};Server=marcus\sqlexpress;Database=test_bin ary;UID=sa;PWD=1qaz2wsx;"

Debug.Print "Connecting to DB..."
Set dbMyDb = OpenDatabase("", False, False, strConn)
Debug.Print "Connected to DB"

Debug.Print "Populating the recordset..."
Set rsMyRS = dbMyDb.OpenRecordset("Select * from Graphics",
dbOpenDynaset, dbSeeChanges)
Debug.Print "Done populating"

If Not rsMyRS.EOF Then
rsMyRS.MoveFirst
End If

Do While Not rsMyRS.EOF
Debug.Print rsMyRS("GraphicEnabled")
data = rsMyRS("GraphicEnabled_BINARY")

Dim fileName As String
fileName = rsMyRS("GraphicEnabled")

Open fileName For Binary As #1
Put #1, , data
Close #1
rsMyRS.MoveNext
Loop

rsMyRS.Close
dbMyDb.Close
Set rsMyRS = Nothing
Set dbMyDb = Nothing

MsgBox ("Finished")
End Sub

Any idea why I was having problems with OpenConnection? Do you know if
Dao.OpenDatabase is any better or worse than Workspace.Openconnection?
Now I will see if I can upload data with no issues to the db.

Thanks,
Marcus


Gord wrote:
Marcus wrote:
Is it possible to use DAO 3.6 to access binary data (varbinary(max)) in
Sql Server 2005? I have images and sound in a Sql 2005 DB that I need
to retrieve (and write) with DAO

The following works for me...
Public Sub BinaryTest()
Dim wks As DAO.Workspace, con As DAO.Connection, _
rst As DAO.Recordset
Dim foo As Variant, b As Variant

Set wks = CreateWorkspace("wks1", "admin", "", dbUseODBC)
Set con = wks.OpenConnection("Escona", , , _
"ODBC;DSN=MAS_EsconaTutorial;UID=sa;PWD=whatever;" )

Set rst = con.OpenRecordset( _
"SELECT TextCol FROM AMGR_Letters_Tbl " & _
"WHERE Record_Id=44", _
dbOpenSnapshot)

foo = rst!TextCol ' array of bytes

rst.Close
Set rst = Nothing
con.Close
Set con = Nothing
wks.Close
Set wks = Nothing

Open "C:\__tmp\ding.wav" For Binary As #1
For Each b In foo
Put #1, , CByte(b)
Next
Close #1

End Sub
Aug 29 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Rune Froysa | last post by:
Trying something like:: import xmlrpclib svr = xmlrpclib.Server("http://127.0.0.1:8000") svr.test("\x1btest") Failes on the server with:: xml.parsers.expat.ExpatError: not well-formed (invalid...
121
by: typingcat | last post by:
First of all, I'm an Asian and I need to input Japanese, Korean and so on. I've tried many PHP IDEs today, but almost non of them supported Unicode (UTF-8) file. I've found that the only Unicode...
6
by: ransoma22 | last post by:
I developing an application that receive SMS from a connected GSM handphone, e.g Siemens M55, Nokia 6230,etc through the data cable. The application(VB.NET) will receive the SMS automatically,...
9
by: Water Cooler v2 | last post by:
There are two datatypes for storing binary data type in the SQL Server: 1. binary - for fixed length binary data 2. varbinary - for variable length data My question is: how is data inserted...
4
by: mpietrzyk | last post by:
Hi guys I'm having a nasty problem with bulk copying into a table that has unique identifier column. I'm coding on C++, using ODBC driver. I'm coping from a file containing UID description...
2
by: Marcus | last post by:
(Appologies if this group isn't the best place for this post) Is it possible to use DAO 3.6 to access binary data (varbinary(max)) in Sql Server 2005? I have images and sound in a Sql 2005 DB...
14
by: Mintyman | last post by:
Hi, I want to run queries on a table that has binary fields in it. How do I filter on a binary field? E.g. One of the fields is called 'Account_Manager_ID' which is binary - I would like to do a...
3
by: renjucool | last post by:
hi, i am working electronic document management system store documents word excel adobe in database in binary format in sql server 2005. i want to search the contents from the binary data.I am...
4
by: Marc | last post by:
Hi, I don't get it I cannot get this to work, can somebody give me a hint Table1 contains a field Id which is a GUID as primary key and DATA a string, I want to insert a new row but it does not...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.