title: Extract Blob data from Access via vbscript
body: <p>So I have a vbscript that pulls data from an access database</p>
  
  <p>Example:</p>
  
  <pre><code>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; " &amp; "Data Source =" &amp; 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
  </code></pre>
  
  <p>The export looks like this:</p>
  
  <pre><code>19199439,person
  29034234,john
  </code></pre>
  
  <p>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</p>
  
  <p>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.</p>
  
  <p>The string that is in the BLOB Field is jpg files</p>
  
  <p>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.</p>
  
  <p>Open to other ideas, but i do need to be able to script it and run it from an access database (unfortunately)</p>
  
  <p>(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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>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; " &amp; "Data Source =" &amp; 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\" &amp; rs("AimsIDNumber") &amp; ".jpg", 2 ''adSaveCreateOveWrite
  mstream.close
  rs.movenext
  
  Loop
  </code></pre>