Solved

microsoft access

Posted on 2015-02-03
9
179 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 119

Expert Comment

by:Rey Obrero
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
 
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 34

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

Suggested Solutions

Title # Comments Views Activity
Access Runtime 2010 Error 17 29
highlight duplicate entry 16 29
Format Meeting Request through VBA 5 12
append to an ms access field 6 21
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

912 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now