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. 15 7253 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 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
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.
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.
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.
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
<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
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
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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....
|
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...
|
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
|
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...
|
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...
| |
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...
|
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
|
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
|
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...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
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...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |