X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Select Query From SQL Server Slow In Access Fast In Excel, Access 2016    
post May 19 2020, 09:02 AM

Posts: 1
Joined: 19-May 20


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

Go to the top of the page
post May 20 2020, 07:38 AM

Posts: 700
Joined: 26-May 15
From: The middle of Germany

Any differences in the connection string of DBConn?
Otherwise this is pretty inexplicable.

What's the original type of colB? Can it be changed to DateTime in the Table? The type conversion is what's making this query slow. - But it should be equally slow everywhere.

A professional Access developer tool: Find and Replace for Access and VBA
Go to the top of the page
post May 20 2020, 07:55 AM

UtterAccess Moderator
Posts: 13,021
Joined: 6-December 03
From: Telegraph Hill

FYI, Cross posted:
on AWF
and AccessForums



David Marten
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    8th July 2020 - 04:51 PM