Access 2007 Current Path string

I have the following string on an Access 2003 form which displays the path of a file, but for some reason when I run this form in 2007 I get the #?Name error message.

Is there syntax I am missing for 2007?

=Mid(CurrentDb.TableDefs("COMBINED").[Connect],InStrRev(CurrentDb.TableDefs("COMBINED").[Connect],"=")+1)+"\COMBINED.txt"
szadrogaAsked:
Who is Participating?
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
Sorry again...

Use this as the controlSource of a textbox:

=DLookUp("Database","msysobjects","Name='Combined'")
0
 
Helen FeddemaCommented:
Here is the syntax for getting the path of the current database:

strDBPath = Application.CurrentProject.Path & "\"
0
 
Helen FeddemaCommented:
If you are looking for something else, please clarify.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
szadrogaAuthor Commented:
I am looking to return the location of a currently linked table named "COMBINED"  which is located in the current DB
0
 
Jeffrey CoachmanMIS LiasonCommented:
SELECT Database
FROM msysObjects
WHERE Name='Combined';
0
 
Jeffrey CoachmanMIS LiasonCommented:
Or:...
SELECT Database
FROM msysObjects
WHERE Name="Combined";
0
 
szadrogaAuthor Commented:
Works perfectly, thanks for your help.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"but for some reason when I run this form in 2007 I get the #?Name error message."

Using CurrentDb, Environ() & RecordsetClone (and possibly others) no longer work in expressions used in the Control Source of a text box starting with A2007.  It's unclear if this is a bug or these were never supposed to work and Microsoft simply 'tightened' things up.

So for example
=Environ("UserName")  will result in #Error.

Use the example give by boag2000 above.  That will work.

mx

0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks Joe
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
:-)
0
 
Jeffrey CoachmanMIS LiasonCommented:
szadroga,

The "Connect" field may also be of interest...
...especially with ODBC linked tables

You can also narrow the search by Filtering for TYPE=6
Which will only give you "Linked tables"

You could have probably retrieved this data by loading the control via code:
Me.txtPath=CurrentDB....
...as many developers do not like to query the Msysobjects table directly.
Sometimes I use it because the SQL syntax is easy to remember...

;-)

jeff

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.