Unanswered: Limiting returned records. Preparing for using Data Access Pages/performance concern.
Hi, I am preparing a 2-part db app that will have around 2-5 access users (mainly 2) adding and updating records simultaneously, and around 20 others accessing the db from the browser.
The two people using access itself will add and update one table that will soon contain tens of thousands of records and another table which will contain hundreds of thousands or more records. The latter being a simple one with only name, company and phone number fields.
The 20 other people will query mainly 3 tables. They will be ADDING but not updating records using Data Access pages. The tables in question will be in the number of hundreds and then perhaps thousands, for each of the 3 tables.
I am not experienced with Data Access Pages. And the latter part of the db (for the 20 assessing by browser) will be replaced by a huge non-access system within 6 months, so we are not looking to do very much development on this part.
We will not necessarily run a dedicated server, perhaps only directly from one of the PCs being used for other purposes as well. ¨
- and if we choose a dedicated "server", maybe only with an Access client "serving" the data. The real server alternative would be storing the data in MySQL, but we want to check if that can be avoided taken the fairly short perspective into account.
A security concern makes us wonder if we should have all DAPs pointing to one MDB which then links to the MDB that really stores the data - on another computer via the internal WinXP workgroup network.
"2-part db", meaning that it will either be two sets of tables getting the hits in one mdb, or we might choose to separate into two mdb files, perhaps even on two different workstations.
So, in terms of performance considerations, I am looking for some general comments and advices on this, plus specifically if (and which) I should use any tricks in the queries to limit the resulting records on the Access client side who is doing the queries BEFORE the data is returned to the browser clients. (Maybe I am wrong here, this is not a "true" client/server situation perhaps, are the queries (through ODBC) being carried out on the PC which stores the DB or are all records sent over the network before the query runs? Any way to avoid this without using a real server?)
Re: Limiting returned records. Preparing for using Data Access Pages/performance conc
Some additional info:
The biggest table with hundreds of thousands of records will only be queried sporadically by the two main access users, and only printed in very small portions from the main computer a couple of times per month or so. They will not be accessed at all via the browser, and rarely by two people at the same time.
So the question of performance and concurrent browser users is perhaps mostly concerned with one table with - some tens of thousands of records.
Then there is a ODBC buffer setting for the computer with the linked tables - I think the default is 2048? - is that important/critical (to adjust) in this scenario?