Solved

Code to allow MS Access database to determine whether it's being run from a network

Posted on 2013-12-18
10
444 Views
Last Modified: 2013-12-18
Is there a catch-all way for an Access database frontend to detect whether it's being opened from a network location (mapped to a drive or not) versus the user's hard drive?

I want to keep the frontends off the network so that multiple users don't use the same copy, and to reduce traffic.

I know about Currentdb.name, and I know what drive letters we use for network locations, but I want something that will work anywhere.
0
Comment
Question by:Jolio81
10 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39726917
you can try something like this

if left(currentdb.name,3)<> "C:\"  then
0
 
LVL 57
ID: 39726925
A great question!

I would think there is a windows API call that would need to be made.  Only the OS will know if it's a local or mapped/unc connection.

Let me see what I can find out...

Jim.
0
 
LVL 57
ID: 39726939
Looks like you can use the GetDriveType() function, or GetDrivePath().

Let me see what I can work up.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39726946
I put all of my current version front ends in a specific folder on my server, something like:

C:\DatabaseApplications\

Then I have some code in the Timer event (TimerInterval = 1000) of my Splash form of my applications which checks to see whether the CurrentProject.Path matches that path.  If so, it displays a message indicating that the application cannot be run from the server and shuts down.

Private Sub Form_Timer()

     If CurrentProject.Path = "C:\Users\dale.fye\Documents\Work\Access" Then
         MsgBox "cannot run from Server, copy application to desktop before running"
         Application.Quit
     End If

End Sub
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39726980
Use the code below and pass a drive letter.

Jim.

Option Compare Database
Option Explicit

Private Declare Function GetDriveType Lib "kernel32" Alias "GetDriveTypeA" (ByVal strDrive As String) As Long


Public Function IsNetworkDrive(strDrive As String) As Boolean

    Select Case GetDriveType(strDrive)
   
    Case 4
        IsNetworkDrive = True
    Case Else
        IsNetworkDrive = False
    End Select
   
End Function
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39726993
My Timer event should have read:

Private Sub Form_Timer()

     If CurrentProject.Path = "C:\DatabaseApplications\" Then
         MsgBox "cannot run from Server, copy application to desktop before running"
         Application.Quit
     End If

End Sub

But I like the GetDriveType function call and may try that.  Wonder if that requires modification for 64 bit systems or Office (x64)?
0
 
LVL 57
ID: 39727007
Note that you need to pass a root spec, ie.  IsNetworkDrive("C:\")

and not just a letter.

Jim.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39727039
Using Jim's IsNetworkDrive function, you could change my example code to:

Private Sub Form_Timer()

     If IsNetworkDrive(CurrentProject.Path) Then
         MsgBox "cannot run from Server, copy application to desktop before running"
         Application.Quit
     End If

End Sub
0
 

Expert Comment

by:Mwvarner
ID: 39727337
A better or at least easier approach may be to force the application to launch from the desktop and not a server location.  I have a batch file that copies my frontend each time the app is launched to the local machine.  This keeps people from running the frontend from the server and when I need to do an update to the front end I only have to change it in one place.

cls
echo ********************************************
echo Update Local Copy
echo This will update your machine with my App
echo from the file server. If this is NOT what
echo you want to do, press CTRL and C now.
echo ********************************************
pause
echo Copying file... Please wait
REM if not exist c:\myapp md c:\my app
copy j:\apps\myapp0\myapp.mdb c:\myapp
0
 

Author Closing Comment

by:Jolio81
ID: 39727643
Great! Thanks Jim!

To add a bit of robustness, I modified your function a bit:

Public Function IsNetworkDrive(strPath As String) As Boolean

    strPath = Left(strPath, 2)
   
    IsNetworkDrive = (GetDriveType(strPath) = 4) Or (strPath = "\\")

End Function
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now