通过vbscript从Access提取Blob数据 [英] Extract Blob data from Access via vbscript

问题描述

所以我有一个vbscript，可以从访问数据库中提取数据

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

示例:

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

导出看起来像这样:

19199439,person
29034234,john

我遇到的问题是，当我将名称更改为Blob列时，Blob列为空白.在访问中它只是说:长二进制数据

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.

BLOB字段中的字符串是jpg文件

The string that is in the BLOB Field is jpg files

我的最终目标是使用IDNumber作为文件名来编写将blob数据提取到每列中的脚本.如果我可以通过上述查询导出ID号，然后导出相应的Blob数据，那么我就可以解决其余的问题.

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)

(如果有人知道如何编写脚本，我甚至可以将其从访问sql server导出.我可以很容易地从sql server中提取数据，但这必须是我可以编写脚本才能运行每月一次.

(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.

根据Suing提供的答案，这是我完成整个工作的最终代码.从访问数据库中提取Blob数据，用相应的ID号命名每个Blob字段，然后在每个文件的末尾添加.jpg并将其放入我需要的文件夹中.

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

推荐答案

我很幸运将Blob值写入流文件 试试这个:

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

这篇关于通过vbscript从Access提取Blob数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！