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
