VBA, Extract Image out of OLE Object Field to Filesystem

Extract Image out of OLE Object Field to Filesystem

Author	Message
Spencer Tabber #1 / 7	Extract Image out of OLE Object Field to Filesystem I would like to be able to extract out an Image that is being stored in an Access 97 DB in an OLE Object Field. I would like to be able to do this via VBScript hopefully so I can have an automated process that extracts the images out of an entire table and saves them to a file system. Any ideas? Hints? Thanks, Spencer Tabbert
Wed, 16 Jul 2003 04:54:43 GMT

Stephen Leban #2 / 7	Extract Image out of OLE Object Field to Filesystem Have a look at: http://www.lebans.com/oletodisk.htm HTH Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Quote: > I would like to be able to extract out an Image that is being stored in an > Access 97 DB in an OLE Object Field. I would like to be able to do this via > VBscript hopefully so I can have an automated process that extracts the > images out of an entire table and saves them to a file system. Any ideas? > Hints? > Thanks, > Spencer Tabbert
Wed, 16 Jul 2003 05:47:02 GMT

Spencer Tabber #3 / 7	Extract Image out of OLE Object Field to Filesystem Looking at your code it appears this is a rather complex process. I was under the impression it was much simpler. I also am trying to read out JPG's not BMP's. I got the following snipet of code from Microsoft that is supposed to extract an Image out of an Image field for SQL Server and tried to convert it to Access but the file is unrecognizable when I save it. I tried porting the db to a SQL Server DB and running the script and got the same results. Const adTypeBinary = 1 Const adSaveCreateOverWrite = 2 Const adOpenKeyset = 1 Const adLockOptimistic = 3 Dim cn, rs, mstream Set cn = CreateObject("ADODB.Connection") 'SQL Server 'cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DimensionHomes;Data Source=Columbus" 'Access cn.Open "DBQ=\\pokemon\mlsdata\dimensionhomes\dimensionhomes.mdb;Driver={Microsoft Access Driver (*.mdb)};" Set rs = CreateObject("ADODB.Recordset") 'SQL Server 'rs.Open "Select MlsNum, Photo from fd_Dimension", cn, adOpenKeyset, adLockOptimistic 'Access rs.Open "Select MlsNum, Photo from comps", cn, adOpenKeyset, adLockOptimistic Do While Not rs.Eof Set mstream = CreateObject("ADODB.Stream") mstream.Type = adTypeBinary mstream.Open mstream.Write rs.Fields("Photo").Value mstream.SaveToFile "c:\dimension\" & rs.Fields("MlsNum").Value & ".jpg", adSaveCreateOverWrite Set mstream = Nothing rs.movenext Loop Set rs = Nothing Set cn = Nothing Quote: > Have a look at: > http://www.lebans.com/oletodisk.htm > HTH > Stephen Lebans > http://www.lebans.com > Access Code, Tips and Tricks > > I would like to be able to extract out an Image that is being stored > in an > > Access 97 DB in an OLE Object Field. I would like to be able to do > this via > > VBscript hopefully so I can have an automated process that extracts > the > > images out of an entire table and saves them to a file system. Any > ideas? > > Hints? > > Thanks, > > Spencer Tabbert
Wed, 16 Jul 2003 06:38:01 GMT

