2

I'm trying to create a dynamic report in Excel. I have lots of sales data and I want the user to be able to slice and dice it according to his needs.

Normally I would use two sheets: one hidden, containing the raw data, and one visible, containing all the buttons and form controls so that the user can dinamically select and visualize only a small subset of the original data at the time.

The problem is that this time I need to handle 6.000.000+ rows of data (and counting). Storing it all in an excel sheet is not an option. Besides, the data is already in the form of an Access table.

I tried accessing it dinamically via a query that "filters out" the un-needed information based on what the user selects in the form control on the Excel sheet. For some reason, this is very slow. It takes 4-5 minutes to pull out as little as 10 rows of data.

There has to be a quicker way to do this! I need this whole process to feel "instantaneous".

Any thoughts?

Edit: Ok, so the problem seems to be related to the fact that my access table is actually a linked table pointing to a *.txt file. This slows the import down a lot.

I tried both of the suggested solutions.

iDevlop's idea works quite fast (200k rows imported in 10-15 secs), but it has the downside of me having to update the table every time. I'll post another question, like he suggested, to see how and if the process can be automated.

Remou's script works perfectly too now (I had a hard time getting it right but he was really open and helpful so know I got it) and, although slower, it has the advantage of not requiring any database mantainance.

There's a few more things I need to get straight before choosing which approach to use. For now, all I want to say is thank you guys for you help! I could have never made it without you!!!

Community
  • 1
  • 1
Bruder
  • 563
  • 3
  • 8
  • 18
  • Have you tried ADO? Another possibility is that you do not have enough indexes to get the data quickly, which is why your query is slow. – Fionnuala Mar 09 '12 at 14:37
  • 1
    You should post your SQL, too, because someone may see a way to improve it. – Fionnuala Mar 09 '12 at 14:45
  • @Remou - I tried playing around with indexes, but I had some problems with my table (see comment to iDevlop's answer). How can I use ADO to solve my problem? Do you have any specific link to that? I tried searching trough past posts but I couldn't find anything that deals specifically with the extraction of a small subset of data from a large database. – Bruder Mar 09 '12 at 16:45
  • Bruder, I have posted an example that attaches to a text file. – Fionnuala Mar 09 '12 at 19:14
  • I have inserted your additional notes and flagged the post below. It looks like you have a tab separated file, for this, you will need a schema.ini containing `Format = TabDelimited` – Fionnuala Mar 12 '12 at 21:08

2 Answers2

2

As says Remou, check xour indexes, but also make sure your criteria are entered in a way that allows indexes to be used !
e.g : WHERE Format(myDate,"yyyy-mm") = "2011-09" does not allow the date index optimisation,
while WHERE myDate BETWEEN #09/01/2011# AND #09/30/2011# does allow index optimisation.

Edit:
If you have some kind of unique identifier in your text file and you translate that into a PK in your table design, you can then import the whole thing on a regular basis, and the duplicates will be discarded by the PK.
The import could be automated, even with a .vbs, you don't need Access to do it. Make that another question if you're stuck. You could also ask the IT guys to delete the older records every month or so.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • It appears as though no column is indexed in my table. I tried to change that but it says I can't because it's a linked table and so I cannot change its structure. It also says I should make changes directly on the original database, but that's a txt file so I really don't know how to do it. I wish I could be more specific but my Access is in italian so I'm just roughly translating the error message I got. When I try to define some indexes it doesn't let me save the changes, and the next time I open the table all the indexes are gone. – Bruder Mar 09 '12 at 16:47
  • 2
    OMG, if your table is a linked table pointing to a 6M lines text file, no wonder why your queries take time ! Could'nt you import hose lines in a table and then delete the imported lines from the text file ? Or maybe move the lines that are more than x months old to another txt file ? – iDevlop Mar 09 '12 at 16:49
  • and the 6M lines txt is not even on my computer, it's on some server somewhere. I guess that explains much of my problem. So, I'm now trying to import that txt into a new DB, but what am I going to do tomorrow as the file is updated with the sales orders we received today? The IT guy is going to update the txt (that's why we use a linked table), do I have to import the whole thing again? – Bruder Mar 09 '12 at 17:35
2

Don't bother going through Access if you have a text file. This may hold you until you can get a better system in place.

Dim cn As Object
Dim rs As Object
Dim strFile As String
Dim strCon As String
Dim strSQL As String
Dim s As String
Dim i As Integer, j As Integer

strFile = "z:\docs\"

''Note that if HDR=No, F1,F2 etc are used for column names,
''if HDR=Yes, the names in the first row of the range
''can be used.
''
''Connection strings : http://www.connectionstrings.com/excel

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
    & ";Extended Properties=""Text;HDR=Yes;IMEX=1"";"

''Late binding, so no reference is needed

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT * " _
       & "FROM [test.txt] a " _
       & "WHERE a.FirstName ='Bernard'"

rs.Open strSQL, cn, 3, 3


''Pick a suitable empty worksheet for the results

Worksheets("Sheet3").Cells(2, 1).CopyFromRecordset rs

''Tidy up
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

If your file is tab delimited, you can use a schema.ini (http://msdn.microsoft.com/en-us/library/ms709353(VS.85).aspx). It must be in the same directory as you text file and need only contain two lines:

[Ordini BO new.txt]
Format=TabDelimited

Your connection string should read:

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFile _
& ";Extended Properties=""Text;HDR=No;IMEX=1;FMT=Delimited"";"
Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • thank you for your reply. I gave your idea a try but I get the following error message: "excel run time error No value given for one or more required parameters". What I did was edit the `strSQL` part so that it reads as `strSQL = "SELECT * FROM [Ordini BO new.txt] a WHERE a.FirstName ='GW93349'"`. Where did I go wrong? Suppose I want to extract all data that have GW93349 written in "column C" of my txt file named _Ordini BO new.txt_. How do I do it? (sorry if this is a lame question, I'm really new to this stuff). – Bruder Mar 10 '12 at 12:08
  • 1
    As I mention in comments to the code, if HDR=Yes, then use your own column names. If HDR=No, then use F1, F2 ... Fn, so if column C is called `C` then `WHERE a.[C]='GW93349'`. If column C is the third column, then you will need to change the connection string to set HDR=No and say `WHERE a.F3='GW93349'`. You can always say `WHERE 1=2` and just check the field names returned, `For i=0 To rs.Fields.Count-1 ↵ Debug.Print rs.Fields(i) ↵ Next` – Fionnuala Mar 10 '12 at 18:24
  • Can you post about 10 lines of the text file? You could also try a different column, preferably one that is numeric, so you have something on the lines of `WHERE SomeCol = 10` – Fionnuala Mar 12 '12 at 19:15
  • See notes on schema.ini above. – Fionnuala Mar 12 '12 at 21:28
  • 1
    If you use `strSQL = "SELECT * FROM [Ordini BO new.txt] a WHERE 1=2` and then add a line `Debug.Print rs.Fields(0).Name`, I bet you do not have single field names, if that is the case, there is a problem with the delimiter. What did you use as the delimiter when importing into Access? – Fionnuala Mar 13 '12 at 08:40
  • I cannot move this to chat, and I cannot vote you up, because you only have submitted one question, so would you mind going to http://lessthandot.com and send a real sample of the textfile (not just a cut and paste) using PM? – Fionnuala Mar 13 '12 at 09:13