How to run loop append query ?

[mystic2k]

First let me say I'm an absolute beginner with Access, but I know my way in Excel and have some basic knowledge of VBA.

So I have this query, selecting data from a linked ODBC table.

What it does is group by 3 fields (date, product, department), and sum a 4th field (sales), with filters (as specific as possible) on each of the 3 first fields. My objective is to get the total of daily sales for 1 given product and department, over the last 5 years.

Problem: the table is absolutely enormous (I estimate over 100 million records), and running the query for 1 day of data will take 5-10 minutes, and running it for several days will most likely timeout (I've already increased the ODBC timeout to 3000 seconds).

Since I can't simply run the query for all days at once (or even for 1 week at a time for that matter), I thought maybe I can make it an APPEND query and loop it using VBA, so it will run for 1 day at a time and keep adding the output to a table. It will take days to gether the data but that's not a major issue. However I really don't know how to do that.

Questions :

- How do I program this in VBA? Ideally I'd like something that can handle basic errors (the occasional timeout) so I can let it run overnight.

- What's the best way to manage the dates ? Not all days have data, so would it be useful to build a list of days which do have data for the VBA code to loop through ?

- How do I ensure I can stop the query, and have it restart where I left it ?

- Any suggestions on alternative ways to achieve my desired outcome are welcome! I can get assistance from developpers if needed, but would prefer handling it myself.

Thanks a lot in advance!

Laurent

 

[Norie]

Laurent

I don't see how an Append query would help, in fact I think it might actually cause problems.

Do you really have that many records? If you do I would suggest you look for another application than Access - it has it's limitations just as Excel has.

 

[GeofW]

Laurent - clarifying question -

selecting data from a linked ODBC table

- is the table containing a million records a base Access table? That is, it is not a linked Oracle or SQL Server table?

 

[xenou]

If you can swing a pass through query it might go faster. Not sure.
http://support.microsoft.com/kb/303968
http://www.aspfree.com/c/a/Microsoft-Access/On-Using-Passthrough-Queries-in-MS-Access/

As far as programming a loop its probably not too hard if you are looping, say, one day at a time ... have you done anything like that before, though? I hope the above idea of a pass-through query works better for you and problem solved!

 

[mystic2k]

Thanks for your replies!
So here's some additional information :

- The table is a SQL Server table, and yes it does have that many records for sure.
- Using another application is probably not going to be an option here - it's a one-time effort. Unless there are some open source solutions?

I thought about an append query because I realized could only query for 1 day at a time - so I thought an append query could be run over and over for 1 day at a time, and keep adding the results to an output table.

As you can tell I'm new to this stuff, and mostly learning by doing. I will try now with a pass-through query and see how that works out! Any other suggestions are very much welcome.

Laurent

 

[Norie]

Laurent

Why don't you use SQL Server?