Want only database name from Application.CurrentDb.Name

[EagleOne@microsoftdiscussiongroups]

2003

How can one get only the current database path from the (Access converted
macro to VBA) VBA command Application.CurrentDb.Name?

I cannot find a function in Access VBA for the filepath of the current .mdb
file.

So I have tried to find a string function which would locate the first "\"
from the right of Application.CurrentDb.Name but I can not find that function.

Ideally, I want to open a 2nd .mdb file in the same path as my 1st .mdb.
Sure I can hard code it, but I want it to "sense" the path and enter the
actual filename in an InputBox.

Is this possible in Access VBA as it is in Excel VBA?

Thanks

EagleOne

 

[Jeff Boyce]

Take a look at the InStr() function to "find" a character in a string.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"EagleOne@microsoftdiscussiongroups"

 

[EagleOne@microsoftdiscussiongroups]

Jeff, Thanks for the thought ... that said

How do I get Instr() to start the search from the right (to the left) to get
the first instance of "\" from the right? That is the position of the end of
the Path-only from which I could do a Left() or a Mid()?

 

[fredg]

On Thu, 22 May 2008 10:18:00 -0700,

Jeff, Thanks for the thought ... that said

How do I get Instr() to start the search from the right (to the left) to get
the first instance of "\" from the right? That is the position of the end of
the Path-only from which I could do a Left() or a Mid()?

Access 2000 or newer?
Mid("c:\MyFolderName\MyDb.mdb",InStrRev("c:\MyFolderName\MyDb.mdb","\")+1)

returns MyDb.mdb.

 

[EagleOne@microsoftdiscussiongroups]

I cannot believe I did not see InsStrRev() in the "members of Strings"
blindness is mine.

 

[EagleOne@microsoftdiscussiongroups]

Here is the code that I ended up using in ACCESS VBA to sense the
Currentdb name and to capture the Input File Name via InputBox then
to execute the process:

Dim myFileName As String
Dim myPath As String
myFileName = InputBox("Enter Filename to be imported", "FILE NAME?")
myPath = Mid(Application.CurrentDb.Name, 1, InStrRev _
(Application.CurrentDb.Name, "\"))
DoCmd.TransferDatabase acImport, "Microsoft Access", _
myPath & myFileName, "YourInputTable", "YourOutputTable", False

Thanks for all the help to all!!

*******************************************************

 

[EagleOne]

Chris,

I totally agree. Thanks

This would have been simpler and easier to maintain:

Dim myFileName As String
myFileName = InputBox("Enter Filename to be imported", _
"FILE NAME?")
DoCmd.TransferDatabase acImport, "Microsoft Access", _
CurrentProject.Path & "\" & myFileName, , "YourInputTable", _
"YourOutputTable", False

3 lines of code, not counting the continuation lines that I put in to make it
fit into this editor.

Chris
Microsoft MVP

Here is the code that I ended up using in ACCESS VBA to sense the
Currentdb name and to capture the Input File Name via InputBox then
to execute the process:

Dim myFileName As String
Dim myPath As String
myFileName = InputBox("Enter Filename to be imported", "FILE NAME?")
myPath = Mid(Application.CurrentDb.Name, 1, InStrRev _
(Application.CurrentDb.Name, "\"))
DoCmd.TransferDatabase acImport, "Microsoft Access", _
myPath & myFileName, "YourInputTable", "YourOutputTable", False

Thanks for all the help to all!!

*******************************************************
[quoted text clipped - 39 lines]

returns MyDb.mdb.

 

[Albert D. Kallal]

Use CurrentProject.Path to get the current db path. No special string
manipulation required.

Chris
Micrsoft MVP

Yup, and currentproject.Name will return the file name withtout the path:

So, we have:

? currentdb.Name
returns full path name + file name
eg: C:\Documents and Settings\Albert\My Documents\Access\Answersxp.mdb

? currentproject.Path
gives
C:\Documents and Settings\Albert\My Documents\Access

Note there is no trailing "\"

And
? currentproject.Name
gives
Answersxp.mdb

So, you can get all 3 values without any string stuff at all now...