Access Database name in query

Grasshopper3
Grasshopper3 used Ask the Experts™
on
Hi,

Is there syntax that I can use in the design view of an access query that will return the database name of the active database?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Since Access may have linked tables to several databases as well as access data without a connection, you may have to redefine your question

in a label you might be able to set the value to a database property to return the name of the Access File.

Commented:
For file name:  currentproject.name
Database Architect / Application Developer
Top Expert 2007
Commented:
In VBA Code you have (examples):

CurrentProject.Name
returns aaEE2.mdb

CurrentProject.Path
C:\Documents and Settings\Administrator\Desktop\EE

CurrentProject.FullName
C:\Documents and Settings\Administrator\Desktop\EE\aaEE2.mdb

So, you could call a Function in a standard VBA Module to return this information ...

mx
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
Forgot to mention that for the application title you would use:

currentdb.properties.("AppTitle")

You can create a public function, then call it from the query.

Commented:
Short answer: No.
Long answer: If, as suggested, you create a function to return the db path and name, yes. Which you could call in deign view, if you use (right click) Build, functions, [db name], [module name],[function name]
Short question: Why? (As suggested, too.) The DB knows itself as currentdb.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
This actually works in A2003:

SELECT Table1.ID, [CurrentProject].[Name] AS db
FROM Table1;

OR

SELECT Table1.ID, [CurrentProject].[FullName] AS db
FROM Table1;

OR

SELECT Table1.ID, [CurrentProject].[Path] AS db
FROM Table1;



But I may not work in A2007/10

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
So, Short Answer is actually Yes :-)

Commented:
Silly me, I used currentdb.name. Works in 2007, too.  Point to Joe.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
And finally:

SELECT Table1.ID, [CurrentDB].[Name] AS db
FROM Table1;

mx

Commented:
currentdb.name does not work in the Access 2007 I have
Hamed NasrRetired IT Professional

Commented:
What do want to do?
Give an example.
Interesting Q given the outcome.  I agree with findings above - All OK in A2003 but currentdb.name doesn't work in A2010  (in a query ).  

As an added bonus (:-) neither of them work  if you save the query and then try to open a recordset based on the saved query.  This applies to both A2003 and A2010.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
I suspected CurrentDB.Name would work in A2010 (7) ... because Microsoft did something that changed that, OR ... it was never supposed to work.  CurrentProject.WhatEver does work ... BUT, not sure I would put that in 'production' .. because it may not work in the future.

I would suggest what I posted at http:#a38009911 ... which is always going to work.

(But the short answer is ... still yes ... at the moment)
mx

Author

Commented:
I've requested that this question be deleted for the following reason:

Solved problem
I thought it was DatabaseMX who provided the solution?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial