union query

[abourg]

Is there a max # of select clauses that can be used in a union query.

 

[dhookom]

At some point (maybe depending on the number of fields or whatever) the union query my become too complex. You may need to experiment to find the limit.

Duane
Hook'D on Access
MS Access MVP

 

[genomon]

This may prove helpful:

http://www.databasezone.com/techdocs/acclimit.html

Cogito eggo sum – I think, therefore I am a waffle.

 

[lameid]

Genonom is on the right track. I think the limit is in the number of characters in the query not the number of tables or select statements.

Although Duane is also right there is a practical limit before Access starts whining about being too complex. You might get around some errors by increasing the max locks per file in the resgistry. The below text if put in notepad and saved as a .reg file will increase the default value by 4 times, at least for Access 2003. You can also navigate there yourself using the registry editor. Of course you can generally make a total muck of your system if you do something wrong and make your system unbootable. I recommend setting a restore point and not messing up

If you were to mess up you'd have to find out how to restore a registry hive manually... The only time I did so I used Trinity Rescue Kit (a linux rescue boot cd) to access the file system and manually copied the restore point hive over the corrupted registry. It was not a fun experience and if you mess up the registry doing this, I recommend you find someone else to fix it (That reminds me, linux paths are case sensitive <cringe>)

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0]
"MaxLocksPerFile"=dword:00009470

Also a way to keep the number of charcters down is to use queries and not true select statments for your Union.

For example:

Select *
From Query1
Union All
Select *
From Query2