Catch linked sql table errors?

[ahmed_optom]

My code works fine, so I can connect to my sql server and access the tables stored there.

My issue is this, if for whatever reason (no internet or firewall issue etc), the front end cant connect to the backend, then you get a horrible timeout message, and then next screen is even worse. It exposes the sql server location and user name.

I have tried "catching" the error, with the code below, however, it doesnt work, it just hangs, there is no connection or error message, it just sits there with no activity.

EDITED BY THE_DOC_MAN TO ADD CODE TAGS. (NO OTHER EDITS PERFORMED)

If DCount("Name", "MSysObjects", "Name = 'tablenamexxxxxxx' and Type = 4") <> 0 Then 'The table exist
    DoCmd.DeleteObject acTable, "tablenamexxxxxxx"
End If

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("tablenamexxxxxxx")
tdf.SourceTableName = "tablenamexxxxxxx"

'error handle for connection
On Error GoTo ErrHandler

tdf.Connect = sConnect

   Exit Sub

ErrHandler:

      MsgBox "Connection Error"

dbs.TableDefs.Append tdf
dbs.TableDefs.Refresh

If there a way to catch this error? or to connect to the backend differently to avoid this?

 

[The_Doc_Man]

Some people use a "batch-mode" launcher to activate a Front End file in a way that always copies the "latest, greatest" version of that file from the network location where it is kept. Several posts in this forum show explicit batch scripts for this.

If you used that approach, you could add a couple of commands to the batch file such that you try to PING the server before actually launching. If the ping succeeds, it takes maybe 1/4 of a second, particularly if it is a relatively close server (i.e. on the same intranet or at most a firewall-hop away). But you could trap when it fails and show a message that you control. And you can keep PING silent using the ECHO command and the ">" redirection operator to hide all output.

@echo off
set MyServer=Server.MyDomain.de
%SystemRoot%\system32\ping.exe -n 1 %MyServer% >nul
if errorlevel 1 goto NoServer

... do your launch
exit

:NoServer
... do something else

NOTE: This excerpt was taken from

How to ping a server only once from within a batch file?

I want to learn how to write batch scripts and tried to create a script which automatically runs this command in the command line once: ping www.google.de -t and displays the ping, so it would loo...

stackoverflow.com

This thread shows what Pat Hartman uses for this purpose and would therefore give you an idea of what to modify.

Updating the front end on local server

Hello, I always wanted to know how to update the Fe and Be using a version number. Currently I have the Be in a folder on the local server and I distribute the Fe to each employee. Is there a way when I update the FE each person using the database FE when open it runs a version check and if its...

www.access-programmers.co.uk

 

[Isaac]

I interpreted your need differently, I guess, I thought you have someone working in a database and the connection to the back end goes bad.
Maybe these posts will have some ideas?

Check if ODBC linked table is "reachable"

I need to check (through VBA) if a linked table is online or not. The linked table is attached to a SQL server using an ODBC connection string. If the server is offline or unreachable is what I am using to trigger other code. Anyone have ideas? I have been googling all morning with little...

www.access-programmers.co.uk

ODBC Link to SQL Server - test

Hi Guys My Access App connects to an SQL Server to collect product data that we print 2D matrix barcodes for. In general that all works really well. However, currently if the ODBC link is down for any reason the App Errors out and crashes. I have put trapping code in now to check the Internet...

www.access-programmers.co.uk

