Post reply

troubleshooting query in access

  • May 9, 2006 at 10:27 am

    #168038

    hi, i am kind of embarrassed for this question but I need to fix it asap and I already tried everything.

     

    I have a database with the front end access and backend sql server, now I have a simple form which uses a query from access using my tables from access to retrieve and enter data but when i try to run the form or the query , it freezes as soon as the tab starts. when you run the tables used in the same query individually everything is fine. Also, I went to sql server and all my tables open and run fine. and all the other forms and queries run fine in my access database also, what else can I check for?

     

    Jessica 

  • May 9, 2006 at 12:00 pm

    #636836

    The "freezing" is probably due to the way Access processes queries   The Jet DB engine will grab everything from SQL Server and process the query locally.  This can cause a lot of freeze-ups while retrieving data.  If you want decent performance look at using a PASS-THROUGH query in Access.  This just sends the query to SQL Server, lets it do the processing and only pulls the results "across the wire".

  • May 9, 2006 at 3:07 pm

    #636895

    thanks, do you have the steps of how to do this with an existing query or to do it from scratch?

  • May 9, 2006 at 5:13 pm

    #636909

    I haven't used Access in a while, so please forgive if the directions aren't 100%.  This is for Access 2003; Access 2000 is similar, but the menu options might be different:

    1.  With an existing query, just go to Design Mode and select View > SQL View.

    2.  Choose Query > SQL Specific > Pass-through.

    3.  You might have to modify the column names and table names.  I think Access automatically appends the table owner (usually "dbo_") to the front of your column and table names.

    Pass-through mode sends the queries directly to SQL Server and lets the server do the processing for you instead of pulling everything across the wire for Jet to fudge around with.  Since the queries go directly to SQL Server for processing, SQL functions will work in the queries; Access-specific functions and syntax will not.  Just keep that in mind when changing functions.

    To keep the ODBC Connection box from popping up every single time you run the query, look at it in SQL View and select View > Properties.  Change your ODBC Connection String in the pop-up box to "ODBC;DSN=datasource_name;Trusted_Connection=Yes" for Trusted Authentication, or replace Trusted_Connection with "UID=user_id;PWD=password;" for SQL authentication (not recommended!)

  • May 10, 2006 at 2:54 pm

    #637115

    got it !!! thank you.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply