Test to see if be is connected

[speakers_86]

I found this code that checks if the backend is connected, but it is not working for me.

Private Sub Form_Open(Cancel As Integer)

' Check links to the Back End Database; returns True if links are OK.

Dim dbs As DAO.Database, rst As DAO.Recordset

Set dbs = CurrentDb

' Open linked table to see if connection information is correct.
On Error Resume Next
Set rst = dbs.OpenRecordset("lstcustomertitle")

' If there's no error, return True.
If Err = 0 Then
Me.Label16.Visible = False
Me.Label16.Width = 0.001
Me.Label16.Height = 0.001
Else
Me.Label16.Visible = True
Me.Label16.Width = 1
Me.Label16.Height = 0.25
End If
End Sub

If the backend is connected, I want label16 to not be visible. If the backend is not connected, label16 should be visible. Label16 contains refresh links code on click.

lstCustomerTitle is a table that should always have fields in it (Mr., Mrs., etc.).


edit- perhaps I should say what is not working with it! Regardless of the backend, the label is never visible. The if statement always returns err=0 as true.

 

[wiklendt]

why not have the DB connect on open? then you don't have to worry about whether they're connected or not.

http://www.access-programmers.co.uk/forums/showthread.php?t=168034&highlight=backend

 

[speakers_86]

If the db is connected as it should be, then I do not want label16 shown. Otherwise, label16 has to be shown, so that the end user can search for the new location of the be. In the on click event of label16, I placed refreshlinks code.

 

[wiklendt]

why would the location of the be change? i would expect a be would have a 'permanent' location (whether it be on a server or locally).

in any case, here is a sample db (accdb) by ChrisO i downloaded some time ago. perhaps you can adapt it to suit your case (specifically thinking of module mdlFileOpen)?

edit: actually, my be 'changes' depending on whether i'm opening it to maintain the FE or whether it's used in a "real-life" scenario - that is, when i'm developing it at home, i don't have access to the server, so my BE is local. in this case, when i open the DB it tests for which BE is available and links to it.

here is the code i'm using for that (which i adapted from.... uhmmmmm.... namliam, i think? i really should comment more of my references in my code):

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
    Dim tbl As TableDef
    Dim x As Long, MaxX As Long
    Dim tblDB As String
    Me.Visible = False
        
    tblDB = myFolder & "\ORDERS_ReferenceLabs_be.mdb"
    
    If Dir(tblDB) = "" Then ' local back end not found, use server BE.
        tblDB = "\\wm-icpmr\Data2\SHARED\Cidmls\Molecular Biology\Orders\ORDERS_ReferenceLabs_be.mdb"
    End If
                     
    MaxX = 1 ' first count all attached tables
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then MaxX = MaxX + 1
    Next tbl
    x = 1 ' Now update them
    For Each tbl In CurrentDb.TableDefs()
        If tbl.Attributes = dbAttachedTable Then
'            tblDB = myFolder & Mid(tbl.Connect, InStr(1, tbl.Connect, "\"))
            If tbl.Connect <> ";Database=" & tblDB Then
                Me.Visible = True
                Me.Repaint
                tbl.Connect = ";Database=" & tblDB
                tbl.RefreshLink
            End If
            x = x + 1
        End If
        Me.Fill.Width = x / MaxX * Me.FillTo.Width
    Next tbl
    If Me.Visible Then
        Me.lblWait.Caption = "Done... Opening login form."
        Me.Repaint
        MaxX = Timer + 2
        Do While Timer <= MaxX
        Loop
    End If
    
    DoCmd.OpenForm "frmLogin"
    DoCmd.Close acForm, Me.Name
    
End Sub
Function myFolder()
    myFolder = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
End Function

 

[speakers_86]

I wouldn't expect it to change, but stuff happens. Also, it makes the first launch loads easier.

 

[wiklendt]

could you put the BE in a hidden folder? then those 'curious' users won't accidentally delete or move it?

i read this morning you can make a folder hidden by adding a dollar sign to the end of it...

 

[speakers_86]

Or you could right click on the folder, and somewhere in properties is an option to make the folder hidden.


I looked at your attachment, and did find this

If (conHandleErrors) Then

though it gave an error when I tried to use that if statement. I looked through all of the modules to see if that was a global variable, but saw no other reference to conHandleErrors.

 

[speakers_86]

If I can't test if the be is connected, perhaps I can just set a one minute timer, and make the label visible after one minute. That should be fine, because, when someone opens up the db, they will login right away most of the time.

 

[speakers_86]

That doesn't seem to work either. On form open, I set me.timerinterval=60000. On timer event I set label16=true and set the height and width. Why doesn't that work?

 

[wiklendt]

there is this in the module mdlCommonAPIandConstants:

'   Public constant to switch on/off error handling in all Procedures.
'   Useful for raw code testing during development... just let it crash.
'   Don't forget to switch it back on after testing.
Public Const conHandleErrors       As Boolean = True

 

[wiklendt]

That doesn't seem to work either. On form open, I set me.timerinterval=60000. On timer event I set label16=true and set the height and width. Why doesn't that work?

i don't know. can you upload your DB?

also, i'm not sure how well the code you posted in your original post would actually check for a BE connection... and rather than "On Error Resume Next", perhaps add some error handling there. or a Debug.print in case of connected be and see if it returns an expected result?

 

[speakers_86]

I can't upload my db.

Any other ideas?

 

[wiklendt]

did you get the code from my second post to work? (not form the attachment, but from the code itself that was posted?)

i just uploaded my own database (for another reason) here. on open, it loads the form "frmWait", which has code to connect the backend. it first looks for a server location, if it doesn't find that, then it looks for a be in the same folder as the fe.

you could theoretically add several locations to the code. i have added the be in the zip on that post, so you can see it in action.

let me know how that goes for you (or if it's even suitable)

 

[speakers_86]

I got the timer to work. Not quite sure what I was doing wrong. After waiting for one minute, the link appears that refreshes links on click. It uses a prompt that asks the user for the location of the be. Works like a champ.

 

[wiklendt]

great. glad you got it sorted. good luck with the rest of your project