Access SQL Server linked tables, limited # of records

[brettclare]

I have linked a large SQL Server table to Access, however 'only'
2,195,439 records are shown and are available to query. Can I increase
the size (cache??)/number of records showing in Access?

Thank you.

[Trevor Best]

br********@yahoo.com wrote:

I have linked a large SQL Server table to Access, however 'only'
2,195,439 records are shown and are available to query. Can I increase
the size (cache??)/number of records showing in Access?

Why on Earth would you do that to your users? Who'd want to browse that
many records? You want to go the other way and restrict the number of
records shown.

--
This sig left intentionally blank

[brettclare]

You want to go the other way and restrict the number of
records shown.
I know how to do that. I do not have permission to store a query/sp on
the SQL Server and I need to make a flexible app for an end user.
Who'd want to browse that many records?

Application needs to have ability to model all historical data

[Steve Jorgensen]

On 25 Mar 2005 10:22:46 -0800, br********@yahoo.com wrote:

You want to go the other way and restrict the number of
records shown.

I know how to do that. I do not have permission to store a query/sp on
the SQL Server and I need to make a flexible app for an end user.

You don't need server access, just create an Access query. Access will submit
the SQL to the server, and the server will retrieve the selected rows to
Access.

[billmiami2]

Stored procedures are the best way for you to interact with the SQL
Server. If you don't have permission to add your own stored procedures
and you can't get the required permissions, you should have the
database administrator add the stored procedures for you. If he/she
won't do that then you should create pass-through queries in your MS
Access user interface. You'll need to write your queries using T-SQL
as if you were writing them directly in SQL Server. You can modify the
SQL in your pass-through queries programatically by changing the
querydef properties (DAO). This approach isn't as good as calling a
stored procedure from your pass-through query, but at least you'll have
the SQL Server doing the work and you'll get better performance than
using ODBC linked tables as Steve suggested. Use those as a last
resort.

Bill Ehrreich
Hollywood, FL

Steve Jorgensen wrote:

On 25 Mar 2005 10:22:46 -0800, br********@yahoo.com wrote:

You want to go the other way and restrict the number of
records shown.
I know how to do that. I do not have permission to store a query/sp onthe SQL Server and I need to make a flexible app for an end user.

You don't need server access, just create an Access query. Access

will submit the SQL to the server, and the server will retrieve the selected rows to Access.

[Steve Jorgensen]

On 26 Mar 2005 18:50:43 -0800, bi********@netscape.net wrote:

Stored procedures are the best way for you to interact with the SQL
Server. If you don't have permission to add your own stored procedures
In some situations, stored procedures are the best way to interact with the
server. In others, they're not. The use of stored procedures to interact
with SQL Server from Access makes the interaction vastly more complex to set
up.
and you can't get the required permissions, you should have the
database administrator add the stored procedures for you. If he/she
won't do that then you should create pass-through queries in your MS
Access user interface. You'll need to write your queries using T-SQL
as if you were writing them directly in SQL Server. You can modify the
Why would you do a silly thing like that? For the most part, if you write an
Access query, Access builds the server-side query, and creates a prepared
statement for it. When SQL Server sees the same prepared statement signature
again with different parameters, it uses the cached query plan just as it
would for a stored procedure. If you use pass-through, not only can you not
pass dynamic parameters and not take advantage of a cached query plan, but
Access can't let you edit the resulting recordset through a form.
SQL in your pass-through queries programatically by changing the
querydef properties (DAO). This approach isn't as good as calling a
stored procedure from your pass-through query, but at least you'll have
the SQL Server doing the work and you'll get better performance than
using ODBC linked tables as Steve suggested. Use those as a last
resort.

I have the reverse opinion. For the most part, queries of linked tables work
great. When they don't use Access queries of views, and as a last resort, use
pass-through queries.

[billmiami2]

Steve,

If you use pass-through, not only can you not pass dynamic parameters and not take advantage of a cached query plan,
but
Access can't let you edit the resulting recordset through a form<<

I assumed here that this was a read-only, reporting style application
so there would be no editing of records. I assumed this because users
who don't have rights to create objects on a database tend not to have
write permissions either, but perhaps I assumed wrong.
Why would you do a silly thing like that?<<

Passing through a call to a stored procedure with parameters does
indeed make use of the stored procedure execution plan. For example,
if my pass through query SQL is

exec prMyProcedure 1

and I change it programatically to

exec prMyProcedure 2

SQL server will run the procedure with the parameter 2 rather than 1,
using the current execution plan. The work will be done on the server
and only the resultset (hopefully tiny) will be passed to the
application.

If instead my pass through query SQL was a SELECT with parameters in my
WHERE clause tailored programatically, then yes, I would have no saved
execution plan to run against so this would not be as efficient, but I
would still do my processing on the server. Of course, I would only
use this approach if I coudn't use stored procedures due to some kind
of restriction. If you use linked tables, you will need to be very
careful in designing your queries or you risk dragging tables across
the connection for Jet to process locally. Will a SELECT run from MS
Jet with linked tables will be more efficient that an equivalent SELECT
run directly on SQL Server via pass through? I doubt it but I suppose
it could be possible in certain cases. However, I can't imagine that
your SELECT with Jet/linked tables would ever be more efficient than
running a stored procedure via pass through.

