2GB size limitation workaround

[Karen]

I'm approaching the 2GB size limitation. Compacting the
database has no effect and the other workaround to split
the database is not an option (for us).

Does anybody has any other recommendations?

PS
Some background information: this is an Access 2000
database with pictures and it was decided to embed the
pictures in order to activate them using PhotoEditor and
so that files can be moved to other locations on the disk.

 

[Roger Carlson]

There is no workaround for the 2GB database size other than splitting the
database into multiple databases less than 2GB.

However, it is *very* *bad* *practice* to store the pictures IN Access.
Sure, it's possible, but Access is not really designed for it, as you have
discovered.

One solution is to leave the pictures in a directory on the hard drive and
only store the path and file name. You can then load the picture
programmatically on a form or report. On my websites (see sig below) is a
small sample database called "Picture.mdb" which illustrates how.

If you need to load them in an external program, you can store the path as a
hyperlink. Clicking the hyperlink will open the file in the associated
external program. Another sample: "SetHyperlink.mdb" illustrates how to do
that.

You can also move files around your harddrive or network using Access with
the Name(), FileCopy(), Dir(), and Kill() functions, which all work in the
Operating System on files.

Your only other option is to move up to a more powerful database engine like
Oracle or SQL Server.

 

[John Vinson]

I'm approaching the 2GB size limitation. Compacting the
database has no effect and the other workaround to split
the database is not an option (for us).

Does anybody has any other recommendations?

PS
Some background information: this is an Access 2000
database with pictures and it was decided to embed the
pictures in order to activate them using PhotoEditor and
so that files can be moved to other locations on the disk.

If you want the pictures to be available to PhotoEditor and to be able
to move them to other locations on the disk - just leave them on the
disk! You can store a path and filename in a short Text field in your
table. See

http://support.microsoft.com/?id=148463

or do a Google Groups search of the microsoft.public.access.* groups;
there are some ways to store images in BLOB's without letting Access
know that it's an image (thus avoiding much of the bloat).

 

[Karen]

Dear Roger and John, many thanks for your feedback. I am
aware - with hindside - that storing the pictures in
Access was a bad idea but a lot of work has been put into
entering (200) records that way. Do any of you know of a
way to convert the embedded pictures to a linked one or do
we have to do that manually? (setting the picture type to
link applies to new records only, not for existing ones :-(

Once again thanks for your help!

Karen

 

[John Vinson]

Dear Roger and John, many thanks for your feedback. I am
aware - with hindside - that storing the pictures in
Access was a bad idea but a lot of work has been put into
entering (200) records that way. Do any of you know of a
way to convert the embedded pictures to a linked one or do
we have to do that manually? (setting the picture type to
link applies to new records only, not for existing ones :-(

Alas, Linked is just as bad as Embedded as far as bloating goes.

You'll need to find someone with more image expertise than me! good
luck!

 

[Roger Carlson]

Unfortunately, I don't know how to save a picture that has been stored in
the database back to a file. Perhaps there is some ActiveX image control
that allows you to SaveAs, but I don't know of any. Still, it's a place to
start looking.

Sorry I can't be more help.

 

[Adrian Jansen]

Unfortunately, I don't know how to save a picture that has been stored in
the database back to a file. Perhaps there is some ActiveX image control
that allows you to SaveAs, but I don't know of any. Still, it's a place to
start looking.

Sorry I can't be more help.

Stephen Lebans has functions to do this on his website www.lebans.com


--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control