My database is now extremely large, approaching 2GB. Need help to split/ other options

[benJAMin14]

My database is approaching 2GB. I have been told at that point there will be issues with the database crashing or having issues. I believe there is a way to store most database information in another file and just pull from that file whenever I try to query anything. Honestly I am not sure, but any suggestions for what to do as my database increases in size would be helpful. Also it has been repaired and compacted several times and is still approaching 2GB

[rpeare]

when was the last time you compacted/repaired the database? There are settings in MS Access to perform that action every time the database is closed now so as long as you're using a front end/back end solution every user should have their own copy of the front end and it wouldn't be an issue to force the compact/repair on close.

if you are already compacting/repairing on a regular basis I would look at archiving some of your data to an 'old' data version of your database.

[benJAMin14]

Sorry I just edit my first post to say it has been compacted fairly regularly. And for the old data version of a database we would still want those records to be queryable (if that's a word). Is there a way to have the information linked?

[GinaWhipp]

Hmm, so do you have tables that hold the *old* data? If so you could split those into another file and still link at them thru the Frontend. That said, sounds like you are storing images or memo fields that are *full* of data. If images are the case then there is another way to do that which will shrink your file considerable. If not you might want to consider upsizing to an SQL Server. The free version gives you 10 gig (double-check that). However, depending on how long this database has been in production mode even 10 gig might not be enough.

[June7]

Is the db currently a split design?

Are you storing files in Attachment or OLEObject field(s)?

[benJAMin14]

We are currently storing files in OLEObject fields as 256 bitmap images. I believe we will want to move to an SQL server, but do any if you know of any good resources for how that is accomplished.

[rpeare]

yeah... don't store ole objects. That's where your bloat is happening. Instead store the path to the file name then, when you need to view the image, open the file with a command line instead

like

.followyperlink (file path & name)

[June7]

That's reason for file size. Most of us recommend NOT storing files within db. Store them external and save filepath in text field. Are these files all images - what format? Use Image control to display external image files (except PDF) in form and report.

Try Google for Access conversion to SQLServer.

[GinaWhipp]

Well, I would first suggest changing the way you are storing images. You need to put them in a folder and only store the path to the image which you can display using an Image control. Doing this will considerably reduce the size of your database.

If you still want to move to an SQL Server, you can start reading here...
https://regina-whipp.com/blog/?p=371