Extract Blob data from Access via vbscript

Question

So I have a vbscript that pulls data from an access database

Example:

db = "C:\Users\username\databases\employeeID.mdb"
TextExportFile = "C:\Users\username\databases\Exp.txt"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "Provider = Microsoft.Jet.OLEDB.4.0; " & "Data Source =" & db

strSQL = "SELECT IDNumber+','+Name FROM employeeID "

rs.Open strSQL, cn, 3, 3

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(TextExportFile, True)

a = rs.GetString
f.WriteLine a
f.Close

The export looks like this:

19199439,person
29034234,john

The problem I am running into is that when i change name to the Blob Column, the blob column is blank. In access it just says: Long Binary Data

I believe it has something to do with a Text String vs a Binary String, but i dont know how to set it to pull back that info.

The string that is in the BLOB Field is jpg files

My end goal is to script the extraction of the blob data into each column with the IDNumber as the name of the file. If I can get the above query to export the ID Number then the corresponding blob data, I can take care of the rest.

Open to other ideas, but i do need to be able to script it and run it from an access database (unfortunately)

(I am even open to the idea of exporting it from access to sql server if someone knows how to script it. From a sql server i can pull the data pretty easily, but this has to be something i can script to run once a month.

Reason why: the access database is for photo id software and we want to export the pictures for use in other software and this is how the software stores it.

From the Answer Supplied by Suing here is my final code that does whole shebang. pulls the blob data from the access database, names each blob field with the corresponding Id Number adds .jpg to the end of each file and put it in the folder i need.

db = "C:\Users\amoore19\databases\employeeID.mdb"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set mstream = CreateObject("ADODB.Stream")

mstream.Type = 1 ''adTypeBinary

cn.Open "Provider = Microsoft.Jet.OLEDB.4.0; " & "Data Source =" & db

strSQL = "SELECT AimsIDNumber,photo FROM employeeID WHERE AimsIDNumber like 'A00%' and photo is not null"

rs.Open strSQL, cn

DO WHILE not rs.eof

mstream.Open
mstream.Write rs("photo")
mstream.SaveToFile "C:\images\all\" & rs("AimsIDNumber") & ".jpg", 2 ''adSaveCreateOveWrite
mstream.close
rs.movenext

Loop

Helen · Accepted Answer · 2015-09-16 15:00:19Z

4

I've had luck writing the Blob value to a stream file try this:

db = "C:\Users\username\databases\employeeID.mdb"
TextExportFile = "C:\Users\username\databases\Exp.txt"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set mstream = CreateObject("ADODB.Stream")

mstream.Type = 1 'adTypeBinary

cn.Open "Provider = Microsoft.Jet.OLEDB.4.0; " & "Data Source =" & db

strSQL = "SELECT IDNumber,Name FROM employeeID "

rs.Open strSQL, cn

Do Until rs.EOF
   mstream.Open
   mstream.Write rs("Name")
   mstream.SaveToFile rs("IDNumber"), 2 'adSaveCreateOveWrite
   mstream.close
   rs.MoveNext 
Loop


'Set fs = CreateObject("Scripting.FileSystemObject")
'Set f = fs.CreateTextFile(TextExportFile, True)
'a = rs.GetString
'f.WriteLine a
'f.Close

edited Sep 16, 2015 at 15:00

Helen

99.8k18 gold badges280 silver badges352 bronze badges

answered Apr 13, 2015 at 23:10

Suing

4534 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

cha Over a year ago

You do not need to concatenate the IDNumber and the name. Please read the OP's question. The IDNumber should be the file name, and the second column (whatever its name) is the JPG file binary data

Suing Over a year ago

hi Cha, thanks for your comment.edits have been made

moore1emu Over a year ago

so i tried that code, but im getting an error at line 18 char 1. Error:Type Mismatch

moore1emu Over a year ago

okay i fixed the error by making my sql query a bit more accurate, i add a where statment that says where idnumber is not null and photo is not null. I am not getting the error anymore, but its only exporting one blob file. im giong to work on turning this into a loop. Any info on how to turn it into a loop so it goes through every row would be apperciated

moore1emu Over a year ago

oh adding a .jpg to the end of each file is on my list as welll :) in case you wanted to help me out :)

|

Collectives™ on Stack Overflow

Extract Blob data from Access via vbscript

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related