467,161 Members | 1,040 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,161 developers. It's quick & easy.

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
  • viewed: 7804
Share:
2 Replies
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Rune Froysa | last post: by
121 posts views Thread by typingcat@gmail.com | last post: by
9 posts views Thread by Water Cooler v2 | last post: by
4 posts views Thread by mpietrzyk@autograf.pl | last post: by
14 posts views Thread by Mintyman | last post: by
4 posts views Thread by Marc | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.