Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You don't mention the actual time taken by the queries, but here are a few thoughts:</p> <ol> <li><p>Make sure you have indexes for all the fields in the database that you are grouping or filtering.</p></li> <li><p>If you are the only user of that database, open it in exclusive mode:<br> For <code>ADO</code>, use the connection string:</p> <pre><code>"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" &amp; strFile &amp; ";Mode=12;" </code></pre></li> <li><p>Use <code>DAO</code> instead of <code>ADO</code>.<br> <code>DAO</code> is native to Access and generally faster.</p> <p>First thing is to add a reference to the Access Engine to Excel:</p> <ul> <li><p>From the IDE, under Tools > References, go down the list and check:<br> <code>Microsoft Office 12.0 Access database engine Object Library</code>.</p></li> <li><p>If you have Access 2010, the reference will be:<br> <code>Microsoft Office 14.0 Access database engine Object Library</code>.</p></li> <li><p>With older versions of Access (2003 and previous), using the Jet engine instead (MDB files only), it would be:<br> <code>Microsoft DAO 3.6 Object Library</code>.</p></li> </ul> <p>Then use the VBA code below to load the data into your worksheet:</p></li> </ol> <pre><code> Public Sub LoadFromDb() Dim db As DAO.Database Dim rs As DAO.Recordset ' Open the database in Exclusive mode ' Set db = DBEngine.OpenDatabase("C:\Users\MyName\Desktop\DataBase.accdb", True) ' Open the recordset as a snapshot, it's faster than the default dbDynaset ' Set rs = db.OpenRecordset("SELECT CUSTOMER, DATE, REVENUE " &amp; _ "FROM [SALES DB] WHERE REGION='REGION1'", dbOpenSnapshot) ' Copy the recordset to the sheet ' Worksheets(1).Cells(2, 1).CopyFromRecordset rs rs.Close db.Close Set rs = Nothing Set db = Nothing End Sub </code></pre> <ol> <li><p>If you are mostly importing your data from Access to display them in a Pivot, you may be better served by the pivot table in Access itself.</p></li> <li><p>On that subject, did you know that you can split your database to share the data backend and use the free <a href="http://www.microsoft.com/download/en/details.aspx?id=10910" rel="nofollow">Access Runtime</a> to allow all your users to view your reports and play with the data on their machine?</p></li> <li><p>Moving to SQL Server or another database may/may not solve your issues at all:</p> <ul> <li><p>if SQL Server is on your Machine, it will take more or less as much resources to calculate your query as if the MS Access database was on your machine.</p></li> <li><p>if SQL Server is on a remote machine, most of the time will be spent on the network data transfer.</p></li> <li><p>your bottleneck is probably not the database, it's the time to import that much data into the spreadsheet itself. You can try and execute the query from Access itself and see how long it takes.</p></li> </ul></li> <li><p>If you have that much data to sift through, Excel is probably not the best tool for the job, and you may be better served by a dedicated reporting or Business Intelligence application.<br> There are plenty of OpenSource and commercial platform, for instance:</p> <ul> <li><a href="http://www.pentaho.com/" rel="nofollow">http://www.pentaho.com/</a> (OpenSource + Commercial)</li> <li><a href="http://www.jaspersoft.com/" rel="nofollow">http://www.jaspersoft.com/</a> (OpenSource + Commercial)</li> <li><a href="http://www.gcpowertools.com/categories/Analysis" rel="nofollow">http://www.gcpowertools.com/categories/Analysis</a> (Commercial, for developers)</li> </ul></li> </ol>
    singulars
    1. This table or related slice is empty.
    1. POGrouping data slows query despite 25% decrease in the number of results
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USRenaud Bompuis
    1. USRenaud Bompuis
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POGrouping data slows query despite 25% decrease in the number of results
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThank you for your kind reply. That is really helpful stuff. You're right when you say that the problem is not the query itself but actually the inport in excel. It all runs much faster in Access. I'll try using DAO like you suggested and if that fails too maybe I'll migrate to Access. On that note, `Set en = CreateObject("DAO.DBEngine")` generates an error _"activex component can't create object"_. Any clue on how to fix that? Also, I see you removed the `rs.Open strSQL, cn, 0, 1 Worksheets(1).Cells(2, 1).CopyFromRecordset rs` is this right? Thanks again for your help!
      singulars
      1. PO
      1. USBruder
    2. COActually, I solved the _"activex component can't create object"_ with `Set en = CreateObject("DAO.DBEngine.36")`. Now I get a pop up asking to input or browse for a DSN. What is that? And how do I do it? I'm sorry, I'm really bad at this stuff!!
      singulars
      1. PO
      1. USBruder
    3. CO@Bruder: I updated the code a bit. Regarding the DAO exception, please check this KB http://support.microsoft.com/kb/157471
      singulars
      1. PO
      1. USRenaud Bompuis
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload