add variable table name to query results

[sparlaman]

Scenario: A vendor has setup an access database as a holding place for
individual tables with one or two records of common data instead of one table
with many records. Each day (sometimes more than once a day) a new table is
added to this database with a new name. For obvious reasons my options for
retrieving data are limited.

Question: Is there some way to include the table name in a query
automatically?

Obsticles I'm encountering: I can't manually type the table name into an
'expression' field within the query because the name changes.

I have learned that I can query MSysobjects to retreive the names and other
data about every table in the database but what I can't figure out is how to
use that information and/or relate back to the query to include the table
name in the query.

Example: table1 contains field1, field2, field3, etc... I want Query1 to
include table1.field1, table1.field2, table1.field3, AND Table1's table name

I hope this makes sense. Thanks for everyone's help in advance.

sparlaman

 

[Dirk Goldgar]

Scenario: A vendor has setup an access database as a holding place for
individual tables with one or two records of common data instead of one
table
with many records. Each day (sometimes more than once a day) a new table
is
added to this database with a new name. For obvious reasons my options for
retrieving data are limited.

Question: Is there some way to include the table name in a query
automatically?

Obsticles I'm encountering: I can't manually type the table name into an
'expression' field within the query because the name changes.

I have learned that I can query MSysobjects to retreive the names and
other
data about every table in the database but what I can't figure out is how
to
use that information and/or relate back to the query to include the table
name in the query.

Example: table1 contains field1, field2, field3, etc... I want Query1 to
include table1.field1, table1.field2, table1.field3, AND Table1's table
name

I hope this makes sense. Thanks for everyone's help in advance.

I'm going to assume that you're stuck with this situation, and you can't
just tell the idiot vendor, "Don't do that!"

You have two approaches:

1. Use code to copy all the data from the tables into one table (tagged in
an extra field so you know -- if necessary -- what table a record originally
came from). Then forget or even delete the little tables, and do all the
rest of your processing with the consolidated table.

-- OR --

2. Build SQL on the fly, each time you need to do something with one of the
tables. This will involve VBA code, but you can build an SQL statement as a
string, and then ether execute that string directly or save it into the SQL
property of a stored query. For example,

Dim strTableName As String

strTableName = "Table1"

CurrentDb.QueryDefs("Query1").SQL = _
"SELECT Field1, Field2, Field3, " & _
"'" & strTableName & "' AS SourceTable " & _
"FROM [" & strTableName & "]"

 

[sparlaman]

Exactly! I like your first idea. I actually thought about that right after I
posted my question. This is the kind of thing that happens when an ex IT
Support person gets a job as a secretary/occasional IT help...

Now... how do I do this? I saw a post about using SQL to create a union
query. I would want to reference every field in every table beginning with
the word "Inventory".

I have a decent understanding of relational databases and Access plus a
smidgen of training with SQL plus for Oracle. However, I can't remember most
of the SQL and/or VBA that I once knew but I can understand it if I see it.

Any chance you could give me a hand with the code?

Scenario: A vendor has setup an access database as a holding place for
individual tables with one or two records of common data instead of one
table
with many records. Each day (sometimes more than once a day) a new table
is
added to this database with a new name. For obvious reasons my options for
retrieving data are limited.

Question: Is there some way to include the table name in a query
automatically?

Obsticles I'm encountering: I can't manually type the table name into an
'expression' field within the query because the name changes.

I have learned that I can query MSysobjects to retreive the names and
other
data about every table in the database but what I can't figure out is how
to
use that information and/or relate back to the query to include the table
name in the query.

Example: table1 contains field1, field2, field3, etc... I want Query1 to
include table1.field1, table1.field2, table1.field3, AND Table1's table
name

I hope this makes sense. Thanks for everyone's help in advance.

I'm going to assume that you're stuck with this situation, and you can't
just tell the idiot vendor, "Don't do that!"

You have two approaches:

1. Use code to copy all the data from the tables into one table (tagged in
an extra field so you know -- if necessary -- what table a record originally
came from). Then forget or even delete the little tables, and do all the
rest of your processing with the consolidated table.

-- OR --

2. Build SQL on the fly, each time you need to do something with one of the
tables. This will involve VBA code, but you can build an SQL statement as a
string, and then ether execute that string directly or save it into the SQL
property of a stored query. For example,

Dim strTableName As String

strTableName = "Table1"

CurrentDb.QueryDefs("Query1").SQL = _
"SELECT Field1, Field2, Field3, " & _
"'" & strTableName & "' AS SourceTable " & _
"FROM [" & strTableName & "]"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Exactly! I like your first idea. I actually thought about that right after
I
posted my question. This is the kind of thing that happens when an ex IT
Support person gets a job as a secretary/occasional IT help...

Now... how do I do this? I saw a post about using SQL to create a union
query. I would want to reference every field in every table beginning with
the word "Inventory".

I have a decent understanding of relational databases and Access plus a
smidgen of training with SQL plus for Oracle. However, I can't remember
most
of the SQL and/or VBA that I once knew but I can understand it if I see
it.

Any chance you could give me a hand with the code?

Do all the tables have the same fields, in the same order? If so, it's not
that hard. For example, one could use code like this:


'----- start of example code -----
Dim strSQL As String

Dim ao As AccessObject

For Each ao In CurrentData.AllTables

If ao.Name Like "Inventory*" Then
strSQL = strSQL & _
" UNION ALL SELECT *, '" & ao.Name & "' As SourceTable " & _
"FROM [" & ao.Name & "]"
End If

Next ao

If Len(strSQL) > 0 Then

strSQL = "INSERT INTO TargetTable SELECT * FROM (" & _
Mid$(strSQL, 11) & ")"

CurrentDb.Execute strSQL, dbFailOnError

End If
'----- end of example code -----

Note that I am assuming here that the output "TargetTable" has already been
created, and has the same fields, in the same order, as the input tables,
except that it has an additional text field named "SourceTable" at the end
of the list.

 

[sparlaman]

Thank you! I'll give this a try.

Exactly! I like your first idea. I actually thought about that right after
I
posted my question. This is the kind of thing that happens when an ex IT
Support person gets a job as a secretary/occasional IT help...

Now... how do I do this? I saw a post about using SQL to create a union
query. I would want to reference every field in every table beginning with
the word "Inventory".

I have a decent understanding of relational databases and Access plus a
smidgen of training with SQL plus for Oracle. However, I can't remember
most
of the SQL and/or VBA that I once knew but I can understand it if I see
it.

Any chance you could give me a hand with the code?

Do all the tables have the same fields, in the same order? If so, it's not
that hard. For example, one could use code like this:


'----- start of example code -----
Dim strSQL As String

Dim ao As AccessObject

For Each ao In CurrentData.AllTables

If ao.Name Like "Inventory*" Then
strSQL = strSQL & _
" UNION ALL SELECT *, '" & ao.Name & "' As SourceTable " & _
"FROM [" & ao.Name & "]"
End If

Next ao

If Len(strSQL) > 0 Then

strSQL = "INSERT INTO TargetTable SELECT * FROM (" & _
Mid$(strSQL, 11) & ")"

CurrentDb.Execute strSQL, dbFailOnError

End If
'----- end of example code -----

Note that I am assuming here that the output "TargetTable" has already been
created, and has the same fields, in the same order, as the input tables,
except that it has an additional text field named "SourceTable" at the end
of the list.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)