VBA code to kick user out if opening on the shared network (1 Viewer)

[nbowman56]

Hi all,

I inherited quite a few databases at my new job, and my predecessor had set up some VBA on a couple of databases so that if the user tries to open the Access file from the shared server, it will force quit on them. However, he didn't leave any descriptive notes in his code so I'm not 100% sure that this is all the code I need to do this:

Option Compare Database
Option Explicit

Sub ValidateCDrive()
If Not CurrentDb.Name Like "C:\*" Then
MsgBox ("The file must be located on your C:\ drive.")
DoCmd.Quit
End If
End Sub

***Then in another module that opens a particular form, it does the following code upon load***

Private Sub Form_Load()
Me.TimerInterval = Nz(DLookup("TimerInterval", "tblForms", "FormName = '" & Me.Name & "'"), 0) * 1000
Debug.Print Me.TimerInterval
ValidateCDrive
InitializeUser
ResetForm
End Sub

****

I think where I'm a little confused is why this subroutine is called on a particular form's code, when this validation code runs wright when you open the database. The form module that it is called from does not open automatically when opening the database.
Additionally, I am wondering what the translation of the Me.TimerInterval is so if anybody could tell me what that does exactly, that would be awesome!

Thanks
Nicole

 

[theDBguy]

Hi Nicole. Do you know if ValidateCDrive() is called anywhere else in the entire database? If not, then my guess is this form is probably the first form that opens when the user runs the database. Is that correct?

Me.TimerInterval contains the value of the Timer Interval property of the form. That code is looking up the value to assign to the property from a table.

 

[nbowman56]

Hello! ValidateCDrive() is only called the one time on the one form. That form is actually not the first form that would open, so that's why I'm confused as to why it would work.
As for the TimerInterval - do you know why you would want to use this?

 

[theDBguy]

Hello! ValidateCDrive() is only called the one time on the one form. That form is actually not the first form that would open, so that's why I'm confused as to why it would work.
As for the TimerInterval - do you know why you would want to use this?

You would use the TimerInterval property in conjunction with the Timer Event. For example, I imagine that form counts down a certain number of seconds and then performs a task. If you don't clear the TimerInterval, then that cycles repeat until the form closes.

 

[Micron]

That form is one that users need to open to do anything?
I don't see the point in repeatedly checking if a db was opened from any particular place. It's not like it can be C drive one second and some other drive the next second. I don't suppose you want to re-write this everywhere it's used, but it might be very low level protection. F'rinstance, can this be circumvented by opening with shift bypass? Or maybe if I enter a huge value in the table so that it won't check for hours?

My approach might be to hard code this check (because code can't be viewed in an accde), do it only once and force a close if the shift bypass was used (but allow me to use a back door).

 

[The_Doc_Man]

Your "ValidateCDrive" code is correct for protecting from using the wrong copy of the FE file. However, to make it work it must be launched as soon as the DB is opened. This means that there should be an "Opening Form" for this DB. Note that it cannot be run from a macro because the only thing that works for a RunCode macro step is to call a FUNCTION and what you have is a SUB.

Unfortunately, I had to use a different method because I worked with the Navy. We had a C drive and a D drive on our PCs and of course our private files all went to D. But the solution is, let's say you have your master copy of the FE in the same folder as the BE file. You tell your users to copy the FE file to their local PCs, you don't care where.

So look at the BE file's connect string from any linked table in the FE. You might first want to look at it manually just to satisfy yourself how it will appear. This article summarizes what you can expect to see.

TableDef.Connect property (DAO)

Office developer client VBA reference documentation

docs.microsoft.com

For the simple linked table case, you can use InStrRev twice to find the device and path of the file minus the file name. Once on the .Connect string for any table that is linked from the back end, and once from CurrentDB.Name (which is the FE name). Then if they are the same, kick them out. This would work no matter what kind of machine your user is using.

If you aren't familiar with InStrRev, look at this:

InStrRev function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[Micron]

I think one can make this simpler just by checking the CurrentProject.Path property in code on startup. Not correct? Close db unless the user has admin rights.