Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Red face Unanswered: Access/MySQL back-end -- how to disable DataSource config screen

    Hi,

    I've just created an Access interface with a MySQL back-end. I use System DNS to connect the two.

    Now, if the server holding the back-end is running, I'm fine. But when the server is down, for whatever reasons, clients accessing the front-end will get the Data Source configuration dialog box. Is it possible for me to stop that from happening, instead somehow use VBA to redirect and just close the DB. I don't want anyone snooping around anything back-end.

    What I have in mind is:
    When DB interface loads and server cannot be contacted:
    msgbox "Sorry, the server cannot be contacted at the moment."
    docmd.closedatabase
    The part that I'm stuck is where to call this procedure. Any suggestion?
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Have a splash screen / start up form which queries one of the remote tables. Use error trapping and tske appropriate action
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    32
    I do have a startup form, in which I do a query to check the DB version on the server. Trouble is, the moment I do a query on any remote table, this ugly screen comes up:

    https://www.dropbox.com/s/o58ubzdile...SQL%20ODBC.png

    Is there some sort of code whereby instead of querying for data in a remote table, I directly query for the connection status and get a boolean result? I tried

    Code:
    MsgBox CurrentDb.TableDefs("remote_table").Connect
    but appears that it gives me only the connection string, not status.
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    are you using error trapping?
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2009
    Posts
    32
    Not familiar with the term "error trapping", is it the same as error handling or exception handling?

    I set the DB to load the form named "Dummy" when it starts, and this is how my code looks like:

    Code:
    Sub Form_Load()
    On Error GoTo doError
    
      DoCmd. RunCommand acCmdWindowHide
      DoCmd.Close acForm, Me.Name, acSaveNo
    
    ''check for latest version
      If Not DLookup("version_no", "version_client") = DLast("version_no", "version_server") Then
        MsgBox "Your client version is outdated. Please contact DB administrator to update.", vbOKOnly, ""
        DoCmd.CloseDatabase
      End If
    
    ''open Login form in dialog mode
      DoCmd.openForm "Login", acNormal, , , acFormEdit, acDialog
    
    doExit:
      Exit Sub
    
    doError:
      MsgBox Err.Number & ": " & Err.Description & Chr(10) & Chr(10) & _
             "DB needs to close.", vbOKOnly, ""
      DoCmd.CloseDatabase
    
    End Sub
    The caveat comes in at Line 8 when I do a DLookUp on "version_server", which as its name suggests resides on server side. If it comes up as a VBA error per se, I could handle that. But it doesn't, instead the Data Source window pops up. (Only if I choose Cancel or enter something wrong, then Error 3151 is called. This is later than what I'd like.)

    Any idea how to circumvent this?
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    off hand no
    you could try doing a ping on the server
    https://www.google.co.uk/#hl=en&scli...w=1920&bih=995

    if you run the code as an MDE (ie no design changes allowed) does that do as expected or does it still display the system DNS
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jul 2009
    Posts
    32
    I deployed client versions as ACCDE, and yes the dang DNS still comes up. I'm actually curious why it does.

    The suggestion for ping seems fair enough, I think I will go deeper on that route. Thanks for the tip, will update if it works (or not). *fingers crossed*


    **EDIT**

    It works! After some digging and trying to find simplified ways to do it, this post here gave me the short answer in one package. Awesome! Thanks healdem, you're a lifesaver =)
    Last edited by andi_kan; 05-08-13 at 08:27. Reason: Solution found; update the community
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •