April 30, 2012 at 11:13 am
hey there, this is my first post to the forum. thanks in advance!
FYI: i totally new to SQL server but have solid skill in ms access. the goal was to link an access database to a sql server table via ODBC. therefore i created a driver SQL Server Native Client 10.0 and pointed it to appropriate database. so in access if i go to external data i can find the desired table and created a link to the table.
my issue is that if i query on the table or even try to just view the table it is extremely slow. i actually was getting a timeout error so i change the timeout prop to be blank which then resulted in a completed query.
are there setting that need to be validated? i'm pretty lost on what to do.
ms access 2007 linking to SQL Server 2008 R2. also i will stated if i attempt to query on Top1000 in the same table in the management studio it is rather slow as well.
Terry
April 30, 2012 at 4:37 pm
Have you tried taking the Access query and refactoring it appropriately as a SQL Server stored procedure and calling that from access instead?
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 30, 2012 at 5:32 pm
Two things.
1) Create your view as a passthrough query, preferably with the necessary restrictors. That'll help not have to pull the entire table back to access and into memory before Access can start working with the data.
That'll get you started on looking into even more optimizations as you go through the process and hit the different blogs/comments/discussions.
2) You never want to open the table up directly in Access. Access opens a cursor on any dataset it works with (it's like opening up the table in SSMS for SQL Server, bad juju). This is slow, unwieldy, and opens locks against the SQL Server that you don't want in place.
However, for best performance, what you really want is a passthrough query running a proc on the server-side.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 30, 2012 at 5:42 pm
My experience with Access is that anything done with GUI tools is unacceptably slow. Therfore, I always use unbound forms with VBA code that opens recordsets directly and places the fields I want into display text boxes. This is blazing fast with the access data engine as well as remote SQL data sources. For reporting I just call stored procs on the server etc. Anything you ask Access to figure out for you will result in cursors pulling all the data into local memory before access can do anything with it.
June 16, 2015 at 6:42 am
I'm a new member of this forum...Sorry for what is likely a very basic question, but how do you create a 'pass through query' using MS Access with SQL via an ODBC connection?
June 19, 2015 at 7:16 pm
I'm assuming you're using Access 2013 (it's what I have installed, sorry!) so if this doesn't work, you might need to specify the version of Access you're using.
If you're querying tables/objects that are in the backend database (SQL Server), and you need to indicate that the query is a pass-through (so the Access engine makes no attempt to parse the query), you open the query in Design View, and then in the Query Type section of the Design tab, click Pass-Through.
June 24, 2015 at 5:27 am
Thank you for your guidance. It made me realize the answer was right in front of me -- sorry for asking the obvious. Thanks again
April 7, 2017 at 12:55 am
I believe this Stack Overflow Answer and Question will be very usefull.
http://stackoverflow.com/a/39810690/4050261
http://stackoverflow.com/questions/39793476/access-local-front-end-connected-to-azure-sql-server-back-end-very-slow
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply