Client / Server Application Design using MDB backend.

[ions]

Dear MS Access Expert,

I have been reading John L. Viescas's book titled "Building Microsoft Access Applications".

I was under the impression that when using MDB for the backend (Split Database design) you can't really reduce network traffic because it is a file server. Sends back all records for your query requests.

In the book, John preaches that we should always limit the underlying recordsourse of forms and combo boxes using filters. However since MDB is a file server does this make any difference? Is John preaching this for the mere fact that the desktop database may be eventually be upsized to a SQL server client / server architecture?

Thank you for your clarification.

Peter.

 

[TexanInParis]

<< John preaches that we should always limit the underlying recordsourse of forms and combo boxes using filters. However since MDB is a file server does this make any difference? >>

Absolutely! Note also that I recommend filters only on indexed fields. Even though Access is a desktop database, when you ask for rows WHERE StateOrProvince = 'TX' and there's and index on that field, the engine fetches blocks from the index and then makes specific requests for blocks containing records that match the predicate. Only when your query would result in a table scan does Access fetch the entire table to the desktop over the network.

Is this as efficient as a true database server? Absolutely not. But if you look at the Customer Support sample database in my Building book that uses entirely unbound forms, you can see an example of an Access-only application that can support 50 or more concurrent users. The secret is the app fetches only the ONE row that's needed for the task by Primary Key. This sample database comes from a real application that still runs today supporting a call center - without SQL Server.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[boblarson]

One other thing to note is that Access does NOT necessarily send all records. It actually does limit things IF IT CAN. If you use good criteria, without grouping, without user defined functions, it will bring back only those records you asked for.

And that goes with connecting to a SQL Server backend with Access as well. You do have to be careful about what you do and when so as to minimize the impact.

 

[boblarson]

And Welcome John to AWF - a definite surprise to see you here.

Bob Larson
Access MVP

 

[TexanInParis]

Bob-

A friend in the U.S. who monitors this forum sent me the link to the post, so I couldn't resist a response.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[ions]

Hi John and Bob,

What a surprise! A response from John himself.

So far I have purchased Microsoft Office Access 2003 Inside Out and
Building Microsoft Access Applications, both very good books.

Thank you for both of your responses. Bob you have helped me several times in the past and I am very grateful for that.

I want to ensure that I understand what you both stated.

If the filter / where criteria / query criteria (do they cause JET to act the same?) are on an indexed field(s) the file server returns only records that match the criteria? I wasn't quite sure what you mean by blocks John.

>>Only when your query would result in a table scan does Access fetch the entire table to the desktop over the network.

By a table scan do you mean an aggregate function like Sum? (totals in the Query Grid)


>>Is this as efficient as a true database server? Absolutely not.

If I use a filter / query criteria on a indexed field and the JET engine only returns records that match the criteria wouldn't this scenario be as efficient as a client / server engine?

John and Bob I also posted a question about the validity of ADP in 2009. I would like to hear your opinion.

http://www.access-programmers.co.uk/forums/showthread.php?t=179210


Thank you,

Peter.

 

[TexanInParis]

<< If the filter / where criteria / query criteria (do they cause JET to act the same?) are on an indexed field(s) the file server returns only records that match the criteria? I wasn't quite sure what you mean by blocks John. >>

By "blocks" I mean 2k or 4K pages from the file system. Yes, when your predicate uses an indexed field, Access normally looks at that index first, then fetches only the pages that contain qualifying rows.

<< By a table scan do you mean an aggregate function like Sum? (totals in the Query Grid) >>

Not necessarily. Where LastName LIKE "*son" will cause a table scan because the charatcters at the beginning of the string are indeterminate - it can't use any index on LastName.

<< If I use a filter / query criteria on a indexed field and the JET engine only returns records that match the criteria wouldn't this scenario be as efficient as a client / server engine? >>

No. If you send a request like this to a true back-end database server, Access sends ONLY the SQL. The server parses it, does what it needs to do, and send back ONLY the data that satisfies the request. When you do it in Access, the parsing and decision about what to fetch gets done on the client. The point about using criteria on indexed fields is the JET (or ACE - 2007) engine will then fetch only the index and data blocks necessary to solve the request.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[Banana]

FWIW, many optimizations that are true for any client/server applications still apply even to a pure Access solution.

If you read the section about using Jet intelligently and cross reference with linked articles in the reference, I'm sure you'll find most, if not all, case apply just as well to the pure Access solution. Jet is pretty smart in what it does, but it's at the mercy of the developer. Tell it to wield sledgehammer, and it will.

 

[TexanInParis]

Exactly. That's the whole point of my Building book. If you get it to run well using an mdb solution, it'll upsize painlessly to a true backend server should the need ever arise. Too many developers try to take a slow 5-user mdb application and upsize it with horrendous results. If it runs slowly as an mdb-only C/S application, it'll still run slowly when you upsize.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[ions]

Thank you John,

I think I am beginning to understand. In a file server split MDB backend the Access client:

1) parses the SQL string for errors and optimizes it before sending to JET
2) receives blocks from JET that contain only the index and data necessary to resolve the SQL request. (Some but not all data in a given block may be irrelevant to the SQL request)
3) processes the received blocks from JET to produce the final recordset.

In an SQL Server client server architecture, the Access client sends a mere SQL string to SQL Server which does all the work and then returns the final recordset for the Access client.

Because SQL Server sends purely the required records vs. blocks (blocks contain both pertinent and unnecessary data) there is less network traffic.

Also because SQL Server resolves the SQL string entirely, there is less processing on the client CPU.

Please correct me if I am wrong in any of the above statements.

Thank you for your help.

Peter.

 

[TexanInParis]

"By George, he's got it!"

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

 

[boblarson]

Also because SQL Server resolves the SQL string entirely, there is less processing on the client CPU.

Just a warning - if you send something that SQL Server can't resolve completely, it will resolve what it can and Access will need to do the rest.

 

[ions]

Thank you to both of you for your advice.

I appreciate your time and feedback.

Sincerely,

Peter.