How to optimize linked tables

[mbath]

I have a front-end and a back-end which both reside on the server. The tables are simply linked. Is there a better (read, more efficient) way to do this?

Have always thought that performing the link thru code or ODBC would be more efficient but thought I would check in with experts.

thx

 

[Len Boorman]

I found this on the Access Help.

You can greatly enhance performance when opening the main database and opening tables and forms, by forcing the linked database to remain open. To do this, create an empty table in the linked database and link the table in the main database. Then use the OpenRecordset method to open the linked table. This prevents the Microsoft Jet database engine from repeatedly opening and closing the linked database and creating and deleting the associated .ldb file.

I am working on the design of a database that will have 5 Million records, Yes 5 Million is correct and it is normalised etc. I am therefore looking at performance of the FE communicating with the BE that contains the records.

So a couple of questions.

a) The extract above, will it enhance performance. ?

b) What does it all mean ?.

c) How do I set it up ?. Quite familiar with linking tables across databases but this seems to indicate linking a table within the FE to table in the BE or am I up the creek.

All explanations welcome.

Len B

 

[Mile-O]

Shouldn't you consider moving the front end onto the computer and let it link into the server?

 

[Len Boorman]

Pat
That was really helpful. I now understand exactly what they were saying.

Your additional comments are also appreciated.

There will be a limited number of users. This application is associated with the management of equipment serial numbers. The 5 M is a max figure but is possible and I need to be sure that it can be handled in a reasonable manner.

Again many thanks

Len B