Storing Images and Files in SQL2000 with a project DB as front end.

[Angel G]

I have a Project database (Access 2003) that I am using to store Files and
Images into SQL2000. I am using the OLE_CREATE_EMBED Action to store the
image or document into SQL (Image type field) It works great! however The
databse is just growing like crazy. We have other applications that do the
same on sql2000 (not using access as a front end) and they are not growing
as much. When I create SQL Reports I am not able to display the images. It
seams as if acces is storing the data in a propietary way. However, Access
is able to display images and documents in a snap. The thing that concerns
me is the Database growth. Can anyone help me? Should I have access convert
the Data to binary when inserting into the SQL2000 table? if Yes can you
provide a sample? perhaps I should not be doing this via access Project db?
Our company requires me to store the docs and Pics in the sql table. So
linking files or pictures is not an option. Any help is appreciated.

 

[Paul Shapiro]

I believe Access 2003 converts all images to bitmaps when storing them.
Bitmaps can be significantly larger than .jpg since they don't have any
compression. Check the format of the ole objects stored in SQL Server from
the Access front end to see if this is correct. I'm not sure Access does the
same thing when storing into SQL Server.

If that's the issue you could try writing the files directly into SQL Server
with VBA code, without using Access linked tables. Or try Access 2007 which
I believe can process more native graphic formats, including .jpg and other
compressed formats.

 

[Angel G]

Thank you!

I believe Access 2003 converts all images to bitmaps when storing them.
Bitmaps can be significantly larger than .jpg since they don't have any
compression. Check the format of the ole objects stored in SQL Server from
the Access front end to see if this is correct. I'm not sure Access does
the same thing when storing into SQL Server.

If that's the issue you could try writing the files directly into SQL
Server with VBA code, without using Access linked tables. Or try Access
2007 which I believe can process more native graphic formats, including
.jpg and other compressed formats.

 

[Angel G]

Does anyone have a sample code that I could use to achieve this (using VB
code)?