Adding attachments causes database to crash

[docmike]

Sorry for posting this again. I gather someone has replied but I think my
post has been deleted as I can't find it!

I created a new DB in Access 2007 to store botanical records. Each species
has details entered on a main form, and site locations are entered on a
linked subform. I am attaching .jpg images and other compatible files on the
locations subform using an attachments control. Every so often a dialog box
appears on closing the
attachments pop-up which says 'Cannot open database ".It may not be a
database that your application recognizes, or the file may be corrupt' When I
click on help it lists this as Error 3049. Repairing the database doesn't help
Eventually the database crashes and I have to restore from a backup which is
frustrating as I am having to re-enter lots of data
My DB is 1.97GB in size. Each attacment file is less than the maximum 256 MB
Access allows, but I am unsure about the advice on the MS Office site which
says 'You can attach a maximum of two gigabytes of data (the maximum size for
an Access database). ' Does this mean you can only have 2GB per record, or
for the whole DB? The size of my .jpeg files is large and if the latter is
true then I have probably exceeded the 2GB total
Any comments would be appreciated (including the one that has gone missing!)

Mike Shaw

 

[Douglas J. Steele]

It's 2 Gb for the entire database.

While Access 2007 does a far better job dealing with attachments than
previous versions, you still might consider using the approach previously
recommend of keeping the images outside of the database and simply storing
the file name in Access.

Access MVP Larry Linson suggests the following:

The sample imaging databases at http://accdevel.tripod.com illustrate three
approaches to handling images in Access, and the download includes an
article discussing considerations in choosing an approach. Two of the
approaches do not use OLE Objects and, thus, avoid the database bloat, and
some other problems, associated with images in OLE Objects.

If you are printing the images in reports, to avoid memory leakage, you
should also see MVP Stephen Lebans' http://www.lebans.com/printfailures.htm.
PrintFailure.zip is an Access97 MDB containing a report that fails during
the Access formatting process prior to being spooled to the Printer Driver.
This MDB also contains code showing how to convert the contents of the Image
control to a Bitmap file prior to printing. This helps alleviate the "Out of
Memory" error that can popup when printing image intensive reports.

If you've already starting storing images in your database, and you'd like
to export them all, see: http://www.lebans.com/oletodisk.htm

 

[a a r o n . k e m p f]

Of course, Jet still runs like crap when you put objects into it.

SQL Server has always been much much much more reliable in this
regard.
Most importantly, you have a 4gb limit, not a 2gb limit with SQL
Server.

Also-- SQL Server now has FILESTREAM support-- which does exactly
this-- it keeps files in the database, even though the files aren't
really in the database.

It's the best of both worlds, and it will never be available in Jet--
because Jet is obsolete.

 

[docmike]

Thanks Douglas

I thought this may be the case and have therefore created smaller copies of
my .jpeg images to replace the 5-10MB files I was attaching. This has shrunk
my mdb from 2GB to 0.6GB, which should keep me going for now. I will look
into your other suggestions

Mike