Query Know Thyself?

[Ed B]

Hi everyone,

I have a series of queries that append data from a number of source tables
into one destination table. I would like to add two fields to the
destination table e.g.

WhereDataCameFrom Text 50
WhenDidItGetHere Date/Time

and have the source queries populate these fields whenever they are run.
The Date/Time is no problem: I'l just use DataGotHere:Now() in the SELECT
statement and append that into [WhenDidItGetHere] in the destination table.
However, is it possible for an Append query to "know it's own name" other
than me going through and explicitly typing in the name into the query such
as SourceQueryName:"<NameOfThisQuery>" in the SELECT statement?

In other words, how can a query "lookup it's own name?"

Thanks in advance,

Ed

 

[Ed B]

Aaargh...I Hit "Post" before I remembered to add:

"Furthermore, can the query also know the name of it's database so that
[WhereDataCameFrom] could be e.g. <DatabaseName> & "." & <QueryName>?

Thanks again,

Ed

 

[Allen Browne]

You can do this with a VBA function, Ed.

The QueryDef has Fields.
Each Field has a SourceTable.
Example:
CurrentDb.QueryDefs("Query1").Fields("CompanyName").SourceTable

The Field of the QueryDef also has a SourceField name (in case it's
aliased.)

Once you know the SourceTable name, you can examine the Connect property of
the TableDef to see what database it's from. This GetDataPath() function
does that:
http://allenbrowne.com/ser-53code.html#GetDataPath

An example of looking at all fields in a query:

Function TestQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")

For Each fld In qdf.Fields
Debug.Print fld.Name, fld.SourceTable, fld.Type
Next
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Aaargh...I Hit "Post" before I remembered to add:

"Furthermore, can the query also know the name of it's database so that
[WhereDataCameFrom] could be e.g. <DatabaseName> & "." & <QueryName>?

Thanks again,

Ed

Hi everyone,

I have a series of queries that append data from a number of source
tables
into one destination table. I would like to add two fields to the
destination table e.g.

WhereDataCameFrom Text 50
WhenDidItGetHere Date/Time

and have the source queries populate these fields whenever they are run.
The Date/Time is no problem: I'l just use DataGotHere:Now() in the
SELECT
statement and append that into [WhenDidItGetHere] in the destination
table.
However, is it possible for an Append query to "know it's own name" other
than me going through and explicitly typing in the name into the query
such
as SourceQueryName:"<NameOfThisQuery>" in the SELECT statement?

In other words, how can a query "lookup it's own name?"

Thanks in advance,

Ed

 

[Ed B]

Hi MG,

Thanks for the quick and useful reply.

That's a great idea, but unfortunately it returns the full UNC path and file
type ["C:\MyDocuments\...\...\<DatabaseName>.mdb"], whereas I need only the
name. Do you have any other ideas?

In the mean time, I'm going see if there is a way to use the String
functions to trim off the part before the last '\' and the ".mdb", which
would give me the database name by itself.

Thanks again,

Ed

:

You can get the name of the database, including the full path, like
this:

CurrentDB.Name

I believe the query cannot self-reference it's name. You'll have to
hard-code it into the query.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

Aaargh...I Hit "Post" before I remembered to add:

"Furthermore, can the query also know the name of it's database so that
[WhereDataCameFrom] could be e.g. <DatabaseName> & "." & <QueryName>?

Thanks again,

Ed

Hi everyone,

I have a series of queries that append data from a number of source tables
into one destination table. I would like to add two fields to the
destination table e.g.

WhereDataCameFrom Text 50
WhenDidItGetHere Date/Time

and have the source queries populate these fields whenever they are run.
The Date/Time is no problem: I'l just use DataGotHere:Now() in the SELECT
statement and append that into [WhenDidItGetHere] in the destination table.
However, is it possible for an Append query to "know it's own name" other
than me going through and explicitly typing in the name into the query such
as SourceQueryName:"<NameOfThisQuery>" in the SELECT statement?

In other words, how can a query "lookup it's own name?"

Thanks in advance,

Ed

 

[Ed B]

Allen,

Wow. Great answer. Between what you posted and what is on the page on your
site you referred me to, I can see that I'll be busy for the next few hours
^H^H^H^H^H days learning all kinds of new things about Access and beyond.
Thank you very much.

