By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,292 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Using DAO to access binary data in Sql Server 2005?

P: n/a
(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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.