Delete query to remove only pictures from attachments

[InfernoJaffa]

Hi All,

Long story short, my database is at 1.9GB so I need to perform drastic action before I hit the 2GB limit.

Is there any way to delete only pictures files from the attachments field? I have PDF's which aren't too large (150mb in total accrued over 2 years) which i would like to keep stored in the database.

Thanks in advance.

Andy

 

[Ranman256]

you could create another DB that has only this 1 table.
link the table into the main db then compress the db.
It will save space on your main db and keep your attachments.
access wont know the difference.

 

[InfernoJaffa]

you could create another DB that has only this 1 table.
link the table into the main db then compress the db.
It will save space on your main db and keep your attachments.
access wont know the difference.

Hi, thanks for your suggestion.

Sadly, most the size (1.8GB of it) is in that one field so splitting down further won't help.

 

[isladogs]

First do a backup...

Then
1. Replace the attachment field with a field the location of the item so you can use code linking to that location
2. Repopulate the new field either manually or with suitable code depending on how many items are involved.
3. Delete the attachment field
4. Compact the database

 

[Ranman256]

You still have to compact. It DOES help.

 

[InfernoJaffa]

First do a backup...

Then
1. Replace the attachment field with a field the location of the item so you can use code linking to that location
2. Repopulate the new field either manually or with suitable code depending on how many items are involved.
3. Delete the attachment field
4. Compact the database

Hi Ridders,

Are you talking about transferring the data to the disk drive and hyperlinking to it? I spent a couple of days looking into getting this working but struggled with it which is why I decided to delete the bulky, less important data.

 

[InfernoJaffa]

You still have to compact. It DOES help.

The raw data itself is too large (I exported it all with a VBA macro).

 

[isladogs]

Hi Ridders,

Are you talking about transferring the data to the disk drive and hyperlinking to it? I spent a couple of days looking into getting this working but struggled with it which is why I decided to delete the bulky, less important data.

Yes I was as I assumed you didn't want to lose it.

However if you want to keep a few important items only:
- follow my previous suggestion but just add links to the items you want to keep in step 2

As has already been said more than once, do compact after deleting the attachment field.
It really will make a difference

 

[InfernoJaffa]

Yes I was as I assumed you didn't want to lose it.

However if you want to keep a few important items only:
- follow my previous suggestion but just add links to the items you want to keep in step 2

As has already been said more than once, do compact after deleting the attachment field.
It really will make a difference

This is what i was struggling with before, can't seem to find any step by step methods online...?

 

[Minty]

Menu - goto Database Tools, select compact and repair database.

For your own satisfaction, write down the file size, before and after.

 

[InfernoJaffa]

Menu - goto Database Tools, select compact and repair database.

For your own satisfaction, write down the file size, before and after.

Hi Minty, Sorry I was referring to ridders initial reply for exporting attachment files and linking them to the database.

 

[Minty]

Hi Minty, Sorry I was referring to ridders initial reply for exporting attachment files and linking them to the database.

Ah - sorry mis-read / wrong end of stick.

I think the simplest route would be to just link to them as a table in another database with just the attachments in them, but I've not followed the thread properly so this may have already been suggested.