Get UNC Path of Current Database

[Joe4]

I volunteer at an organization that has a situation much like this one here: http://www.accessmonster.com/Uwe/Fo...rrentProject-Path-and-CurrentProject-FullName

Basically, they have a network of three PCs hooked up to each other, with Access databases on each one that they all link to (I tried to sell them on the idea of splitting the databases and having separate front ends, but they did not like that as they all want to be able to add queries that everyone can acces). The problem is that occassionally, somehow a database will be copied, and users will be updating different copies of the database (instead of updating the same one). I thought a way to help prevent that is institute code like in the link above, where upon opening it verifies the location and name of the database.

However, just like one of the replies in that link states, I am finding that if the database resides on the computer that is opening it, it returns a local file path. However, if a computer opens a database found on some other computer of the network, it returns a UNC file path. In order to get the code to work properly every single time, I would need it to return the UNC file path in every instance.

One of the replies in the link contains this link (http://www.mvps.org/access/api/api0003.htm) to some code that is supposed to be able to return the UNC file path of any mapped drive. However, I cannot get it to work for me, as it returns compiler errors with the "Private Declare Function" statements. It appears that this is because I am using Microsoft Access 2010 on a 64 bit system of Windows 7.

Does anyone know how I can get the UNC file path of a database (or mapped drive) using Access 2000 on a 64 bit system?

Or does anyone have any other clever ideas to help prevent users from working in "accidental" copies of a database?

Thanks

 

[shg]

No idea if this works on a 64-bit OS:

[FONT=Consolas][COLOR=#595959]Function Path2UNC(sFullName As String) As String[/COLOR][/FONT]
[COLOR=#595959][FONT=Consolas]   ' Converts the mapped drive path in sFullName to a UNC path if one exists.[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]   ' If not, returns a null string[/FONT][/COLOR]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[COLOR=#595959][FONT=Consolas]   Dim sDrive      As String[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]   Dim i           As Long[/FONT][/COLOR]
<o:p></o:p>
[COLOR=#595959][FONT=Consolas]   sDrive = UCase(Left(sFullName, 2))[/FONT][/COLOR]
<o:p></o:p>
[COLOR=#595959][FONT=Consolas]   With CreateObject("WScript.Network").EnumNetworkDrives[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]       For i = 0 To .Count - 1 Step 2[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           If .Item(i) = sDrive Then[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]               Path2UNC = .Item(i + 1) & Mid(sFullName, 3)[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]               Exit For[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]           End If[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]       Next[/FONT][/COLOR]
[COLOR=#595959][FONT=Consolas]   End With[/FONT][/COLOR]
[FONT=Consolas][COLOR=#595959]End Function[/COLOR][/FONT]

 

[SydneyGeek]

If you browse to the database using the Network icon, rather than My Computer, you will get the UNC path.

EDIT: That of course relies on the users doing it every time...
shg's code looks like an elegant solution.

Denis

 

[Joe4]

Thanks for the replies!

shg,
That looks like it might be what I am looking for. I will try it out the next time I in that volunteer office (it may be in two weeks, so forgive me if there is a long delay in my reply).

Denis,
I might not have been totally clear what I am after. I know how to get the UNC path through Windows Explorer or My Computer. What I am looking for is to get it via VBA code so I can use it for database verification.

For example, let's say my database is stored under: \\server01\databases\mydb.accdb
I want to create a macro or VBA procedure that runs upon opening the database (ie. through an AutoExec macro) that checks to see if the full path and name of the database is: \\server01\databases\mydb.accdb
If it is anything different, it will close down, or at least pop up some warning messages that they are working in an apparent copy of the database, and not the original database.

If shg4421's code works, I should be able to do just that.

 

[SydneyGeek]

I realised that I had misunderstood, once I saw shg4421's response.

You could potentially go one step further with the link check.

1. Keep a constant string of the correct UNC path
2. Check that they are connected to a UNC path, and that it's the correct one
3. If not, assemble the correct connection string for each table and refresh the link
... grab everything up to and including DATABASE=
... insert the UNC path
... append the correct backend database name
... use the RefreshLink method

Denis