Can I insert the Table Name in a query result field?

D

Dave

I want to run a query against a individuals tbales and append the results.

Is there a way to get the table name and put it in a result filed?

Thanks
 
B

Bob Barrows

Dave said:
I want to run a query against a individuals tbales and append the
results.

Is there a way to get the table name and put it in a result filed?

Thanks
Click to expand...
Automatically? No. You would need to hard-code the tablename into the
query.
 
B

Bob Barrows

I'm trying to figure out why you need it to be automatic. It implies
that you don't know at design time what table the query will be running
against. The only scenario where that is even possible is if you are
using dynamic sql to insert the table's name into the FROM clause of a
sql string. In that case, all you need to do is insert the tablename
into an expression within the string.

The goal would be:

Select ... "mytable" as SrcTable, ... FROM mytable

the VBA code would be:

dim tblname as string,sql as string
tblname = "mytable"
sql="Select ... """ & tblname & """ as SrcTable, ... FROM " & _
tblname
 
D

Dave

Thnaks for the response.

The task is I have about 50 spreadsheets (varies each month) linked to an
Access Database. I get these each month and then I append the data to a
single table. The linked spreadsheets only have a single column of info and
I want to know where the data came from.

So I thought I could add a column to the quries that would grab the table
name, then insert that next to the data point with minmal intervention on my
part.

Dave
 
B

Bob Barrows

VBA is your only alternative. I'm a little surprised you're not already
using VBA for this task.

I don't have time right now to whip up a subroutine for you, but you
might find something here: http://www.rogersaccesslibrary.com/

If not, and nobody else replies here, post to one of the VBA newsgroups.
Thnaks for the response.

The task is I have about 50 spreadsheets (varies each month) linked
to an Access Database. I get these each month and then I append the
data to a single table. The linked spreadsheets only have a single
column of info and I want to know where the data came from.

So I thought I could add a column to the quries that would grab the
table name, then insert that next to the data point with minmal
intervention on my part.

Dave
Click to expand...
 
Top