Solved

microsoft access

Posted on 2015-02-03
9
187 Views
Last Modified: 2016-02-11
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
Comment
Question by:WoodrowA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 12

Expert Comment

by:jkaios
ID: 40587791
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
 

Author Comment

by:WoodrowA
ID: 40587802
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40587847
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 40587872
<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
 
LVL 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 40588360
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 40588810
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
 

Author Comment

by:WoodrowA
ID: 40589250
Thank you.  The combination of Jim Dettman and Jeffery Couchmans remarks did it for me.

Thank you very much
0
 

Author Comment

by:WoodrowA
ID: 40589256
The combination of these remarks and those of Jeff Dettman got me my soltuion.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month6 days, 19 hours left to enroll

623 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