JET transfers entire table?

[qwertyjjj]

I read somewhere that the main difference between JET AND SQL server is that a query rn through JET transfers the whole table across the network to the client machine and then processes it. Whereas, SQL server just transfers the particular record, say if you use a stored procedure.
Is this actually true?
What about if it's usng a SQL back end?
Any links on this subject?

 

[GaryPanic]

check on a post by Ken - yesterday where he has gone into a lot of discussion on this and their is a detailed response from another poster on this subject

 

[GaryPanic]

subject "Difference between SQL server and Access"
half way down this post you'll find a discussion on this

 

[RoyVidar]

Thread is http://www.access-programmers.co.uk/forums/showthread.php?t=136599, a couple more links there now, too.

Gist of it is - Jet will only transfer the whole table if you don't know what you're doing (indices).

While SQL server executes the query on the server, Jet will (from the client) examine the indices, then pull only the requested records.

Compare it with needing some specific information that you know can be found in a particular book. You will probably look in the index/TOC, and look at only pages that you think match, not start reading from page 1 until you find the information you want... That's what Jet is programmed to do, look in the index, fetch only the matches. After all, it is a database engine, not a spreadsheet or word processing program

 

[KenHigg]

I read somewhere that the main difference between JET AND SQL server is that a query rn through JET transfers the whole table across the network to the client machine and then processes it. Whereas, SQL server just transfers the particular record, say if you use a stored procedure.
Is this actually true?
What about if it's usng a SQL back end?
Any links on this subject?

The way I understand it is this: The main difference is that jet operates within a file server system and SQL Server is itself a database server. Database server models are inherently more efficient, etc at handling data, hence all other options being somewhat equal, it is the better choice, unless of course you don't know what you're doing

As far as the second part of your question, the way I understand it is that as long as you keep 'access' stuff out of your query's sql statement (keep it standard sql) sql server will execute it on the database server.

I am still at a loss as to how a file server app (jet) can open, edit, etc a file on another machine without actually loading the file - If it can do all of that it seems like we wouldn't need a database server like sql server. But I yield as the consensus around here is that jet can do all of that -

(I wish Pat would pop in with her insight, she always does a good job exploring and explaining these types of topics )


ken

 

[GaryPanic]

Ken ,
I am with you on this one
My access programmer was of the same opion
as I am as well - but hell it won't be the first time I am wrong

g

 

[KenHigg]

... but hell it won't be the first time I am wrong

g

Been there -

 

[GaryPanic]

it won't be the last either ...
(LOL)

 

[KenHigg]

I read somewhere that the main difference between JET AND SQL server is that a query rn through JET transfers the whole table across the network to the client machine and then processes it. Whereas, SQL server just transfers the particular record, say if you use a stored procedure.
Is this actually true?
What about if it's usng a SQL back end?
Any links on this subject?

Back to this original issue - Here is a pretty good download. I think some where around #9 was some good stuff on this:

Link



ken

 

[Pat Hartman]

I create all my new databases with Jet back ends but I always allow for the posibility that they might need to be upsized. That means that I use table and column names that will be acceptable to a RDBMS. I also NEVER bind forms to tables or to queries without selection criteria. And finally, I pay attention to how I use functions in my SQL.

Assuming I have done it right, Jet will "pass through" all my queries for processing on the server and only return requested rows.

One thing I have found that Jet doesn't handle well is bulk deletes. If you need to run an action query that deletes more than a few records, use a pass-through query. You may also find that you need to make views or stored procedures for complicated reports. But the bulk of your application will work fine with linked tables.