Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi there!

    The organisation I'm working for at present, to coin a phrase, hates Access. They are trying to stop me using it as a data retrieval and number crunching tool because they tell me it locks SQL Server tables and slows the systems down. They also tell me it is notoriously bad for doing so.

    I connect to the DB's via ODBC. Is there anything I should be setting at the Access front end or at the ODBC point in order to avoid these issues and thus keep the DBA's off my back?

    I don't know enough about the SQL Server DBA side in order to know whether they are telling the truth or are just being bloody minded.

    Any advice will be greatly appreciated.

    Cheers,

    Niven

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='Niven' post='795382' date='28-Sep-2009 15:14']... The organisation I'm working for at present, to coin a phrase, hates Access. They are trying to stop me using it as a data retrieval and number crunching tool because they tell me it locks SQL Server tables and slows the systems down. They also tell me it is notoriously bad for doing so.
    I connect to the DB's via ODBC. Is there anything I should be setting at the Access front end or at the ODBC point in order to avoid these issues and thus keep the DBA's off my back?
    I don't know enough about the SQL Server DBA side in order to know whether they are telling the truth or are just being bloody minded.
    ...[/quote]
    I think the DBAs are giving you a hard time. We use Access as the front-end to SQL Server all the time - but our apps are dedicated in that respect. If you are accessing another application's tables that has its own front-end, there might be some minor issues. In that case, you might ask them to make sure your SQL login has read-only capability. Or you could switch to using ADPs which connect to SQL Server tables in native mode, just like their admin tools.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I'm with Wendell on this, I think they are blaming you for something they can't explain. You said that "...they tell me it locks SQL Server tables and slows the systems down. They also tell me it is notoriously bad for doing so.". I never heard of that.

    Exactly what you are doing? Any data entry, or just running queries and reports? Do you use Access queries or pass-through queries?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm effectively just running Access queries (not pass through). I link the tables in from the DB's I'm interested in, by ODBC, and then create the queries. I'm not updating or doing any data entry, just purely retrieving.

    What I particularly like about Access in this respect is that I can quickly build the links across a multitude of relational tables, extract the data I'm interested in and then crunch it accordingly. I can then pass the SQL code onto our developers if this is going to be a regular job and they will create the stored procs from this and subsequently produce the reports through reporting services. Here's the political bit. I can write the stored proc's myself, but because my job title is systems analyst, I'm not supposed to. The developers are actually quite supportive. 1) I make their job easier 2) they feel the DBA's are making it harder to do anything and 3) Can't see thenselves how Access is locking tables at the SQLServer end. One actually said to me "Access is a recognised product, so no way should be causing a problem at the DB end"

    Should I be running Pass through queries rather than Access queries? I get a bit jittery every time I run something now as I feel I maybe being spied upon as it were.

    Many thanks for your interest in this

    Niven

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    With large numbers of records, pass-though queries are much more efficient.

    When Access processes a query, ALL records have to be transferred from the SQL Server database, over the network, to the PC running Access, regardless of the number of records eventually displayed by the query.

    With a pass-through query, SQL Server does all the hard work - selecting, sorting and aggregating the records, and only the records that will actually be displayed will be transferred to the PC running Access.

    To give an extreme example: if you have a SQL Server table with 1,000,000 records, the query

    SELECT Count(*) FROM TableName

    will cause 1,000,000 records to be transferred to the PC if the query is processed by Access, but only 1 record if it is a pass-through query.

    Also, SQL Server machines usually have lots of processing power and memory, so they can handle large amounts of data easily.

    But if you want to join tables from different sources, you can't use pass-through queries, so Access has to do the heavy lifting.

  6. #6
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795594' date='30-Sep-2009 00:24']With large numbers of records, pass-though queries are much more efficient.

    When Access processes a query, ALL records have to be transferred from the SQL Server database, over the network, to the PC running Access, regardless of the number of records eventually displayed by the query.

    With a pass-through query, SQL Server does all the hard work - selecting, sorting and aggregating the records, and only the records that will actually be displayed will be transferred to the PC running Access.

    To give an extreme example: if you have a SQL Server table with 1,000,000 records, the query

    SELECT Count(*) FROM TableName

    will cause 1,000,000 records to be transferred to the PC if the query is processed by Access, but only 1 record if it is a pass-through query.

    Also, SQL Server machines usually have lots of processing power and memory, so they can handle large amounts of data easily.

    But if you want to join tables from different sources, you can't use pass-through queries, so Access has to do the heavy lifting.[/quote]


    Hans,

    Generally I'm pulling data from the same DB. Do pass through queries work with linking tables within the same db?

    Cheers,

    Niven

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Niven' post='795596' date='30-Sep-2009 01:31']Do pass through queries work with linking tables within the same db?[/quote]
    If you join several tables in the same SQL Server database, you can use a pass-through query.

  8. #8
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='795598' date='30-Sep-2009 00:35']If you join several tables in the same SQL Server database, you can use a pass-through query.[/quote]


    Hans,

    The penny dropped just after I posted this question!

    It's late, I should goto bed now.

    Many thanks for help again,

    Niven

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='HansV' post='795594' date='29-Sep-2009 17:24']With large numbers of records, pass-though queries are much more efficient.

    When Access processes a query, ALL records have to be transferred from the SQL Server database, over the network, to the PC running Access, regardless of the number of records eventually displayed by the query.

    With a pass-through query, SQL Server does all the hard work - selecting, sorting and aggregating the records, and only the records that will actually be displayed will be transferred to the PC running Access.

    To give an extreme example: if you have a SQL Server table with 1,000,000 records, the query

    SELECT Count(*) FROM TableName

    will cause 1,000,000 records to be transferred to the PC if the query is processed by Access, but only 1 record if it is a pass-through query.

    Also, SQL Server machines usually have lots of processing power and memory, so they can handle large amounts of data easily.

    But if you want to join tables from different sources, you can't use pass-through queries, so Access has to do the heavy lifting.[/quote]
    Actually, in this example, as long as only a single table is involved, the ODBC driver is smart enough to limit the number of records returned initially, unless you actually scroll through the entire table. Also, if you have a criteria involved, it will actually pass that criteria to SQL Server as long as there are a limited number of joins. By virtue of this, we use a form with some 400K main records, and it has two subforms which display a related subset of records from tables containing 4M and 1M records respectively, and the response time is subsecond at the workstation.

    All that aside, if you get a complex query involving three or more joins, Access may well bring over the contents of all of the tables, which can make things much slower than if you were dealing with local tables. We typically try the query first and see what sort of response we get. If it is fine, we don't worry, but if it is really sluggish, then we either create a pass-through query, or look at creating either a view which does the joins for us, or a stored procedure which returns the subset of data we want. Hope this clarifies things some.
    Wendell

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thanks, Wendell!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •