Recordset Open taking a long time

[mahmed1]

Hi Lovely People

i have a database which is around 100kb in size

Within that i have table called Tbl_All_Data

If i run a simple query like this in Access - its quick
SELECT date_, dept, sum(total)
FROM tbl_Incoming
WHERE date_ >= #01/04/2020 AND date_ <= #30/04/2020
GROUP BY date_, dept

takes around 4-5 seconds

now if i run this same query in Excel using open recordset it takes around 4-5 minutes to run

rs.Open sqlString is the part it takes ages

the SQL string is exactly the same as I posted but put into the variable sqlString

so nothing has changed and my excel file is small and all other SQL statements etc run supee quuick - its just this from excel that takes long

is there anything you can advise i add to speed it up?

 

[Legacy 456155]

It may help to replace:
WHERE date_ >= #01/04/2020 AND date_ <= #30/04/2020
with
WHERE date_ BETWEEN( #01/04/2020 AND #30/04/2020 )

Also, as the last argument in your rs.Open method (the options argument) use adCmdText.

I'm not real hopeful that this will solve your problem, but it may help.

 

[xenou]

It's not immediately clear why it would take longer but I would advise making sure you are using a ready-only type of recordset. Something like a fast-forward or forward-only recordset if that is an option would be best. Also would probably need to know a little more about the connection - is it entirely local? Excel and Access on the same machine?

 

[mahmed1]

Hi Xenou - here is the query in access

SELECT tblOut.Date_Ran, Nz(tblOut.Department,"Other") AS Area, Sum(tblOut.Total) AS Total_Outstanding
FROM tbl_AQs_Outstanding_Rolling AS tblOut
WHERE tblOut.Date_Ran between #01/04/2020# and #27/04/2020#
GROUP BY tblOut.Date_Ran, tblOut.Department;

this is the SQL string bit in Excel

Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset

        With cn
            .Provider = "Microsoft.ACE.OLEDB.12.0"    'For *.ACCDB Databases
            .ConnectionString = myDB3
            .Open
        End With

        'On Error GoTo CloseConnection

        With rs
            .activeconnection = cn
            .Source = "SELECT tblOut.Date_Ran," & _
                      "tblOut.Department," & _
                      "Sum(tblOut.Total) AS Total_Outstanding," & _
                      "Sum([TOTAL_AHT])/Sum([Total]) AS Weighted_AHT" & _
                      " FROM tbl_AQs_Outstanding_Rolling AS tblOut" & _
                      " WHERE tblOut.Date_Ran >= #" & Format(DateValue(StartDate), "yyyy\/mm\/dd") & "# AND tblOut.Date_Ran <= #" & Format(DateValue(EndDate), "yyyy\/mm\/dd") & "#" & _
                      " GROUP BY tblOut.Date_Ran, tblOut.Department"
            .CursorType = adOpenForwardOnly
            .Open

            'On Error GoTo CloseRecordset

            Call DeleteDataAQs
            AQsSh.Range("A2").CopyFromRecordset rs
        End With

 

[xenou]

Are you still having trouble? Where is the database? Is it entirely local? Excel and Access on the same machine?

Also you must test with a simpler query first - Select * from tblOut

 

[mahmed1]

Hi Xenou - I’m still struggling with this

access and excel or on same folder

problem is the open Recordset to excel each time it’s doing that bit takes for ever

what can I do or add to speed it up the best I can?

I can be copying over 100,000 + data so potentially that could be issue

 

[xenou]

WHERE tblOut.Date_Ran between #01/04/2020# and #27/04/2020#

Hi, since you have the above line in your query you can also try indexing the column Date_Ran in your tblOut table. That is a general solution for problems with slow running queries that can make a large difference.

 

[mahmed1]

WHERE tblOut.Date_Ran between #01/04/2020# and #27/04/2020#

Hi, since you have the above line in your query you can also try indexing the column Date_Ran in your tblOut table. That is a general solution for problems with slow running queries that can make a large difference.

Hiya

my Date_Ran column table has Index yes duplicates in there - is that what you are on about?

I have created this same index on the date column on all my tables

On some of my Databases I have tables that do a join to another table using the name area column which has no index (would I need to add an index on that column in that table too)?

 

[xenou]

Yes, that was the thinking.
You said you are copying over 100,000+ records. How many columns are in the table? How many records are in the table (totally)? When is the last time the access database was compacted?

 

[mahmed1]

Hiya - there are 120000 records and 36 columns - data is appended each day

i compacted database yesterday