Stephen Leban #4 / 7	Extract Image out of OLE Object Field to Filesystem You have stored the Image as an OLE object. I will hazard a guess that in the SQL example the JPEG is simply stored as a Binary file without any OLE or any other type of wrapper. Since this OLE wrapper is undocumented you cannot easily get at the original JPEG data. Short of using Automation to control a third party Paint Program you are stuck with 2 possible solutions that I know of. 1) Article ID: Q119395 How to extract a Metafile from an OLE control. This is applicable since the Jpeg is stored as a Bitmap(DIB) wrapped within a Metafile. The example is for VB but you could convert it to an Access solution if you are familiar with the Metafile and Device Context API's. 2) My solution I pointed you to at: http://www.lebans.com/oletodisk.htm If you want the Image to end up in JPEG format just use Paint SHop Prop or any other Paint program that supports batch conversion. It's very simple to do in Paint Shop Pro. A thought just occured to me that it might be possible to search the file for the JPEG header but I have not tried this...yet.<grin> Good Luck. Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Quote: > Looking at your code it appears this is a rather complex process. I was > under the impression it was much simpler. I also am trying to read out > JPG's not BMP's. I got the following snipet of code from Microsoft that is > supposed to extract an Image out of an Image field for SQL Server and tried > to convert it to Access but the file is unrecognizable when I save it. I > tried porting the db to a SQL Server DB and running the script and got the > same results. > Const adTypeBinary = 1 > Const adSaveCreateOverWrite = 2 > Const adOpenKeyset = 1 > Const adLockOptimistic = 3 > Dim cn, rs, mstream > Set cn = CreateObject("ADODB.Connection") > 'SQL Server > 'cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security > Info=False;Initial Catalog=DimensionHomes;Data Source=Columbus" > 'Access > cn.Open "DBQ=\\pokemon\mlsdata\dimensionhomes\dimensionhomes.mdb;Driver={Microso ft - Hide quoted text - - Show quoted text - Quote: > Access Driver (*.mdb)};" > Set rs = CreateObject("ADODB.Recordset") > 'SQL Server > 'rs.Open "Select MlsNum, Photo from fd_Dimension", cn, adOpenKeyset, > adLockOptimistic > 'Access > rs.Open "Select MlsNum, Photo from comps", cn, adOpenKeyset, > adLockOptimistic > Do While Not rs.Eof > Set mstream = CreateObject("ADODB.Stream") > mstream.Type = adTypeBinary > mstream.Open > mstream.Write rs.Fields("Photo").Value > mstream.SaveToFile "c:\dimension\" & rs.Fields("MlsNum").Value & ".jpg", > adSaveCreateOverWrite > Set mstream = Nothing > rs.movenext > Loop > Set rs = Nothing > Set cn = Nothing > > Have a look at: > > http://www.lebans.com/oletodisk.htm > > HTH > > Stephen Lebans > > http://www.lebans.com > > Access Code, Tips and Tricks > > > I would like to be able to extract out an Image that is being stored > > in an > > > Access 97 DB in an OLE Object Field. I would like to be able to do > > this via > > > VBscript hopefully so I can have an automated process that extracts > > the > > > images out of an entire table and saves them to a file system. Any > > ideas? > > > Hints? > > > Thanks, > > > Spencer Tabbert
Wed, 16 Jul 2003 07:56:50 GMT

Mark #5 / 7	Extract Image out of OLE Object Field to Filesystem THBImage at www.thb.nu contains some examples and documentation about viewing images from your DB. The examples contain a piece of VB code to load/save an image from/to the DB using the AppendChunk methods. I'm sure it's worth a look Mark Meloy THB Componentware Components for Developers http://www.thb.nu, Quote: > I would like to be able to extract out an Image that is being stored in an > Access 97 DB in an OLE Object Field. I would like to be able to do this via > VBscript hopefully so I can have an automated process that extracts the > images out of an entire table and saves them to a file system. Any ideas? > Hints? > Thanks, > Spencer Tabbert
Thu, 17 Jul 2003 06:45:13 GMT

Stephen Leban #6 / 7	Extract Image out of OLE Object Field to Filesystem Mark the GetChunk method would only work if the original Image was written to the OLE field with the AppendChunk method. I am guessing, and could be wrong, that in this case the Image was embedded as an OLE object so it can no longer be directly read from the field. Stephen Lebans http://www.lebans.com Access Code, Tips and Tricks Quote: > THBImage at www.thb.nu contains some examples and documentation about > viewing images from your DB. The examples contain a piece of VB code to > load/save an image from/to the DB using the AppendChunk methods. > I'm sure it's worth a look > Mark Meloy > THB Componentware > Components for Developers > http://www.thb.nu, > > I would like to be able to extract out an Image that is being stored in an > > Access 97 DB in an OLE Object Field. I would like to be able to do this > via > > VBscript hopefully so I can have an automated process that extracts the > > images out of an entire table and saves them to a file system. Any ideas? > > Hints? > > Thanks, > > Spencer Tabbert
Thu, 17 Jul 2003 07:46:58 GMT

