Catch Error - Mapped Network drive

[Snowflake68]

I have a front end application with a linked that is situated on a mapped network drive that uses a VPN to connect to it.

I want to be able to error trap when the mapped drive isn't connected (if the user has forgotten to connect to the VPN).

When opening the application it opens a form that is mapped to one of the tables in the backend database but if the network drive isn't connected produces a message to say that the path is not valid.

I

How do I trap this message so that I can inform the user to connect the VPN without seeing this message?

 

[Gasman]

Try to open that folder, and if an error, do what is needed?

 

[Snowflake68]

Try to open that folder, and if an error, do what is needed?

How do I do that please?

 

[Gasman]

I would probably use the FileSystemObject.

Sub TestFSO()
Dim fso As FileSystemObject
Dim F As Folder

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists("F:\temp222\") Then 'taxi red runner.mp4")
    Debug.Print "Folder Exists"
Else
    Debug.Print "Folder does not exist"
End If
Set fso = Nothing
End Sub

 

[cheekybuddha]

Use your form's Open event, eg:

Private Sub Form_open(Cancel As Integer)

  Const VPN_PATH As String = "Y:/*"
 
  Cancel = (Len(Dir(VPN_PATH, vbDirectory)) = 0)
  If Cancel Then
    MsgBox "You forgot to connect to the VPN! The database will now close"
    Application.Quit
  End If

End Sub

 

[Snowflake68]

I would probably use the FileSystemObject.

Sub TestFSO()
Dim fso As FileSystemObject
Dim F As Folder

Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists("F:\temp222\") Then 'taxi red runner.mp4")
    Debug.Print "Folder Exists"
Else
    Debug.Print "Folder does not exist"
End If
Set fso = Nothing
End Sub

Im just testing this on a blank form but I am getting the following error message. Are you able to advise me further please?

 

[Snowflake68]

Const VPN_PATH As String = "Y:/*" Cancel = (Len(Dir(VPN_PATH, vbDirectory)) = 0) If Cancel Then MsgBox "You forgot to connect to the VPN! The database will now close" Application.Quit End If

Ive tried your method but am getting this error message

It points at the highlighted line below. Are you able to assist me any further please?

 

[cheekybuddha]

Just try: Const VPN_PATH As String = "Y:/*" (as written) instead

 

[Snowflake68]

"Y:/*"

I still receive the same error message ??

 

[cheekybuddha]

Hmmm... then you might need some code to check whether a drive is mapped or not.

Here is an example of a function to do that, but you will need to update the API declaration for 64bit if you are using 64bit Access.

 

[Gasman]

Change FileSystemObject to just Object and make it late bound.
Or for early bound, see https://wellsr.com/vba/2018/excel/introduction-to-the-vba-filesystemobject/

 

[The_Doc_Man]

OR change FileSystemObject in the highlighted DIM statement in post #6 to become Scripting.FileSystemObject, because to use FSO you need to have a reference to the Scripting library. See also Gasman's linked reference in post #11, which shows what box to check in the VBA page >> Tools >> References dialog.

 

[Snowflake68]

Thanks everyone for your suggestions. I think I am actually trying to achieve the wrong thing here and my original request for help was incorrect.

What I actually need to do is error trap to see if a linked table opens successfully.
The reason for this is because I send the user the front end application every time I do any development on it. But the link tables to the backend need relinking again.
I was hoping to pop up the linked table manager for them to relink the tables themselves but I only want to pop the table manager if the tables wont open.

If there a way of trapping an error when a linked table wont open?

 

[Gasman]

Why do they need relinking?
I just used to relink to the live BE, and then create the accde.
The users automatically got the new version when they next opened their FE.

You are linking every time a user opens the DB for the first time, when you only need to do it once.
Whilst this can be done, you are making it way more complicated than it needs to be.

 

[Snowflake68]

Why do they need relinking?
I just used to relink to the live BE, and then create the accde.
The users automatically got the new version when they next opened their FE.

You are linking every time a user opens the DB for the first time, when you only need to do it once.
Whilst this can be done, you are making it way more complicated than it needs to be.

Its because, sometimes I make changes to the design of the tables, new column, or data type change and it doesn't recognise it until I refresh the links

 

[Gasman]

If the links are set correctly in your Master DB, then the users will get the same.
However they will need the same mapping, which should be the case in a corporate environment.

 

[Snowflake68]

If the links are set correctly in your Master DB, then the users will get the same.
However they will need the same mapping, which should be the case in a corporate environment.

Thats the trouble the user is not in my network, they are outside and use VPN to connect to it.
I am going to rethink this whole area now as it seems that I will just have to jump on their PC to refresh the linked tables each time I make a change to the front end (or the backend)

 

[The_Doc_Man]

The alternative is to use URS mapping rather than drive-letter mapping. If they could have drive-letter mapped, then they can URS map. That's because drive-letter mapping stores the URS mapping string in a preset drive-letter equivalence slot and just substitutes it dynamically. Where I'm going with this is that mapping is by strings, not by hidden identifiers. Your drive's host machine name is part of the URS mapping string. The tables are referenced by name.

Another factor is that if you use the kind of automatic front-end replacement/update that is mentioned in many of Pat Hartman's posts, you can just keep that front-end file mapped and ready for download. Here is a pointer to one of Pat's posts:

Can multiple users access one front end of a split database?

Hi everyone, I have split up my database the backend is in a shared folder and the front end has been copied onto each users machine (half a dozen users). It may be necessary for the moment to allow all users to access one shared front end, there are not many users, 6 at the most and not...

www.access-programmers.co.uk

You make the auto-downloader script a batch file and launch IT rather than the specific app. VPNs allow bidirectional traffic, so you can have your update ready and waiting for download.