473,735 Members | 2,067 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access SQL Server linked tables, limited # of records

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.

Nov 13 '05 #1
15 7257
br********@yaho o.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
Nov 13 '05 #2
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

Nov 13 '05 #3
On 25 Mar 2005 10:22:46 -0800, br********@yaho o.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.
Nov 13 '05 #4
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********@yaho o.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.


Nov 13 '05 #5
On 26 Mar 2005 18:50:43 -0800, bi********@nets cape.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.
Nov 13 '05 #6
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

Nov 13 '05 #7
<bi********@net scape.net> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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

Nov 13 '05 #8
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********@net scape.net> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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


Nov 13 '05 #9

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.


Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

12
3382
by: Mike MacSween | last post by:
Sorry if this is a bit off topic, but I can't seem to find a 'remote control' newsgroup on my news server. And anyway I know Tony has some experience of this. The app is split FE/BE. I'd like remote access to at least the server, hopefully the whole network. And to be able to upload/download, to install new versions of the FE to the server (which then get sent up to the clients at runtime). What experience does anybody have of this....
5
4004
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL 2000. Of course there were no modifications made to the queries and they noticed significant performance issues. They recently upgraded the application to Access XP expecting the newer version to provide performance benefits and now queries take...
24
2790
by: Bob Alston | last post by:
Most of my Access database implementations have been fairly small in terms of data volume and number of concurrent users. So far I haven't had performance issues to worry about. <knock on wood> But I am curious about what techniques those of you who have done higher volume access implementations use to ensure high performance of the database in a multi-user 100mbps LAN implementation??? Thanks
2
4238
by: egoldthwait | last post by:
I need to convert a 17mb access 2000 db to Oracle and house it in a Citrix farm. The issue: we have never converted an Access Db to Oracle but can probably use Oracle's Workbench to assist with this. Also - the citrix folks do not want us to keep the FE in Access as the queries and other activities consume a lot of power. The users will be in 3 different offices across the globe all accessing the 1 Oracle DB in Citrix. Does anyone have...
1
2742
by: effiw | last post by:
I'm working with an application that has an MS Access front end linked to the SQL Server DB backend. During a period of one week, 32 records of a spefic event (and all related records to that event) disappeared from both DBs. The application uses an Access report db for users to query for specialized data. Security is to the Access DB is limited to 3 db admins (2 IT, I departmental), and 1dept manager. The app's audit log is supposed to track...
6
6263
by: onnodb | last post by:
Hi all, While working on an Access UI to a MySQL database (which should be a reasonable, low-cost, flexible interface to the DB, better than web-based, much less costly than a full-fledged .NET app or so.... is it?), I tried measuring the bandwith consumed by the Access/MyODBC/MySQL link, which came out to be, er, quite high. I fancied it would be interesting to look at the queries Access throws at MySQL through the ODBC link, so I...
5
1866
by: Kip | last post by:
I have an office with approx 8 people. I have used Access with a Form on my personal PC for client records. I was wondering if I could put the Access table on a server and put shortcuts on each work station to allow users to view, update records? Thanks in advance
5
7232
by: jonceramic | last post by:
Hi All, I started developing in Access, and people took notice and so we're starting to migrate into our corporate's bigger Oracle system. I'll still be using my developed Access front ends, but will be migrating my back ends to Oracle ODBC. 1. Does anyone have recommendations for books or web resources for general rules/guidelines/help on doing this? I haven't found a good
6
4404
by: jsacrey | last post by:
Hey everybody, got a secnario for ya that I need a bit of help with. Access 97 using linked tables from an SQL Server 2000 machine. I've created a simple query using two tables joined by one field between them. The join field in both tables are indexed and I'm selecting 1 field from each table to lookup. The Access query is taking more than 60 second to retrieve 1 record and if I execute the same query within the Query Analyzer, it...
0
8964
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9466
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9327
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8202
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6049
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4564
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3277
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.