Philip Le #7 / 7	Extract Image out of OLE Object Field to Filesystem Stephen, Thanks SO MUCH for posting your database, it works great. As for changing formats, I replaced the image control on the form with Pegasus ImageExpress control, and used it's image conversion features to save the file back out in the format I wanted, TIF, JPG, GIF, etc. all supported. It's 4 more lines of code and you're done. BTW, this was in Access2K hitting against a SQL7 database. Very fast, very easy. Philip Lee Atlanta, GA Quote: > You have stored the Image as an OLE object. I will hazard a guess that > in the SQL example the JPEG is simply stored as a Binary file without > any OLE or any other type of wrapper. Since this OLE wrapper is > undocumented you cannot easily get at the original JPEG data. Short of > using Automation to control a third party Paint Program you are stuck > with 2 possible solutions that I know of. > 1) Article ID: Q119395 > How to extract a Metafile from an OLE control. This is applicable since > the Jpeg is stored as a Bitmap(DIB) wrapped within a Metafile. The > example is for VB but you could convert it to an Access solution if you > are familiar with the Metafile and Device Context API's. > 2) My solution I pointed you to at: > http://www.lebans.com/oletodisk.htm > If you want the Image to end up in JPEG format just use Paint SHop Prop > or any other Paint program that supports batch conversion. It's very > simple to do in Paint Shop Pro. > A thought just occured to me that it might be possible to search the > file for the JPEG header but I have not tried this...yet.<grin> > Good Luck. > Stephen Lebans > http://www.lebans.com > Access Code, Tips and Tricks > > Looking at your code it appears this is a rather complex process. I > was > > under the impression it was much simpler. I also am trying to read > out > > JPG's not BMP's. I got the following snipet of code from Microsoft > that is > > supposed to extract an Image out of an Image field for SQL Server and > tried > > to convert it to Access but the file is unrecognizable when I save it. > I > > tried porting the db to a SQL Server DB and running the script and got > the > > same results. > > Const adTypeBinary = 1 > > Const adSaveCreateOverWrite = 2 > > Const adOpenKeyset = 1 > > Const adLockOptimistic = 3 > > Dim cn, rs, mstream > > Set cn = CreateObject("ADODB.Connection") > > 'SQL Server > > 'cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist > Security > > Info=False;Initial Catalog=DimensionHomes;Data Source=Columbus" > > 'Access > > cn.Open > "DBQ=\\pokemon\mlsdata\dimensionhomes\dimensionhomes.mdb;Driver={Microso > ft > > Access Driver (*.mdb)};" > > Set rs = CreateObject("ADODB.Recordset") > > 'SQL Server > > 'rs.Open "Select MlsNum, Photo from fd_Dimension", cn, adOpenKeyset, > > adLockOptimistic > > 'Access > > rs.Open "Select MlsNum, Photo from comps", cn, adOpenKeyset, > > adLockOptimistic > > Do While Not rs.Eof > > Set mstream = CreateObject("ADODB.Stream") > > mstream.Type = adTypeBinary > > mstream.Open > > mstream.Write rs.Fields("Photo").Value > > mstream.SaveToFile "c:\dimension\" & rs.Fields("MlsNum").Value & > ".jpg", > > adSaveCreateOverWrite > > Set mstream = Nothing > > rs.movenext > > Loop > > Set rs = Nothing > > Set cn = Nothing > > > Have a look at: > > > http://www.lebans.com/oletodisk.htm > > > HTH > > > Stephen Lebans > > > http://www.lebans.com > > > Access Code, Tips and Tricks > > > > I would like to be able to extract out an Image that is being > stored > > > in an > > > > Access 97 DB in an OLE Object Field. I would like to be able to > do > > > this via > > > > VBscript hopefully so I can have an automated process that > extracts > > > the > > > > images out of an entire table and saves them to a file system. > Any > > > ideas? > > > > Hints? > > > > Thanks, > > > > Spencer Tabbert
Tue, 09 Sep 2003 02:17:03 GMT

Page 1 of 1

[ 7 post ]