Software

Ask something
Question Locked

Access Database reach limited capacity

0 helpful
12 replies

by assiram

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a database in MS Access. I splitted the database into front-end and back-end. In the back-end, all my records have attachments (PDF) which made the database reached its limited capacity (2GB) and now, i can no longer add record. My database is still growing and who knows how much the database size would grow in the coming months. I have made research about increasing the capacity of Access but it is not possible.

My questions are:

1. Is there any database which can handle large amount of records without limitations?

2. What is the easiest and best engine to migrate my database? If the best option is MySQL, I tried but I dont know how to start.

Please help. Would appreciate it very much.

12 replies

by assiram

Thanks for the reply.

Actually, I did this already (using hyperlinks) but the problem is, I have 3 PDF files (which by the way will come from different folders at times) attached to one record and hyperlinks allows one link only per record.

by yovev

Hi,
i'd recommend migrating to MS SQL where even the free versions have better limitations. Also Microsoft is offering good enough migration tools that can do the dirty job for you when it comes to migrate from Access to MS SQL.
At the following link you can see a comparison chart for different MS SQL Editions:
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx

However maybe there are many converters out there between MS Access and MySQL but i haven't found the right one yet.

by assiram

Thanks for the reply.

I already downloaded MS SQL Server 2008. I will try to convert the back-end. I guess i'll just have to figure out how.

by tony hopkinson

Do you want the same answer?
Easiest solution is to replace the access back end with sql server.

Any full RDBMS will do better than Access, as I said last time though
You real problem is storing the PDFs in the database.

It's not an ideal thing to do at the best of times, but Access is truly awful with it.

Look at storing the files in a suitable directory structure and then storing a URL for them in the db. It's more work than you are doing now, but a lot less than replacing the backend.

by larryd4

Yes the best bet is pull the pdfs out and store them somewhere else! Preferabbly in a share that can be referenced via a link! Even in a MS-SQL or MySQL setup it will be worth it.

by assiram

Thanks for the reply.

Actually, I used hyperlinks but the problem is, I have 3 PDF files (which by the way will come from different folders at times) attached to one record and hyperlinks allows one link only per record.

Is SQL capable of linking 3 PDFs or 3 URLs in one record?

by tony hopkinson

If you have three pdfs in as blobs now e.g
three records or three columns, then all you need is three links in the same way.

by assiram

It will be a huge work but I will consider that. Thanks!

by tony hopkinson

than changing the entire backend
Don't forget to back it up.

Hmm On backups, once the PDFs are separate to the mdd, don't forget you need to back up the new mdb and the images

Personally I'd have my add document function copy the original and then do the database link to the copy.
Your biggest problem will be getting enough space in the MDB to change it, probaly worth creating a new MDB with the new structure and then writing a doodad to read from current and write new stuff and save pdf somewhere useful.
Then you can do the UI from that. Once it's proven clear out and import again then swap it in.

This discussion is closed to new replies.