Attachments table it too large - need alternative 2

[BallunarCrew]

I have a database in Access 2013. There is a table link to another database were attachments are stored. This second database is up against the 2G limit and I need to find an alternative. I have cleaned this file up as much as possible. We have most, if not all the files that are in the attachments database on the server as well. I am considering a method to make a link to each file and just store the links in the database rather than using the attachment data type but I am not sure if this is best. If anyone has done this, was it successful and did you find an easy method to make and store all those links? All the files are not stored in the same folders on the server. This is for patient's medical record data and the files are stored in the individual patient's folders on the server. There is nothing in the file name to use to link the file back to the patient ID in the database so I am afraid this will be a manual process.

 

[MajP]

Use a hyperlink field. Then you simply browse to the file location to make the link. At two gigs it would take some time.

http://www.addictivetips.com/microsoft-office/create-hyperlink-field-in-access-2010/

If creating the links will take to long, you can write code to export all the attachments into a new set of folders, and create a hyperlink to the new file locations.

 

[Blorf]

If your directory structure is consistent, and you are limiting all attachments for patient X to the folder called X, then you don't even need to store links. If life is simple (it never is) you can simply use a few lines of code like:

X = PatientID
MyFile = dir ("\\MyServer\PatientAttachments\" & X & "\*.*")
if MyFile <> "" then followhyperlink MyFile

This assumes only one attachment per patient and that X = your patient number. If there are more than one, then your on current event for our form can populate a list, also using the Dir Command. In the example, X is the patient number.

I run an an accounting department, and I scan and store attachments, and retrieve them in the same manner, it is quite effective for me. Even if your server name changes, it's just one line of code that needs updated, and you can store the server name in a table so a not programmer can update it.



There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

http://www.thelegacyreturns.com/