However, it appears that, while your answer will get me to the database name
(which I need), its still leaves me without the query knowing it's own name,
unless I type it in explicitly within some part of the SELECT statement. Do
you know of any way for a query to look up it's own name using techniques
such as you described to get the database name? IOW, your solution included
the explicit parameter "Query1" in e.g.
CurrentDb.QueryDefs("Query1").Fields... Is there a way to do this 'without
the quotes' like how a Form can "know itself" using Me.Name?

Thanks again for the fantastic input.

Regards,

Ed

You can do this with a VBA function, Ed.

The QueryDef has Fields.
Each Field has a SourceTable.
Example:
CurrentDb.QueryDefs("Query1").Fields("CompanyName").SourceTable

The Field of the QueryDef also has a SourceField name (in case it's
aliased.)

Once you know the SourceTable name, you can examine the Connect property of
the TableDef to see what database it's from. This GetDataPath() function
does that:
http://allenbrowne.com/ser-53code.html#GetDataPath

An example of looking at all fields in a query:

Function TestQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")

For Each fld In qdf.Fields
Debug.Print fld.Name, fld.SourceTable, fld.Type
Next
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Aaargh...I Hit "Post" before I remembered to add:

"Furthermore, can the query also know the name of it's database so that
[WhereDataCameFrom] could be e.g. <DatabaseName> & "." & <QueryName>?

Thanks again,

Ed

Hi everyone,

I have a series of queries that append data from a number of source
tables
into one destination table. I would like to add two fields to the
destination table e.g.

WhereDataCameFrom Text 50
WhenDidItGetHere Date/Time

and have the source queries populate these fields whenever they are run.
The Date/Time is no problem: I'l just use DataGotHere:Now() in the
SELECT
statement and append that into [WhenDidItGetHere] in the destination
table.
However, is it possible for an Append query to "know it's own name" other
than me going through and explicitly typing in the name into the query
such
as SourceQueryName:"<NameOfThisQuery>" in the SELECT statement?

In other words, how can a query "lookup it's own name?"

Thanks in advance,

Ed

 

[Ed B]

This worked:

Public Function Get_DataBaseName() As String
Dim strDBName As String
Dim lngSlashPosition As Long
Dim lngDotPosition As Long

