Solved

microsoft access

Posted on 2015-02-03
9
180 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
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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 57

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 35

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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