Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

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
0
WoodrowA
Asked:
WoodrowA
2 Solutions
 
jkaiosCommented:
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: " & Currentdb.name
   Else
      msgbox "connected locally: " & Currentdb.name
   End If
End Sub
0
 
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?
0
 
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
   else
   msgbox "Cannot locate the BE"
   docmd.quit
end if
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Jeffrey CoachmanCommented:
<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
   Else
      MsgBox "connected locally: " & CurrentDb.name
   End If
End sub

JeffCoachman
0
 
Jim Dettman (Microsoft MVP/ EE MVE)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.

Jim.
0
 
PatHartmanCommented:
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
    

Form_Open_Exit:
    DoCmd.Close acForm, Me.name
   Exit Sub

Form_Open_Error:
    Select Case Err.Number
        Case 3024, 3044, 3043
            MsgBox "Tables have moved.  Please relink.", vbOKOnly
            DoCmd.OpenForm "frmReLinkJetOrACETables", , , , , , Me.name
            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

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

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

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now