Access 2003 - How to define a variable for looping

[wanderer27]

I have a huge DB that I need to split up and recombine - it barely fits within the 2 GB capacity of Access.

There are a massive amount of duplicates that I need to clean up so I can make this DB available for further processing.

SELECT DISTINCT blows it past the 2 GB limit.

DIM and DECLARE (which I've seen from examples) won't work for some reason.

PARAMETERS [count] INT does seem to pass, but I can't use SET to initialize [count] 🙁

I'm trying to set up a loop where I can split out the file by Hours (even Months is too big) and then recombine it.

This is sort of what I'm shooting for within a loop:


SELECT DISTINCT * INTO NEWDB FROM
(
SELECT * FROM DB WHERE HOUR = [count]
)


Actually I'll need to do UPDATE or APPEND in rejoining, but I can't even get that far yet.

I can't use the VB functionality of Access, because ultimately this Query will be called from another program running SQL on another Computer and the VB stuff wouldn't fly.

Yeah, I could probably do this manually one Hour at a time, but I have several other upcoming DB's that are probably going to be in the same situation.


So can anyone point in the right direction on how to set up a counter variable in Access under these conditions?


TIA

 

[KLin]

Try linking the table from the current mdb into a new mdb file that has no data, then do your select queries there to get partitions of the data, then just do make table queries off of that.

 

[wanderer27]

Thanks for the suggestion KLin, that's kind of what I'm doing.

I'm trying to write this in a basically blank Access DB with just the Table with the data linked - i.e. a virtual Table. The original Table is pretty much too large to do anything in.

I should also specify that I'm doing all this in the SQL mode since I can't really use the GUI functionality in the other programs, and I seem to have more control/flexibility in straight SQL. This eventually has to be migrated once I get it figured out anyway.

It would be nice if all the different versions of SQL were standardized too. Each version I have access to (Access, TOAD, Oracle, MySQL, MS SQL, SQL Server) all have little quirks that cause them to work on one version but not another 🙁
This has caused quite a few headaches today . . . .

Definitely been a learning experience.