VBA - Upload & Import Images From Postgres BYTEA

Hey All,

I’ve been at this all week and I’m about to self destruct! @#$# Any ideas or solutions to my major problem will be most appreciated. And may the Karma Gods bless you with a thousand camels if you can share your thoughts.

Here is the situation. In my app I have a common dialog window that comes up where a user can select an image. Then I want to send that image to a database where it is stored. And later retrieve that image to a browser. Sounds all too simple!

Also, the data type is BYTEA for the image.

1. Get Image Path
2. Insert Image to db
3. Retrieve Image through browser

#1 is very easy.
Public Function getFilePath() As String
On Error Resume Next
Dim dlgOpen As FileDialog
Set dlgOpen = Application.FileDialog(DialogType:=msoFileDialogOpen)
With dlgOpen
.Title = "Select a XML file"
.Filters.Clear
.Filters.Add "Images", "*.wmf;*.gif; *.jpg; *.jpeg", 1
.FilterIndex = 1
.AllowMultiSelect = False
.Show
End With
getFilePath = dlgOpen.SelectedItems(1)
End Function

#2 is where I fall apart.
I’ve tried streaming the image through ADODB.Stream. It fails on the .Read

Set strStream = New ADODB.Stream
With strStream
.Type = adTypeBinary
.Open
.LoadFromFile getFilePath
End With
RS.Fields("image").Value = .Read

Another alternative would be to force the image into a binary variable then update RS. I tried this but fails in VBA

This is my statement when I use it directly in Postgres. I didn't get an error and I'm assuming it took it.

insert into images(image) values('c:/test.gif'::bytea)

I once blogged about VBA and blob fields in Firebird, adapting an excellent example for MySQL, I bet PostgreSQL works the same.
See here for reference

I get an error on rs!file_blob = mystream.Read
Run-time error Multi-step operation generated errors. check each status value.

the present data type it is trying to write to is bytea. I also tried text and oid type but still get errors. However the other data did write properly just not the actual file.

You might want to check this tutorials, here and here

It looked like it started to work but failed because the lo is not installed on my Windows Box. I found the contribution but is available for Linux from what I can tell.

Thanks Pabloj for your help! All your tutorial link help me piece it all together. I got it to work beautifully! I'm going to clean up the code and make it a little more elegant and will repost the solution tonight.

Thanks Again! What are you going to do with all those Camels now? lol

Great! That's very kind of you
Smoke them all?