Splitting a database

[ALui]

Hi, I found out today that Access has put a 2GB limit on the size of the database, which seems to be a problem for me.

I have a large table, my patient list contains fields with healthcare number (primary key), Last name, first name, DOB, address, home/other phone, notes, referring MD, city, address, province, provider province, sex, appointment location, time, date and attachments.

My main concern is the attachments in which pdf files of scanned charts will be attached, I was told it would be possible to split the database so that i had tables with my primary key and the text data, and then a table with my primary key and attachments for patients A-F, G-N etc., how would I be able to store a record based on the last name on my backend? The reason is that there will be a LOT of patients and a LOT of charts to attach.

Also, since all my tables would be split up from my main patient table, would I have to change my forms and queries to match the new tables?

 

[RainLover]

Suggest you search this site and Google for Normalization.

You need to do some resarch and understand Normalization before continuing.

 

[Mr. B]

ALui,

In addition to what RainLover as said, you have the wrong concept to what "splitting" the database means. It was not intended for use as you are describing. I do think that you need to reconsider having your "attachments" actually stored in the database. Think about having them in a specific location and just store the path to them.

 

[ALui]

I'm not sure how to store the path to the file, what type of field would this be?

Originally I thought attachments were really handy, and I would scan files and attach them to the database until I realized that even if I deleted the files I could still access them from the database, so the database actually had a copy of the file itself which would eventually make the database too big.

Would storing the path to the scanned file be the best way to link the files to patients in my database?

 

[RainLover]

How is your research on Normalization going?

 

[Mr. B]

All you need is a text field to store the path in. As RainLover says, you may neeed to look at normalizing you database.