|
|
Posted by Marcus on 08/29/06 21:22
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_binary;UID=sa;PWD=1qaz2wsx;")
Set con = wks.OpenConnection("test", , , "ODBC;Driver={SQL
Server};Server=marcus\sqlexpress;Database=test_binary;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=test_binary;Uid=sa;Pwd=1qaz2wsx"
'strConn = "Driver={SQL Native
Client};Server=marcus\sqlexpress;Database=test_binary;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
Navigation:
[Reply to this message]
|