strDBName = CurrentDb.Name
lngSlashPosition = InStrRev(strDBName, "\")
lngDotPosition = InStrRev(strDBName, ".")
Get_DataBaseName = Mid(strDBName, lngSlashPosition + 1, lngDotPosition -
lngSlashPosition - 1)
End Function

Hi MG,

Thanks for the quick and useful reply.

That's a great idea, but unfortunately it returns the full UNC path and file
type ["C:\MyDocuments\...\...\<DatabaseName>.mdb"], whereas I need only the
name. Do you have any other ideas?

In the mean time, I'm going see if there is a way to use the String
functions to trim off the part before the last '\' and the ".mdb", which
would give me the database name by itself.

Thanks again,

Ed

:

You can get the name of the database, including the full path, like
this:

CurrentDB.Name

I believe the query cannot self-reference it's name. You'll have to
hard-code it into the query.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

Aaargh...I Hit "Post" before I remembered to add:

"Furthermore, can the query also know the name of it's database so that
[WhereDataCameFrom] could be e.g. <DatabaseName> & "." & <QueryName>?

Thanks again,

Ed

 

[Allen Browne]

You can use:
CurrentDb.QueryDefs!Query1
The advantage of the approach I suggested is that you can use a string
variable in place of the litteral in quotes.

I'm not sure how you envisage a query knowing its 'own' name. If only one
query is open, you may be able to loop through the CurrentData.AllQueries
collection, testing the IsLoaded property of each until you find it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Wow. Great answer. Between what you posted and what is on the page on
your
site you referred me to, I can see that I'll be busy for the next few
hours
^H^H^H^H^H days learning all kinds of new things about Access and beyond.
Thank you very much.

However, it appears that, while your answer will get me to the database
name
(which I need), its still leaves me without the query knowing it's own
name,
unless I type it in explicitly within some part of the SELECT statement.
Do
you know of any way for a query to look up it's own name using techniques
such as you described to get the database name? IOW, your solution
included
the explicit parameter "Query1" in e.g.
CurrentDb.QueryDefs("Query1").Fields... Is there a way to do this
'without
the quotes' like how a Form can "know itself" using Me.Name?

Thanks again for the fantastic input.

Regards,

Ed

You can do this with a VBA function, Ed.

The QueryDef has Fields.
Each Field has a SourceTable.
Example:
CurrentDb.QueryDefs("Query1").Fields("CompanyName").SourceTable

The Field of the QueryDef also has a SourceField name (in case it's
aliased.)

Once you know the SourceTable name, you can examine the Connect property
of
the TableDef to see what database it's from. This GetDataPath() function
does that:
http://allenbrowne.com/ser-53code.html#GetDataPath

An example of looking at all fields in a query:

Function TestQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")

For Each fld In qdf.Fields
Debug.Print fld.Name, fld.SourceTable, fld.Type
Next
End Function

Aaargh...I Hit "Post" before I remembered to add:

"Furthermore, can the query also know the name of it's database so that
[WhereDataCameFrom] could be e.g. <DatabaseName> & "." & <QueryName>?

Thanks again,

Ed

:

Hi everyone,

I have a series of queries that append data from a number of source
tables
into one destination table. I would like to add two fields to the
destination table e.g.

WhereDataCameFrom Text 50
WhenDidItGetHere Date/Time

and have the source queries populate these fields whenever they are
run.
The Date/Time is no problem: I'l just use DataGotHere:Now() in the
SELECT
statement and append that into [WhenDidItGetHere] in the destination
table.
However, is it possible for an Append query to "know it's own name"
other
than me going through and explicitly typing in the name into the query
such
as SourceQueryName:"<NameOfThisQuery>" in the SELECT statement?

In other words, how can a query "lookup it's own name?"

 

[Ed B]

Thanks Allen!

You can use:
CurrentDb.QueryDefs!Query1
The advantage of the approach I suggested is that you can use a string
variable in place of the litteral in quotes.

I'm not sure how you envisage a query knowing its 'own' name. If only one
query is open, you may be able to loop through the CurrentData.AllQueries
collection, testing the IsLoaded property of each until you find it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

Wow. Great answer. Between what you posted and what is on the page on
your
site you referred me to, I can see that I'll be busy for the next few
hours
^H^H^H^H^H days learning all kinds of new things about Access and beyond.
Thank you very much.

However, it appears that, while your answer will get me to the database
name
(which I need), its still leaves me without the query knowing it's own
name,
unless I type it in explicitly within some part of the SELECT statement.
Do
you know of any way for a query to look up it's own name using techniques
such as you described to get the database name? IOW, your solution
included
the explicit parameter "Query1" in e.g.
CurrentDb.QueryDefs("Query1").Fields... Is there a way to do this
'without
the quotes' like how a Form can "know itself" using Me.Name?

Thanks again for the fantastic input.

Regards,

Ed

You can do this with a VBA function, Ed.

The QueryDef has Fields.
Each Field has a SourceTable.
Example:
CurrentDb.QueryDefs("Query1").Fields("CompanyName").SourceTable

The Field of the QueryDef also has a SourceField name (in case it's
aliased.)

Once you know the SourceTable name, you can examine the Connect property
of
the TableDef to see what database it's from. This GetDataPath() function
does that:
http://allenbrowne.com/ser-53code.html#GetDataPath

An example of looking at all fields in a query:

Function TestQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")

For Each fld In qdf.Fields
Debug.Print fld.Name, fld.SourceTable, fld.Type
Next
End Function

Aaargh...I Hit "Post" before I remembered to add:

"Furthermore, can the query also know the name of it's database so that
[WhereDataCameFrom] could be e.g. <DatabaseName> & "." & <QueryName>?

Thanks again,

Ed

:

Hi everyone,

I have a series of queries that append data from a number of source
tables
into one destination table. I would like to add two fields to the
destination table e.g.

WhereDataCameFrom Text 50
WhenDidItGetHere Date/Time

and have the source queries populate these fields whenever they are
run.
The Date/Time is no problem: I'l just use DataGotHere:Now() in the
SELECT
statement and append that into [WhenDidItGetHere] in the destination
table.
However, is it possible for an Append query to "know it's own name"
other
than me going through and explicitly typing in the name into the query
such
as SourceQueryName:"<NameOfThisQuery>" in the SELECT statement?

In other words, how can a query "lookup it's own name?"