If you were talking about first opening the front end, note my comment about what I do nowadays in post 2. (Similar to Pat's method, except VBScript gives a lot more power and options than a batch file).

 

[ahmed_optom]

Some people use a "batch-mode" launcher to activate a Front End file in a way that always copies the "latest, greatest" version of that file from the network location where it is kept. Several posts in this forum show explicit batch scripts for this.

If you used that approach, you could add a couple of commands to the batch file such that you try to PING the server before actually launching. If the ping succeeds, it takes maybe 1/4 of a second, particularly if it is a relatively close server (i.e. on the same intranet or at most a firewall-hop away). But you could trap when it fails and show a message that you control. And you can keep PING silent using the ECHO command and the ">" redirection operator to hide all output.

@echo off
set MyServer=Server.MyDomain.de
%SystemRoot%\system32\ping.exe -n 1 %MyServer% >nul
if errorlevel 1 goto NoServer

... do your launch
exit

:NoServer
... do something else

NOTE: This excerpt was taken from

How to ping a server only once from within a batch file?

I want to learn how to write batch scripts and tried to create a script which automatically runs this command in the command line once: ping www.google.de -t and displays the ping, so it would loo...

stackoverflow.com

This thread shows what Pat Hartman uses for this purpose and would therefore give you an idea of what to modify.

Updating the front end on local server

Hello, I always wanted to know how to update the Fe and Be using a version number. Currently I have the Be in a folder on the local server and I distribute the Fe to each employee. Is there a way when I update the FE each person using the database FE when open it runs a version check and if its...

www.access-programmers.co.uk

Thanks,

Thats a different angle, I already have a vb.net updater, I could just add your ping check to that, and use it as a launcher instead of having a shortcut to the front end. It would then act as an updater, server status and launcher.

Its a shame so much effort is needed just make things "right". I mean who at Microsoft thought this kind of prompt would be a good idea?

 

[ahmed_optom]

I interpreted your need differently, I guess, I thought you have someone working in a database and the connection to the back end goes bad.
Maybe these posts will have some ideas?

Check if ODBC linked table is "reachable"

I need to check (through VBA) if a linked table is online or not. The linked table is attached to a SQL server using an ODBC connection string. If the server is offline or unreachable is what I am using to trigger other code. Anyone have ideas? I have been googling all morning with little...

www.access-programmers.co.uk

ODBC Link to SQL Server - test

Hi Guys My Access App connects to an SQL Server to collect product data that we print 2D matrix barcodes for. In general that all works really well. However, currently if the ODBC link is down for any reason the App Errors out and crashes. I have put trapping code in now to check the Internet...

www.access-programmers.co.uk

If you were talking about first opening the front end, note my comment about what I do nowadays in post 2. (Similar to Pat's method, except VBScript gives a lot more power and options than a batch file).

Hi Isaac,

Its not what I need. I need a way to either check the backend is reachable before attempting to connect to it, or suppress/catch the connection error.

My googling has concluded the error prompts are not hide-able, and within access I cant check the server status unless I connect to it (catch 22).

 

[Isaac]

Hi Isaac,

Its not what I need. I need a way to either check the backend is reachable before attempting to connect to it, or suppress/catch the connection error.

My googling has concluded the error prompts are not hide-able, and within access I cant check the server status unless I connect to it (catch 22).

But by back end do you mean SQL Server?
And the question is "when" - at what point in time do you want to check that?

It sounds like your FE is designed in such a way that it "needs" the SQl tables to be reachable when it first loads. If that's the case, and unless you change that, then yes - you'll need something like Doc suggested, where the 'checking' is done before the FE is even opened.
But your FE does not need to be designed like that, necessarily. It is not uncommon to create a FE which is designed NOT to connect to the back end when initially opened, not until certain checks and balances are tested, and then to deliberately open a persistent connection at a designated moment in time.
What I'm suggesting is that it might be easier to simply design something into your existing FE which does this check - quite possibly using Doc's suggestion about pinging - easier and more 'encapsulated' compared to trying to check it prior to the FE even opening.
The more things you try to "check" prior to the FE even opening, the more you'll find yourself maintaining essentially two apps instead of one. I've been down that road with my own VBScripts that open a FE. They started to just get more and more complex and at some tipping point I realized I needed to design more carefully in the FE load, rather than the 'opener' app. Or at least, that was my conclusion/preference.

 

[ahmed_optom]

But by back end do you mean SQL Server?
And the question is "when" - at what point in time do you want to check that?

It sounds like your FE is designed in such a way that it "needs" the SQl tables to be reachable when it first loads. If that's the case, and unless you change that, then yes - you'll need something like Doc suggested, where the 'checking' is done before the FE is even opened.
But your FE does not need to be designed like that, necessarily. It is not uncommon to create a FE which is designed NOT to connect to the back end when initially opened, not until certain checks and balances are tested, and then to deliberately open a persistent connection at a designated moment in time.
What I'm suggesting is that it might be easier to simply design something into your existing FE which does this check - quite possibly using Doc's suggestion about pinging - easier and more 'encapsulated' compared to trying to check it prior to the FE even opening.
The more things you try to "check" prior to the FE even opening, the more you'll find yourself maintaining essentially two apps instead of one. I've been down that road with my own VBScripts that open a FE. They started to just get more and more complex and at some tipping point I realized I needed to design more carefully in the FE load, rather than the 'opener' app. Or at least, that was my conclusion/preference.

I hear you Isaac. The issue was I was unable to find a way to ping a sql server which is completely locked down, eg all ports except the sql ports are closed, and I cant find a way to "ping" the sql port from ms access.

The system I have developed doesnt connection on load, but when it does connect, I dont want it to show those security nightmare dialogue boxes.

 

[Isaac]

Couldn't you just open a recordset that did something simple, like selected the top 1 record from a small ODBC linked table, with an Error Handler that trapped the inability to connect?
I am starting to think this whole post might just come down to a targeted error handler. Or have you tried that and the sql login/security/dialogue boxes still pop up regardless?

 

[ahmed_optom]

Couldn't you just open a recordset that did something simple, like selected the top 1 record from a small ODBC linked table, with an Error Handler that trapped the inability to connect?
I am starting to think this whole post might just come down to a targeted error handler. Or have you tried that and the sql login/security/dialogue boxes still pop up regardless?

You cant open any records unless you connect to a database, the error prompts in my first post happen when attempted to connect and refresh the dsnless connections. So doing anything with any records is not going to help, as the problem happens before that.

So its a real catch 22.

 

[Isaac]

I see. I was just trying to get at, specifically, whether an error handler could execute prior to that pop-up box (so the issue would just be that at some point during your connection code you would have an air handler and it would catch an error prior to that box popping up). but maybe you are right.... I seem to remember having the same problem once and no amount of error handling could get away from that stupid box. Solution had something to do with the DSN that I was using on the user system versus the trusted connection or connection string in the linked tables something like that. Anyway, I see what you mean, because I do think I remember that box popping up without regard to any error handler because a true error is not raised yet.

The only other thing I can think of is catching an open window title, I'm not saying it's a very elegant solution but it's the only other thing I can think of see this thread.

Capture "Your network access was interrupted" so can close/exit more controlled

Hi, I have a BE on a wired LAN. Very rare for interruption but power outage or just resetting a Switch/AP without remembering to close all FE (I have 4) give me the above error. I usually have to click 5 or 6 or more close or X on the window before access closes. It can of course happen from...

www.access-programmers.co.uk