Query a given table list

[Touni102]

Hey all,

I'm back with the same problem I started out with here:
http://www.vbaexpress.com/forum/showthread.php?t=26073

I was able to construct a sql string to give me a query that looks like
"SELECT DISTINCT [Program Name] FROM [table1],[table2], ... , [tableN]" However After about 3 tables, this query runs pretty slow.... Is there a better way to do this task? I just want to get a list of distinct "Program Name"s amongst all of the tables in a list (as the query above says). One detail is that it's not querying ALL of the tables in the database, just a list of the ones that pertain to what I want.

Thanks.

[hansup]

I was able to construct a sql string to give me a query that looks like
"SELECT DISTINCT [Program Name] FROM [table1],[table2], ... , [tableN]" However After about 3 tables, this query runs pretty slow.... . If you remove the DISTINCT keyword, you will see the initial number of rows returned from your approach is equal to the number of rows in table1 multiplied by the number of rows in table2, ..., multiplied by the number of rows in tableN. That number can be huge with only a few tables, and Access creates that result set before filtering it down to only the unique values to satisfy the DISTINCT requirement.



Is there a better way to do this task? I just want to get a list of distinct "Program Name"s amongst all of the tables in a list (as the query above says). One detail is that it's not querying ALL of the tables in the database, just a list of the ones that pertain to what I want.
A UNION query can do what you want:

SELECT [Product Name] FROM table1
UNION
SELECT [Product Name] FROM table2
UNION
SELECT [Product Name] FROM table3;

I'm curious why you have [Product Name] distributed among multiple tables like that. If you provide details, perhaps we can suggest an arrangement which avoids the need to survey [Product Name] values from multiple tables.

Hans

[Touni102]

Maybe it's horrible design, but the reason why i'm doing it this way is because Each table represents A list of programs on that computer and the installation details. I'm guessing you would recommend that I scratch having multiple tables, and just use one table having an extra field name that has the Name of the computer... right? Having individual tables makes it easier to see what's going on, but is slow huh...

Anyway, I tested the query using UNIONs and it worked... for smaller cases. When I use this method on all of the tables I need I get an error:

Microsoft Office database engine could not find the object ".

What's the limit of the sql string?

[hansup]

Maybe it's horrible design, but the reason why i'm doing it this way is because Each table represents A list of programs on that computer and the installation details. I'm guessing you would recommend that I scratch having multiple tables, and just use one table having an extra field name that has the Name of the computer... right? Having individual tables makes it easier to see what's going on, but is slow huh...

Anyway, I tested the query using UNIONs and it worked... for smaller cases. When I use this method on all of the tables I need I get an error:

Microsoft Office database engine could not find the object ".

What's the limit of the sql string?
A VBA string can be a couple gigabytes (I think). I don't know about any limit on the length of a SQL statement.

How long is your statement which fails? Are you sure it's failing because of the length? Can you show us the failing SQL statement and the exact error message?

[CreganTur]

Here are the specs on a SQL string in VBA:

Max char length = ~64,000 chars
Max fields in a recordset = 255 fields

I would be very surprised if you were violating either of those limits.

[Touni102]

OK... not violating the limits... so what's wrong? I'm using a loop to create the sql string, and there are around 60+ iterations... I used the debug window to output the sql string before running:

SELECT [Program Name] FROM [PTS-000000] UNION SELECT [Program Name] FROM [PTS-002508] UNION SELECT [Program Name] FROM [PTS-012109] UNION SELECT [Program Name] FROM [PTS-012309] UNION SELECT [Program Name] FROM [PTS-021209] UNION SELECT [Program Name] FROM [PTS-021309] UNION SELECT [Program Name] FROM [PTS-033009] UNION SELECT [Program Name] FROM [PTS-052308] UNION SELECT [Program Name] FROM [PTS-052708] UNION SELECT [Program Name] FROM [PTS-052908] UNION SELECT [Program Name] FROM [PTS-060408] UNION SELECT [Program Name] FROM [PTS-062008] UNION SELECT [Program Name] FROM [PTS-070308] UNION SELECT [Program Name] FROM [PTS-070408] UNION SELECT [Program Name] FROM [PTS-070708] UNION SELECT [Program Name] FROM [PTS-071108] UNION SELECT [Program Name] FROM [PTS-071308] UNION SELECT [Program Name] FROM [PTS-071408] UNION SELECT [Program Name] FROM [PTS-071508] UNION SELECT [Program Name] FROM [PTS-071608] UNION SELECT [Program Name] FROM [PTS-071708] UNION SELECT [Program Name] FROM [PTS-071808] UNION SELECT [Pro
gram Name] FROM [PTS-071908] UNION SELECT [Program Name] FROM [PTS-072008] UNION SELECT [Program Name] FROM [PTS-072108] UNION SELECT [Program Name] FROM [PTS-072408] UNION SELECT [Program Name] FROM [PTS-081808] UNION SELECT [Program Name] FROM [PTS-091008] UNION SELECT [Program Name] FROM [PTS-100908] UNION SELECT [Program Name] FROM [PTS-CIC-POST-5] UNION SELECT [Program Name] FROM [PTS-CIC-POST-6] UNION SELECT [Program Name] FROM [PTS-CUS-CALL-1] UNION SELECT [Program Name] FROM [PTS-CUSCLEAN-1] UNION SELECT [Program Name] FROM [PTS-CUSCLEAN-2] UNION SELECT [Program Name] FROM [PTS-CUSPHONE-0] UNION SELECT [Program Name] FROM [PTS-CUSPRO-3] UNION SELECT [Program Name] FROM [PTS-CUSPRO-4] UNION SELECT [Program Name] FROM [PTS-CUSPRO-5] UNION SELECT [Program Name] FROM [PTS-CUSPRO-6] UNION SELECT [Program Name] FROM [PTS-CUSPROG-2] UNION SELECT [Program Name] FROM [PTS-DIREASSIST] UNION SELECT [Program Name] FROM [PTS-DIRECTOR-1] UNION SELECT [Program Name] FROM [PTS-DISPATCH-2] UNION SELECT [Program Name]
FROM [PTS-FINSIS-0] UNION SELECT [Program Name] FROM [PTS-FINSIS-1] UNION SELECT [Program Name] FROM [PTS-LOBBY-1] UNION SELECT [Program Name] FROM [PTS-LOBBY-2] UNION SELECT [Program Name] FROM [PTS-LOBBY-3] UNION SELECT [Program Name] FROM [PTS-LOBBY-5] UNION SELECT [Program Name] FROM [PTS-MAINTSUPER] UNION SELECT [Program Name] FROM [PTS-MIS-003] UNION SELECT [Program Name] FROM [PTS-MIS-006] UNION SELECT [Program Name] FROM [PTS-OPSCOORD] UNION SELECT [Program Name] FROM [PTS-OPSVENT] UNION SELECT [Program Name] FROM [PTS-PATROL-1] UNION SELECT [Program Name] FROM [PTS-PATROLONE] UNION SELECT [Program Name] FROM [PTS-TRANSADM] UNION SELECT [Program Name] FROM [PTS-TRANSGRAN] UNION SELECT [Program Name] FROM [PTS-TRANSMAN] UNION SELECT [Program Name] FROM [PTS-TRANSTIME] UNION SELECT [Program Name] FROM [PTS-TRANSWRENCH]

Maybe limits the the amount of unions? I have no clue... It works for cases slightly smaller than this one.

[CreganTur]

I think we can safely say that you're violating correct syntax for union queries in some way... but with so many of them it's hard to tell.

Honestly, you're running into this issue because your database is not normalized. You really should have all of this in a single table, instead of having a different table for every computer. I know that's not the answer you want, but it is an answer that's in-line with best practice and good design... and it'll eliminate this problem.

[Touni102]

I guess that's the easiest way to solve it...
Thanks guys.