I've had the unenviable task of cleaning up many reporting applications
designed in MS Access using linked tables to a SQL Server. I was able
to take reports that literally took an hour or hours to run and get
them to run in seconds by dropping all of the linked tables and
replacing all of the JET queries with pass throughs to stored
procedures. If you're not doing this then I would say that you're not
taking full advantage of SQL Server.

Bill

[Rick Brandt]

<bi********@netscape.net> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Steve,

If you use pass-through, not only can you not pass dynamic parameters and not take advantage of a cached query plan,
but
Access can't let you edit the resulting recordset through a form<<

I assumed here that this was a read-only, reporting style application
so there would be no editing of records. I assumed this because users
who don't have rights to create objects on a database tend not to have
write permissions either, but perhaps I assumed wrong.
Why would you do a silly thing like that?<<

Passing through a call to a stored procedure with parameters does
indeed make use of the stored procedure execution plan. For example,
if my pass through query SQL is

exec prMyProcedure 1

and I change it programatically to

exec prMyProcedure 2

SQL server will run the procedure with the parameter 2 rather than 1,
using the current execution plan. The work will be done on the server
and only the resultset (hopefully tiny) will be passed to the
application.

If instead my pass through query SQL was a SELECT with parameters in my
WHERE clause tailored programatically, then yes, I would have no saved
execution plan to run against so this would not be as efficient, but I
would still do my processing on the server. Of course, I would only
use this approach if I coudn't use stored procedures due to some kind
of restriction. If you use linked tables, you will need to be very
careful in designing your queries or you risk dragging tables across
the connection for Jet to process locally. Will a SELECT run from MS
Jet with linked tables will be more efficient that an equivalent SELECT
run directly on SQL Server via pass through? I doubt it but I suppose
it could be possible in certain cases. However, I can't imagine that
your SELECT with Jet/linked tables would ever be more efficient than
running a stored procedure via pass through.

I've had the unenviable task of cleaning up many reporting applications
designed in MS Access using linked tables to a SQL Server. I was able
to take reports that literally took an hour or hours to run and get
them to run in seconds by dropping all of the linked tables and
replacing all of the JET queries with pass throughs to stored
procedures. If you're not doing this then I would say that you're not
taking full advantage of SQL Server.

I don't disagree with what you're saying in theory and I'm sure it *is*
possible to create "queries against links" in Access that are slow because they
pull too much data for local processing. In my experience though I have found
this to be very seldom the case.

Target one is always getting a form/report/process working as desired and then I
move on to making it as fast as I can. Every time I have a report or form (or
other data process) that is slower than what I would like I experiment with
using Pass-Throughs and Stored Procedures and it is very rare that I can get a
significant improvement. I generally find (in order of highest occurrence)...

- No difference
- Slight improvement
- PT and/or SP is actually slower
- Big improvement

Concerning the third one I must admit that I don't understand how this can be
possible, but against our AS400 database I see this with some regularity. In
other cases it might just be a case of "perceived slowness" to the user.

When I use a PT or Query-Against-Links my form or report will open as soon as a
few pages of data is retrieved and Access will not pull in additional rows until
I do something that forces it. When I use an SP I get no response in the GUI
until the entire RecordSet has been fetched. This might mean that in a
situation where I was going to pull all of the rows anyway that the SP is on par
or faster as far as actual execution time is concerned, but in other cases the
user will see a slower response from the application.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[billmiami2]

Rick,

Target one is always getting a form/report/process working as desired and then I
move on to making it as fast as I can.<<

Absolutely. In cases where I've had to fix an Access application using
ODBC table links to SQL Server, I typically find that the forms that
are based on simple queries typically work fine, as long as proper
indexing is done on the SQL Server database. The problems are
generally in the more complicated queries used for reports. It's here
that I generally find that PT to stored procedures gives the most (and
often dramatic) benefit. But of course the degree of benefit depends
on the query, especially the degree to which MS Access functions are
used.

I don't want to get into a huge discussion about the benefits of stored
procedures vs. MS Jet queries of links. I just wanted to make it clear
to the original poster that MS Access provides other means for querying
SQL Server that should be considered.

Bill
Rick Brandt wrote:

<bi********@netscape.net> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Steve,

If you use pass-through, not only can you not pass dynamic parameters and not take advantage of a cached query

plan, but
Access can't let you edit the resulting recordset through a form<<

I assumed here that this was a read-only, reporting style application so there would be no editing of records. I assumed this because users who don't have rights to create objects on a database tend not to have write permissions either, but perhaps I assumed wrong.

Why would you do a silly thing like that?<<

Passing through a call to a stored procedure with parameters does
indeed make use of the stored procedure execution plan. For example, if my pass through query SQL is

