SQL String for union query on dynamic number of tables
in [Help]
|
Prev: Duplicate a sql case statement in Access
Next: using fsubNavigation form with only setting record source at gotfocus
From: Robin Riviere on 27 Apr 2010 10:34 I'm using Access 2007 and am trying to create an SQL string in VBA which loops through each record of a query containing all the names of the tables I'd like included in a union query. Does anyone have a quick snippet of code to identify the query containing the tables names and then loop through each record pulling the table names from the "tbl_Name" field? Thanks, RR
From: Rich P on 27 Apr 2010 11:23 Here is a quick review on union queries: You have to have the same number of columns with the same data types for each union select fld1, fld2, fld3 from tblA union all select fld1, fld2, fld3 from tblB union all select fld1, fld2, fld3 from tblC union all ... The columns don't actually have to be the same name, just the same number and same data types. But if you have several tables that are basically all the same then this suggests a redundancy in your system. An RDBMS -- whether file based (Access) or server based (Sql Server) -- is all about eliminating redundancy. IF this is your case, you may want to retool your system. Just a thought. Union queries are more for like if you wanted to pull data from tables in different databases into one query. Rich *** Sent via Developersdex http://www.developersdex.com ***
From: Robin Riviere on 27 Apr 2010 11:32 On Apr 27, 11:23 am, Rich P <rpng...(a)aol.com> wrote: > Here is a quick review on union queries: You have to have the same > number of columns with the same data types for each union > > select fld1, fld2, fld3 from tblA union all > select fld1, fld2, fld3 from tblB union all > select fld1, fld2, fld3 from tblC union all > .. > > The columns don't actually have to be the same name, just the same > number and same data types. > > But if you have several tables that are basically all the same then this > suggests a redundancy in your system. An RDBMS -- whether file based > (Access) or server based (Sql Server) -- is all about eliminating > redundancy. IF this is your case, you may want to retool your system. > Just a thought. Union queries are more for like if you wanted to pull > data from tables in different databases into one query. > > Rich > > *** Sent via Developersdexhttp://www.developersdex.com*** Rich, Thanks for the feedback. The union query I have in mind will combine a variable number of linked tables; all of which are structured identically. I have created a query which lists the table names of all the linked tables. My objective is to create procedure which loops through each record in the query, capturing the table names and adding those names to the union query string. Does that make sense? Thanks, RR
From: Salad on 27 Apr 2010 11:34 Robin Riviere wrote: > I'm using Access 2007 and am trying to create an SQL string in VBA > which loops through each record of a query containing all the names of > the tables I'd like included in a union query. Does anyone have a > quick snippet of code to identify the query containing the tables > names and then loop through each record pulling the table names from > the "tbl_Name" field? > > Thanks, > RR This identifies the fields (first in this ex) in a query Dim q As QueryDef Set q = CurrentDb.QueryDefs("Query1") MsgBox q.Fields(0).Name You could get the SQL statement q.SQL and then you'd need to parse it out. A query might not contain but 1 table but many with inner, left, right joins. Here's a sub to enumerate thru to queries Public Sub QueryList() Dim qdf As QueryDef For Each qdf In CurrentDb.QueryDefs If Left(qdf.Name, 1) <> "~" Then 'do something End If Next qdf MsgBox "Done" End Sub
From: Rich P on 27 Apr 2010 12:35
Hi Robin, I guess linked tables meets the criteria of tables from different databases. So it isn't a redundancy issue. What you can do is to loop through the DAO.TableDef collection to get the table names. If all the tables are linked tables you can use the TableDef.Attributes property to distinguish this. System tables all start with 2 (or -2). Local tables start with a 0. Anything else is a linked table (either linked to another mdb or an ODBC connection). You can collect these table names into an array (or another table) and then assign the names from this array to your union query. Here is one technique: Dim DB As DAO.Database, tdf As DAO.TableDef Dim arrTbl() as string, i As Integer, j As Integer Dim str1 As String, str2 As String i = 0 j = 0 Set DB = CurrentDb For Each tdf In DB.TableDefs str1 = "" If tdf.Attributes <> 0 Then str1 = tdf.Connect Debug.Print tdf.Attributes & " " & tdf.Name & " " & str1 str2 = ABS(tdf.Attributes) '--absolute value If str1 = "0" Or Left(str2, 1) <> "2" Then i = i + 1 End If Next Redim arrTbl(i) For Each tdf In DB.TableDefs str2 = ABS(tdf.Attributes) '--absolute value If str1 = "0" Or Left(str2, 1) <> "2" Then arrTbl(j)j = tdf.Name j = j + 1 End if Next Rich *** Sent via Developersdex http://www.developersdex.com *** |