File Structure and Performance

[Jim Franklin]

Hi,

I have an Access 2003 app split into a standard front-end/back-end design.

I have recently added some functionality which creates about 6 very large
data tables. Data for each table is appended by a separate function, run on
a monthly basis. Tables are never written to concurrently, although there
are query driven reports which pull in data from several tables at once.

At present I have these tables in a single separate back-end .mdb file.
However I am concerned that at some point in the future there may be an
issue with hitting the 2GB Access file size limit, and am considering
putting each table in its own file. This is a single-user system at present,
with all files on the user's C: drive (although this could change.)

Would there be any performance hit in splitting the BE file now, as opposed
to doing it later when it might become an issue?

Any help would be very much appreciated!

Cheers,
Jim

 

[Jeff Boyce]

Jim

If the performance is still acceptable, I'd look into the separate dbs, all
connected via a front-end.

On the other hand, I'd also be looking at a back-end that didn't have the
2GB limit if there was a chance of exceeding that -- have you considered
other, larger capacity, more robust back-ends?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jim Franklin]

Hi Jeff,

Other, larger capacity back-end's (e.g. SQL server) are a no-go
unfortunately because of licencing and support issues within the client's
organisation.

You said:

If the performance is still acceptable, I'd look into the separate dbs,
all connected via a front-end.

Does that mean that you think Access would run slower with separate dbs, all
connected by the front end?

Thanks once again for your help,

Jim

 

[Paul Shapiro]

SQL Server Express is free. I don't remember for sure, but I thought recent
versions removed the file size limit. It would probably be ideal for this
situation, where you don't need the features of the higher-level versions.

If you put each table into a separate mdb, you can't create relationships
between the tables. Unless these are unrelated tables, that raises issues of
manually maintaining data integrity. Access automatically creates indexes on
all Foreign Keys. Since you won't have any foreign keys, you might want to
consider if there are any additional indexes you should create on the
tables. If there are attributes that relate tables in separate db files, you
might want to build indexes on those logical foreign key attributes to
improve performance.

 

[Tony Toews [MVP]]

SQL Server Express is free. I don't remember for sure, but I thought recent
versions removed the file size limit. It would probably be ideal for this
situation, where you don't need the features of the higher-level versions.

Trouble is you'd have to involve the IT department in getting it
installed on the users PC. Which would likely greatly freak them out.

Tony

 

[Armen Stein]

SQL Server Express is free. I don't remember for sure, but I thought recent
versions removed the file size limit. It would probably be ideal for this
situation, where you don't need the features of the higher-level versions.

Yes, Express is free. But it still has a file size limit - 4GB.

Here's the breakdown of the editions:
http://www.microsoft.com/sqlserver/2008/en/us/editions.aspx

Armen Stein
Microsoft Access MVP
www.JStreetTech.com