correct way to handle excel files stored in mdb

  • Thread starter Thread starter Pierre
  • Start date Start date
P

Pierre

Hi all,

I have an mdb which contains a field where we put an excel files (each 40k)

This file is huge (100 meg)

If this file is link to an access database does it put load on the network
even
if it is rarely used (once in a while to sored an excel file)?

Is the fact that the file is linked and big is an issue for the performance
of the network? Like we should not
linked it or just linked it when it is time to used it and then erase the
link?

Regards,
pierre
 
Are the xl files in a seperate table or are there other fields which are used
in the same table?

From a design point I would consider the following

Table1
Fields: ID, Field1,Field2,....

Table2
Fields: ID, ExcelFile

THis means the excel files are seperately stored from the other data
When there is a need to query the excel field then you just join the 2 tables.

SELECT FIeld1,ExcelFile FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID

- Raoul
 
Is there a reason to put the Excel files in the mdb? I can't think of a
reason this is necessary, but you never know. A better approach would be to
link to the Excel files as if they were tables. This will keep the size of
your mdb down.
 
Indeed,

I try to avoid to store files into MDBs for multiple reasons:
1 Size
2 Speed
3 Backup
4 export OLE is not easy

So best practice would be to organize a folder on the server where people
save there XLS files and only the path is stored in the table.

- Raoul
 
Back
Top