SQL Server Back End: Work Tables DAO or SQL Server?

[(PeteCresswell)]

Let's say we have an MS Access form with a half-dozen subforms.

Parent record gets displayed on part of the form and various
lists of child records are on the six subforms.

Now, for the sake of argument; let's say there's a good reason
for the parent and subform's not being hooked directly into the
back-end tables and therefore, they will be based on work tables.

The question: is it more efficient to make whose work tables
#Temp tables in in an SQL Server DB or make them DAO tables in a
JET work DB in, say, C:\Temp?

My current rationalization favors JET tables in C:/Temp.

Reason: the information for all seven tables (parent plus six
child tables) has to make the trip across the LAN from SQL Server
to the app one way or another, so the number of bits seems tb a
wash.

But by using DAO/JET tables locally, I can open up a single ADO
connection, get all the information for all the tables in a
single stream and the break it up into the respective JET tables
in C:\Temp. One-stop shopping, so-to-speak.

OTOH, using #Temp tables in SQL Server, I'm not only loading up
the server, but I'm also conducting seven separate connections.

Am I on the right track with this? Or is there some flaw in my
reasoning?

 

[Tom Wickerath]

Hi Pete,

Get a copy of the book titled "Microsoft Access Developer's Guide to SQL
Server", written by Mary Chipman and Andy Baron (SAMS Publishing):

http://www.amazon.com/dp/0672319446

It looks like you can get a used copy for under US $20.

The authors describe the "one stop shopping" technique (storing data in
local tables) in Chapter 11: "Scaling Up with Unbound Access Applications".


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[(PeteCresswell)]

http://www.amazon.com/dp/0672319446

It looks like you can get a used copy for under US $20.

Thanks.

Just got the order confirmation from Amazon.

Says it's in the mail: $22.94 delivered.