microsoft access

Hello EE

I have an Access 2013 application that is split.  In the past I have used a mapped drive letter for my connection to the back-end, but I am switching to using a UNC.  In the past, when the application first opened I would check to see if there was a valid connection to (for example) Drive Y.

Now, I need some other kind of code to to see if I am connected to the UNC correctly.  Additionally, Sometimes the application is taken off site and so  the UNC for off-site will be different than the one that is normally used.  It would be helpful if I could determine in code, at start-up what UNC the application is using and verify that the connection is valid and working.

What code might I write to obtain that information?

Thank you
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
As jeff said, the way to go is by looking at one of the linked tables tabldef connect property, which contains the path to the BE DB whether it's a mapped drive letter or not.

As for the test, it's the same either way; can you open a recordset on one of the linked tables?   If no, most applications prompt the user for the location of the BE DB, then re-link all the tables for that BE.

jkaiosIT DirectorCommented:
The quickest and simpliest way to accomplish that is to look for the two backslashes in the file name which denote a network connection.

In the Load() event of the first form (the switchboard form or the startup form):

Private Sub Form_Load()
   If Left(CurrentDB.Name, 2) = "\\" Then
      msgbox "connected to a server on the network: " &
      msgbox "connected locally: " &
   End If
End Sub
WoodrowAAuthor Commented:
Thank you for your feedback.

As I mentioned, the application is split.  In my front-end I ran the code you gave me but I think it thinks of the current database as the FE not the BE, because it is giving me the path the the FE.

Any thoughts?
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Rey Obrero (Capricorn1)Commented:
create a table in the BE, tblConnection with a fields BEPath

in the field BEPath, place the location of the BE, \\ServerShare\FolderName\NameOfBackend.accdb

in the FE create a linked to the table "tblConnection "

in the open event of your start up form, place this codes

if dir(dlookup("BEPath", "tblConnection"))<>"" then
   'were good
   msgbox "Cannot locate the BE"
end if
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
<No Points wanted>
Then try it like this:
Private Sub Form_Load()
 If Mid(CurrentDb.TableDefs("Title").Connect, 11, 2) = "\\" Then
      MsgBox "connected to a server on the network: " & CurrentDb.TableDefs("Title").Connect
      MsgBox "connected locally: " &
   End If
End sub

This is the code I use to verify a good connection.  I use this method because several of my apps can switch between SQL Server and ACE so I can't just check the path to the BE.  Change the code to use one of your app's required tables.  This code is in the Open event of an unbound form and is the first form opened by the app.  If the link is valid, the login form is opened, otherwise the relink form is opened.

Private Sub Form_Open(Cancel As Integer)

   On Error GoTo Form_Open_Error
    If DCount("*", "tblClients") > 0 Then
        DoCmd.OpenForm "frmLogin"
    End If

    DoCmd.Close acForm,
   Exit Sub

    Select Case Err.Number
        Case 3024, 3044, 3043
            MsgBox "Tables have moved.  Please relink.", vbOKOnly
            DoCmd.OpenForm "frmReLinkJetOrACETables", , , , , ,
            Resume Form_Open_Exit
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Open of VBA Document Form_ChrckLinks"
    End Select
End Sub

Open in new window

WoodrowAAuthor Commented:
Thank you.  The combination of Jim Dettman and Jeffery Couchmans remarks did it for me.

Thank you very much
WoodrowAAuthor Commented:
The combination of these remarks and those of Jeff Dettman got me my soltuion.
All Courses

From novice to tech pro — start learning today.