Structure of tables/database to reduce overall size of file (1 Viewer)

[jobrien4]

I designed a database that stores information about a part, then on a seperate form, allows the user to create a quote of that part.

We now see the need to attach files at the part information level. There will be three separate fields to attach the files.

These files will almost always be PDF's ranging in size of 50kb to 200kb. I'm estimating within a 3-5 year time frame, the file size will breach the 2GB limit.

Is there a way to structure the table/database to reduce the size? I'm thinking about each attachment field have its own table, then that table gets split into its own database. Would this work?

 

[spikepl]

Consider not stuffing the PDFs into the db but leave them on a shared drive, and store the link to the file.

 

[jobrien4]

Our IT staff would prefer to go in a different direction. Would what I propose work? If the size limit ever got breached, we would convert the database to SQL, but would like to delay that as long as possible.

 

[DevastatioN]

When you embed a file in the database, the size of that file balloons generally. So a 50k pdf cuold turn into 500kb attachment (have to do testing on the size difference of pdfs inside the DB).

The best way is to always store the link to the file on a shared drive as suggested, and make the database appear like it's "attached" for the user.

There is nothing as far as I know you can do with the table structure or design to get around this limitation.

I'm not sure why your IT staff want to take it in this direction, this will use up the server space even faster than just linking to the document.

 

[spikepl]

Hmm seem to recall seeing a post recently about Blobs, which would supposedly avoid the balooning. But then you'd miss the simplicity.

As far as I know you could split it as you suggest, with own db for each table.

The KISS-principle says just leave it on a shared drive. Cost of disk space is low.

 

[jobrien4]

There would be problems though if the location of the file was ever changed correct?

 

[spikepl]

Correct. But if the lot is structured in folders, only the location of the main folder would change, presumably, and that could be handled in code in some setup or error handling.

 

[DevastatioN]

What you could do is store only the filename in the field for the attachment table.

Then in a table (I call mine tblServer) which includes server specified fields. You could have a field for AttachDir. All frontend's will attach to the tblServer for global variables/fields you wish to use.

So AttachDir would say "J:\MyDB\MyPath\Attachments"

The attachment table will have "MyFile.pdf"

And the button code to open the file would use DLookup("AttachDir","tblServer") & FilePath to create the correct link.

This way if the directory changes, you can just change the directory setting in AttachDir.

Edit: Update query could be used as well to change the directory if you did want to keep the full path in the table. I still prefer the Server Variables table though.

 

[Anakardian]

I am a bit curious on this with the shared drive for attachments.
How does it handle attachments having the same name?
I have been using a program that stored attachments in a shared drive but it could not handle attachments having the same name and would just overwrite the previous version. This of course caused all sorts of problems.

 

[DevastatioN]

There are a few ways to do this, depending which you prefer.

I tend to alert my users "hey, this file already exists, are you sure you wish to overwrite it?"

Dim fs As Object

Set fs = CreateObject("Scripting.FileSystemObject")

If fs.FileExists(newPath) Then
      'Insert code to ask question etc.
      If yes, delete file, move file
      Else exit sub
End If

Set fs = Nothing

If you wish to save the file they're overwritting, I use the following code.

If fs.FileExists(newPath) Then
            fs.MoveFile newPath, "Y:\Blah" & "\" & Filename & "_" & Month(Date) & "_" & Day(Date) & ".pdf"     'Copy file
        End If

newPath is the path of the file being overwritten, so we move it to folder Blah with the month and day that it was overwritten. This way we can go back to it incase it was a mistake. Then we run the normal code to overwrite it.