Prev: Duplicate a sql case statement in Access
Next: using fsubNavigation form with only setting record source at gotfocus
From: Rich P on 27 Apr 2010 12:45
Correction:

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 str2 = "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 str2 = "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 ***
From: Robin Riviere on 27 Apr 2010 12:56
On Apr 27, 12:35 pm, Rich P <rpng...(a)aol.com> wrote:
> 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 Developersdexhttp://www.developersdex.com***

Thanks Rich. That's very helpful and more elegant than what I have
currently. However, at this point, I have created a query which
captures all the names of the tables I want to include in my union
query. The hang-up I currently have is looping through each record in
the field containing those names to add the contents of each record to
the SQL string which comprises the union query. Do you have any
advice on how to take the contents of each record and insert it into
the query string? Below is the code I have thus far...

Thanks again,
RR

Public Function MakeUnionSQL() As Boolean
Dim rst As DAO.Recordset
Dim DB As DAO.Database
Dim qdf As QueryDef
Dim strSQL, strUnion As String

Set DB = CurrentDb
strSQL = ""
Application.RefreshDatabaseWindow

Set rst = dbs.OpenRecordset(qry_IDLinkedTables, dbOpenDynaset)
With rst
If Not (.EOF And .BOF) Then
.MoveFirst
Do Until .EOF
strUnion = ""
strSQL = strSQL & strUnion
strSQL = strSQL & " SELECT qry_IDLinkedTables.tblName
FROM qry_IDLinkedTables;" (This is where I'm getting screwed up...)
strUnion = " Union"
Loop
End If
.Close
End With

Set rst = Nothing
Set DB = Nothing
Set qdf = DB.CreateQueryDef("qry_TblUnion", strSQL)
DB.QueryDefs.Refresh
Application.RefreshDatabaseWindow

Set DB = Nothing
makeUnionSQL = True

End Function
From: paii, Ron on 27 Apr 2010 13:28
The hidden system table MSysObjects can list all tables in or linked to the
MDB.

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=6));

"Robin Riviere" <deltacompany94(a)gmail.com> wrote in message
news:1ded3192-873a-4f9e-b47b-8603007ee717(a)r27g2000yqn.googlegroups.com...
> 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 13:45
Hi Robin,

I can kind of see what is going on here - in a general sense. You are
trying to perform operations in an Integrated developement environment
(Access) that would be better suited for a non integrated development
environment (.Net). In the past I used to refer to this as enterprise
operations in a non enterprise system (Access). But I think that
Integrated vs Non Integrated development environment is more accurate.
Anyway, it looks like you are trying to develop a sql string using the
"Union" operator.

I am perceiving that you want to write a query that would look something
like this -- general format:

select fld1, fld2, fld3, ... from tbl1 union all
select fld1, fld2, fld3, ... from tbl2 union all
select fld1, fld2, fld3, ... from tbl3 union all
...

I am guessing that the field names are all the same but that table names
is where you are having your issue. Again, loop through the tabledef
collection to get the names of your linked tables. YOu could even store
these table names in a local table and then loop through this table to
add the table names to your sql string:

Dim strSql As String, str1 As STring, str2 As String
Dim DB As DAO.Database, RS As DAO.RecordSet
Set DB = CurrentDB
Set RS = DB.OpenRecordset("Select tableName From LocalTbl")
strSql = ""
str2 = "Select fld1, fld2, fld3, ... From "
Do While Not RS.EOF
str1 = RS!TableName
RS.MoveNext
If Not RS.EOF Then
strSql = strSql & str2 & str1 & " Union All "
Else
strSql = strSql & str2 & str1
End If
Loop

As for the non integrated thing, Querydefs work best in the integrated
environment. For your purposes, I wouldn't use querydefs because your
are working with tables that aren't local to your DB. It can be done,
but you will end up with a bunch of spaghetti code to make it work. I
say this respectfully.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: Robin Riviere on 27 Apr 2010 13:55
On Apr 27, 1:45 pm, Rich P <rpng...(a)aol.com> wrote:
> Hi Robin,
>
> I can kind of see what is going on here - in a general sense.  You are
> trying to perform operations in an Integrated developement environment
> (Access) that would be better suited for a non integrated development
> environment (.Net).  In the past I used to refer to this as enterprise
> operations in a non enterprise system (Access).  But I think that
> Integrated vs Non Integrated development environment is more accurate.
> Anyway, it looks like you are trying to develop a sql string using the
> "Union" operator.
>
> I am perceiving that you want to write a query that would look something
> like this -- general format:
>
> select fld1, fld2, fld3, ... from tbl1 union all
> select fld1, fld2, fld3, ... from tbl2 union all
> select fld1, fld2, fld3, ... from tbl3 union all
> ..
>
> I am guessing that the field names are all the same but that table names
> is where you are having your issue.  Again, loop through the tabledef
> collection to get the names of your linked tables.  YOu could even store
> these table names in a local table and then loop through this table to
> add the table names to your sql string:
>
> Dim strSql As String, str1 As STring, str2 As String
> Dim DB As DAO.Database, RS As DAO.RecordSet
> Set DB = CurrentDB
> Set RS = DB.OpenRecordset("Select tableName From LocalTbl")
> strSql = ""
> str2 = "Select fld1, fld2, fld3, ... From "
> Do While Not RS.EOF
>   str1 = RS!TableName
>   RS.MoveNext
>   If Not RS.EOF Then
>     strSql = strSql & str2 & str1 & " Union All "
>   Else
>     strSql = strSql & str2 & str1
>   End If
> Loop
>
> As for the non integrated thing, Querydefs work best in the integrated
> environment.  For your purposes, I wouldn't use querydefs because your
> are working with tables that aren't local to your DB.  It can be done,
> but you will end up with a bunch of spaghetti code to make it work. I
> say this respectfully.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Rich,

You summed it up perfectly. Let me play with what you've posted, and
I'll reply back with my results.

Thanks for taking the time to help.
RR
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Duplicate a sql case statement in Access
Next: using fsubNavigation form with only setting record source at gotfocus