Results 1 to 3 of 3
  1. #1
    philfer is offline Novice
    Windows XP Access 2007
    Join Date
    May 2010
    Posts
    4

    Select Query From SQL Server Slow In Access Fast In Excel, Access 2016

    hi,

    i am connecting to an instance of SQL server and selecting some records from a table. In Access the query takes 45 seconds to run. When I do the same thing in Excel it takes a second.

    Does anyone have any idea why this would happen. Everything is the same between the access and excel query so Im not sure why it would be so much slower?

    The query is a very simple one :-

    myDate = DateAdd("d", -90, Date)
    deletedDate = Year(myDate) & "-" & Right("000" & Month(myDate), 2) & "-" & Right("000" & Day(myDate), 2)


    sqlDeleted = "SELECT colA,colB FROM tblA WHERE CAST(colB AS Datetime) > '" & deletedDate & "' AND colC='TIME';"
    Set RSTDeleted = New ADODB.Recordset
    With RSTDeleted


    .ActiveConnection = DBConn
    .CursorLocation = adUseClient
    .Open sqlDeleted
    End With

    Many thanks
    Phil

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,278
    instead of using connection code, have you tried running a query against a linked table?

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,124
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 04-27-2020, 12:45 AM
  2. Form Slow but underlying Query Fast
    By Mclaren in forum Forms
    Replies: 2
    Last Post: 01-15-2020, 03:58 AM
  3. Replies: 9
    Last Post: 03-07-2019, 06:23 PM
  4. Replies: 4
    Last Post: 08-07-2018, 05:38 AM
  5. Replies: 12
    Last Post: 11-26-2013, 03:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums