Old path in CurrentProject.Path

[perlfan]

Hi there,

I have a strange problem with CurrentProject.Path - I use it to get the path of a another configuration file that is located in the same folder where my accde is located. When somebody else opens the accde on another machine, CurrentProject.Path does not give the new current directory path but the old path of my machine. So somehow the information is stored and not refreshed when executed on another machine. Does somebody know this behaviour and could propose a solution?

Thanks for help! Frank

This is the code:

Public Function StartFunction()

Dim strSQL, Database_filepath As String
Dim var As Double
Dim sales_month As Double
Dim sTemp As String
Dim sValue As String


Dim tdf As TableDef

Dim Conn  As ADODB.Connection
 Dim db As Database, i As Integer
 Dim myTable As TableDef
 Dim RSTSchema As New ADODB.Recordset
 Dim cat As New ADOX.Catalog
 Dim Constr As String
  Set Conn = New ADODB.Connection
  Set db = CurrentDb

DoCmd.SetWarnings False
'myFolder = Left(CurrentDb.name, InStrRev(CurrentDb.name, "\"))
Database_filepath = CurrentProject.Path & "\" & "configuration_tm.accdb"
do_link_tables = link_tables_configuration(Database_filepath)
...

 

[MarkK]

Are you opening the same file over a network from different workstations? If so, that is not recommended, and may be the cause of the problem. In a multi-user environment it is strongly recommended that you "split" the database such that each user runs a front-end (FE) file on his own workstation. That FE file should then connect to the back-end (BE) data file via linked tables.
hth
Mark

 

[ashleedawg]

Hi there,When somebody else opens the accde on another machine, CurrentProject.Path does not give the new current directory path but the old path of my machine.

'myFolder = Left(CurrentDb.name, InStrRev(CurrentDb.name, "\"))
Database_filepath = CurrentProject.Path & "\" & "configuration_tm.accdb"
do_link_tables = link_tables_configuration(Database_filepath)

The database can't be lying. The path it's returning for for any of these should be the same, and is exactly where the .accdb file is sitting:

?CurrentProject.Path 
?Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
?CurrentProject.AccessConnection

Paste those lines into the [CTRL+G] Immediate Window one a time to double-check that they are all returning the same path (especially the last one!)...

Perhaps this implies that a copy (or copies?) were updated incorrectly at one point, and have not been kept up to date since then? Or, maybe the user decided to be helpful and reorganize?

 

[The_Doc_Man]

The question is whether this path is based on drive-letter pathing or UNC pathing (which is of the form \\nodename\device-or-share-name\folder1\folder2...\file). If it uses drive-letter mapping then all systems must map the same drive letter in the same way. You can't have a case where on workstation 1 you use drive T but on workstation 2 you used drive S for the same disk. If the database is on the disk in question, inconsistent pathing starts at the drive letter, not the driver's root folder.

 

[perlfan]

FE and BE are split - it's not a multi-user app, I simply distribute the accde to other users that open it on their machine. configuration_tm.accdb contains information to establish the connection to the database (the location of the accdb or mysql access information). The problem has already occured with other users, but I did never really get to the problem. When they open the accde they will get the message that the file cannot be found in Y:\...demo\.... which is a very specific path where my files are located (when I move my accde to another folder on my PC and remove the configuration_tm.accdb in that specific folder as a test, I also get the error message, so CurrentProject.Path is really not updating). IMO Access really seems to be lying....

 

[MarkK]

Is Y:\ a mapped drive? I would suspect that there is a problem with the mapping of that Y:\ drive, and whether it has been refreshed or not, before I would suspect CurrentProject.Path. But then the veracity of CurrentProject.Path is very easy to test too...

msgbox CurrentProject.Path

 

[perlfan]

Yes, Y: is a mapped drive. On my machine the app is on a mapped drive, and then on the other users' machine it's probably in c:\program files\.... Then on their machine they get the error "The file is not found in Y:\...." even though CurrentProject.Path should indicate the current location of the accde on the respective user's PC. I hope you get my point. So CurrentProject.Path is not working consistently - I use CurrentProject.Path also for other files but there has never been an error. This is why I suspect that the variable Database_filepath is not updated and causes the error as the variable is the only difference.

 

[The_Doc_Man]

The question I see immediately is that if the app is on your Y drive but their C drive then you need to determine the location of the BE because nobody CARES about the FE location (not even Access).

On a split database, you can look at the properties of any table in the BE to see where it is pointing. If the BE is an Access database, what you get will be the full path of the database file where the table is located.

x = Tables("tablename").Connect

This should give you a string containing (among other things) drive:\path\filename, which you can parse to remove the filename and get the pathing information.

See also: https://msdn.microsoft.com/en-us/library/bb243197(v=office.12).aspx

CurrentProject.Path is going to be the file path from which the FE file was opened. If it was opened from the user's machine, it should point to the user's machine. If it was opened from the Y drive, it should point to the Y drive. That is because remember that the code runs on the user's workstation, not the shared machine. So the CODE that is testing these variables is in whatever environment applies to the individual user. And it is only that user's environment that "knows" from where the FE file was opened.

 

[Cronk]

This will give the backend path

left(currentdb.TableDefs("YourLinkedTableName").connect,instrrev(currentdb.TableDefs("YourLinkedTableName").connect,"\"))

Also something I came across this the other day

Split(Split(CurrentDb.TableDefs("YourLinkedTableName").Connect, "Database=")(1), ";")(0)

 

[perlfan]

I have the feeling that I didn't explain the problem well - configuration_tm.accdb is in the same directory as the FE accde file and contains the path of the BE file. So I use CurrentProject.Path to open configuration_tm.accdb in order to open the BE file. However CurrentProject.Path sometimes gives a stored current path of my machine when the FE is executed on other people's PCs.

 

[MarkK]

Where is the BE? If there are links in the FE to tables in the BE, the FE will try to connect to them. It seems more likely to me that this is what is raising your error, and not the fact the CurrentProject.Path is failing. If you are going to programmatically create links to BE tables on start up, I think you have to make sure those same links are deleted on shut down, otherwise the first thing the FE is going to do is try re-establish the links to the BE.

So where is the BE when you create your accde, and why wouldn't that accde, on startup on a different machine, not try and link to the BE where it was when you created the accde? See what I'm saying?

I really doubt that CurrentProject.Path is failing. I think your db is looking for BE tables where it last found them BEFORE your re-link code runs. I don't know this, but this is my guess.

Hope it helps,
Mark

 

[MarkK]

But as I've said before, you can test CurrentProject.Path. Test it. Put a button somewhere in your next FE version, and run code like...

private sub cmdTextCurrentProjectPath_Click()
   msgbox "CurrentProject.Path = " & CurrentProject.Path
end sub

...so with a very simply test you can refute or confirm your suspicion that CurrentProject.Path is, or is not, the problem. I think it is something else...

hth
Mark

 

[perlfan]

I did test CurrentProject.Path in the Immediate Window, however it indicated always the correct path on my machine.

Exactly, it seems to re-establish the last connection even though I tell the FE to use the user specific path to the accdb database file:

Database_filepath_config = CurrentProject.Path & "\" & "configuration_tm.accdb"
do_link_tables = link_tables_configuration(Database_filepath_config)

 

[MarkK]

But doesn't the FE try to connect to the BE before your code runs??? I suspect that if you want to have a fully dynamic mechanism to link to the BE on startup, you may need to have a similar mechanism to unlink the BE at shutdown. I'm not certain about that, but if I was dealing with the problem you describe, that's where I would start testing.

Also, a much more common setup is to locate the BE in a public path that is the same for everyone. That, or locate the BE on a mapped drive, and then you can more easily switch the BE my simply mapping the drive to a different path.

hth
Mark

 

[Cronk]

Mark

But doesn't the FE try to connect to the BE before your code runs???

There is no connection to the BE until a BE table is read.

I always confirm the back end is linked, by catching an error if a table cannot be accessed.

As far as the OP and the Y drive, I wonder what the purpose of the configuration_tm.accdb file in each users' FE folder. Are there different mappings for individual users?

I ask this because I've been at work sites where typically a group of users accesses say a G drive as their network drive, but there is someone who joins the section and needs to maintain another G drive mapping from their former work area. For them, the BE folder might be on X drive. A static separate config file in the FE folder would hold the BE mapping for each individual user.

 

[The_Doc_Man]

Normally, the solution to this problem is UNC mapping. I'm wondering if there is some overthinking going on here?

FE and BE are split - it's not a multi-user app, I simply distribute the accde to other users that open it on their machine. configuration_tm.accdb contains information to establish the connection to the database (the location of the accdb or mysql access information)

Point of clarity. Your statement is self-contradictory. It has multiple .ACCDE files on other user machines that eventually share a dynamic connection to the back end based on your configuration file. That IS data sharing. Can't sugar-coat it.

According to MSDN Library references, CurrentProject has some properties that might be relevant. Your original code snippet refers to the .Path property. According to

https://msdn.microsoft.com/en-us/library/aa173192(v=office.11).aspx

the .Path property is read-only, so you cannot define something that would make this wrong. That is, the path you get back from CurrentProject.Path is the path that Access sees when it follows Application-->Current Project--> .Path in the COM model.

I'm not seeing the cause of your symptom but dollars to donuts says you aren't telling us something that we need to know to understand / fix your problem. I don't even know what question to ask.

If everybody has a local copy of the .ACCDE file (as opposed to a local shortcut that points to the .ACCDE file on your machine), you should not see the behavior you are reporting. Now, if you had your machine wide open and just gave folks network links to the files on your machine, your described behavior might be expected.

 

[perlfan]

The configuration_tm.accdb also stores information about if the user wants to connect to a local accdb or a mysql database. Then, in case the accde is updated/replaced by a newer one, the access data to the accdb or mysql db are kept and do not have to be entered again and again. Sorry for the confusion, it's just that I noticed some behaviour of CurrentProject.Path that was inconsistent IMO. It's not always inconsistent- on most of the user's PC the application runs just fine.