return table name in query results

[RichardMarbat]

Hi guys

I hope this hasn't been asked before, I did look...

I would like to return the table name as a field in the query, I know I can type it in but I have many tables to run this on and sooner or later ill get it wrong so I would like to return it from a system variable if possible. I'm an old FoxPro programmer, in fox, amongst other ways, you could 'select blah, blah,dbf() as tablename from blah, etc'

is there an equivalent in Access?

Thanks, Richard

 

[jdraw]

What exactly is the query involved?

 

[MajP]

There are several versions of this.

Redirecting

How can I get table names from an MS Access Database?

Microsoft SQL Server and MySQL have an INFORMATION_SCHEMA table that I can query. However it does not exist in an MS Access database. Is there an equivalent I can use?

stackoverflow.com

 

[RichardMarbat]

What exactly is the query involved?

selecting pretty much the whole file and then exporting as text - this is then consumed into a mysql database - the table name is used to reference the source of that batch. so not really a working Access system as such - the data is only required for a couple of months and then deleted, this cycle just continues ...

I have tried googling this, I don't want to know the tables in the database ect i just want my query to look something like this ...

select field001,field002,[name of the table im selecting from] as sourcefile from table

this query is just exported to a delimited text file ...

 

[jdraw]

If you have some code to create and export the query data, then it is quite common to include the source table name (and even a date/timestamp) in the data.
We need to know more about your process. Where Access fits? How the query is generated/created? etc.

 

[The_Doc_Man]

This is somewhat suspicious. (Not "illegal"-suspicious, but "concept"-suspicious). Unless you were doing a SELECT *, "tablename" FROM tablename; or something VERY similar, the SELECT portion that retrieves the desired fields by name would ALSO have to be customized for each table... which in turn is unlikely UNLESS you have a set of tables that are structurally the same and the name of the table conveys some data in and of itself. Like, for example, a separate sales history table for each year or each department or each salesperson.

IF this is what you have, then you have committed a structural/design error. If this is not what you have, then I have to ask you "what is the goal?" Because Access has ways for you to determine table names in various ways, but I don't know of an intrinsic structure or function that would tell you from within a query the name of the table you were querying. They just don't directly allow definition of the table in the FROM clause and as a constant field in the same query unless the name was already known when you were writing the SQL.

 

[RichardMarbat]

Hello Doc Man

Seems like a strange thing i guess, let me explain...

I work for a data processing company, we receive a file each day/week/month from one of our clients, we then import into Access, process and prep the data to meet posts standards - this goes on to produce personalized mail, we then export the processed data and import this onto mysql as a reference for any returned mail (gone no address, etc) the data can receive can vary in structure so tables are created for each file received. so data in - processs - out thats pretty much the lifespan of the file

I can simply use: select field1,field2,"job "12345" as filename from whatever table

but rather than typing "12345" i just wanted to use a variable/function or something system related so i don't make a typo or if I used the wrong table it would show in the result.

the only example i can give of what i want is the foxpro one

select field1,field2,dbf() as tablename (the dbf() function simply returns the name of the table)

thanks, Richard

 

[jdraw]

Can you provide some details of the steps involved in this?

we receive a file each day/week/month from one of our clients, we then import into Access, process and prep the data

 

[RichardMarbat]

the work is already done... we run many processes for many clients using a wide range of tools, Access being one of them.

I had to run these for a colleague today and just wanted something like this:

select URN,name,tablename as tablename from job12345 (the bold part is where I want the name of the table returned)

so my output would look something like this:
"URN",Name"," tablename"
"000001","Richard"," job12345"
"000002","John"," job12345"
"000003","Mary"," job12345"

But I'm having to hardcode it as

select URN,name,"job12345" as tablename from job12345

i just want to not have to type the table name's name and use a function/system variable that will return the name, it's done in foxpro with
dbf() (this returns the table name)

i'm new to Access and wondered if there is an equivalent function to dbf()

Thanks Richard

 

[The_Doc_Man]

If you make some assumptions, you might be able to GENERATE the SQL for a SELECT query, in which case you could generate the name as a quoted constant. If you know the tablename AND it exists when you want to do this, then you could open that table's tabledef to find the Fields collection, from which you could find the names of the fields. Here is a very simplified link that deals with field names. It starts with knowing the table name.

How to list all the field names in a specified table?

social.msdn.microsoft.com

If you work at it, you might be able to build your query, and your idea to use "xxxx" AS tablename as one of the field elements would then be trivial because you would have that table name in order to get to the field defs.

As far as table names go, there is a tabledefs object that lists tables and that can be examined.

How can I get table names from an MS Access Database?

Microsoft SQL Server and MySQL have an INFORMATION_SCHEMA table that I can query. However it does not exist in an MS Access database. Is there an equivalent I can use?

stackoverflow.com

This link shows a "FOR EACH" loop to enumerate all tables. You want to avoid system tables because they will contain no useful info for your main problem and will contain TOO MUCH info for solving the "table name" problem.

 

[Josef P.]

But I'm having to hardcode it as

select URN,name,"job12345" as tablename from job12345

Do you create a constant with the SQL statement? Then it doesn't really matter whether a function or the string is the constant in the SQL statement.

If you want to fetch multiple data, I would expect the SQL statement to be created dynamically as well.
With ADODB.Connection.OpenSchema you can read the table definition relatively well.

 

[RichardMarbat]

Thanks so much for your time guys, I did google the question and didn't really find any answers so I thought I would reach out to the members here, looks like there isn't an easy way like the fox example I gave.

I'll take a look at the links posted here and have a good read as i'm keen to learn all I can - you never know what else you'll find out once you start reading examples of code...

Thanks again guys - if/when I find a solution ill post an update back here

best regards, Richard

 

[ebs17]

we then import into Access

If you need the table name as a field value in the table, it will be easiest to write it in an additional field directly during import, because you have all the information in your hands during import.

 

[gemma-the-husky]

I find some stuff can more easily be returned from a function. Where does the current table name get derived from? It can't just magically happen. You must select an external file or something similar somewhere?

 

[Pat Hartman]

Don't you know the name of the table you are extracting from when you extract it? Just reference a form field.

Select fld1, fld2, Forms!yourform!yourtablename as fld3 from xxx