exec prMyProcedure 1

and I change it programatically to

exec prMyProcedure 2

SQL server will run the procedure with the parameter 2 rather than 1, using the current execution plan. The work will be done on the server and only the resultset (hopefully tiny) will be passed to the
application.

If instead my pass through query SQL was a SELECT with parameters in my WHERE clause tailored programatically, then yes, I would have no saved execution plan to run against so this would not be as efficient, but I would still do my processing on the server. Of course, I would only use this approach if I coudn't use stored procedures due to some kind of restriction. If you use linked tables, you will need to be very
careful in designing your queries or you risk dragging tables across the connection for Jet to process locally. Will a SELECT run from MS Jet with linked tables will be more efficient that an equivalent SELECT run directly on SQL Server via pass through? I doubt it but I suppose it could be possible in certain cases. However, I can't imagine that your SELECT with Jet/linked tables would ever be more efficient than running a stored procedure via pass through.

I've had the unenviable task of cleaning up many reporting applications designed in MS Access using linked tables to a SQL Server. I was able to take reports that literally took an hour or hours to run and get
them to run in seconds by dropping all of the linked tables and
replacing all of the JET queries with pass throughs to stored
procedures. If you're not doing this then I would say that you're not taking full advantage of SQL Server.

I don't disagree with what you're saying in theory and I'm sure it

*is* possible to create "queries against links" in Access that are slow because they pull too much data for local processing. In my experience though I have found this to be very seldom the case.

Target one is always getting a form/report/process working as desired and then I move on to making it as fast as I can. Every time I have a report or form (or other data process) that is slower than what I would like I experiment with using Pass-Throughs and Stored Procedures and it is very rare that I can get a significant improvement. I generally find (in order of highest occurrence)...
- No difference
- Slight improvement
- PT and/or SP is actually slower
- Big improvement

Concerning the third one I must admit that I don't understand how this can be possible, but against our AS400 database I see this with some regularity. In other cases it might just be a case of "perceived slowness" to the user.
When I use a PT or Query-Against-Links my form or report will open as soon as a few pages of data is retrieved and Access will not pull in additional rows until I do something that forces it. When I use an SP I get no response in the GUI until the entire RecordSet has been fetched. This might mean that in a situation where I was going to pull all of the rows anyway that the SP is on par or faster as far as actual execution time is concerned, but in other cases the user will see a slower response from the application.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[brettclare]

The linked Access table is not showing all of the records that exist in
the SQL Server table. I need to know why and how to fix.

I appreciate all of the help and discussion but can anyone answer this
question and not redesign the application?

I have no performance problems, the application is used infrequently
and is not time sensitive. I need to keep this simple so that someone
else can modify.

Thanks all.

You don't need server access, just create an Access query. Access will submit the SQL to the server, and the server will retrieve the selected rows to Access.

[brettclare]

also, there is an Access query that is used to read the table. The
problem is that all the records are not available to read in the Access
linked table, yet they are in the SQL table.

[Rick Brandt]

br********@yahoo.com wrote:

The linked Access table is not showing all of the records that exist
in the SQL Server table. I need to know why and how to fix.

I appreciate all of the help and discussion but can anyone answer this
question and not redesign the application?

I have no performance problems, the application is used infrequently
and is not time sensitive. I need to keep this simple so that someone
else can modify.

Thanks all.

You don't need server access, just create an Access query. Access
will submit the SQL to the server, and the server will retrieve the
selected rows to Access.

I have never encountered a link that didn't show the same number of rows as
the source table, but then I have never worked against a SQL Server table
with that many records either. If it is some sort of limit or is dependent
on some property I am not aware of it.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[brettclare]

Thanks, that was my question!

[David W. Fenton]

br********@yahoo.com wrote in
news:11**********************@l41g2000cwc.googlegr oups.com:

also, there is an Access query that is used to read the table.
The problem is that all the records are not available to read in
the Access linked table, yet they are in the SQL table.

There is a MAX RECORDS property of saved queries and in form
recordsources. Perhaps there's a default setting for this that is
fewer than the number of records in your tables?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[brettclare]

Now we're getting somewhere. I'll check that out. Thanks.

[Bri]

You could try a Count query to verify that the records are available
through the link or not.

SELECT Count(*) as TotalRecords FROM myTable

If this record count matched the record count on SQL Server, then it is
not the link, but some limit in what you are viewing the data from (see
David's post).

If the Count does not match the SQL count (and I assume matches the
count of your other viewing method) then something could be whacky with
the link. Have you tried deleting and recreating the link to see if that
makes a difference?

HTH

--
Bri

br********@yahoo.com wrote:

The linked Access table is not showing all of the records that exist in
the SQL Server table. I need to know why and how to fix.

I appreciate all of the help and discussion but can anyone answer this
question and not redesign the application?

I have no performance problems, the application is used infrequently
and is not time sensitive. I need to keep this simple so that someone
else can modify.

Thanks all.