Catch linked sql table errors? (1 Viewer)

ahmed_optom

Registered User.
Local time
Today, 18:16
Joined
Oct 27, 2016
Messages
85
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)

Code:
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?
 

Attachments

Last edited by a moderator:

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 12:16
Joined
Feb 28, 2001
Messages
17,310
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.

Code:
@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


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

 

Isaac

Well-known member
Local time
Today, 10:16
Joined
Mar 14, 2017
Messages
1,497
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?


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

Registered User.
Local time
Today, 18:16
Joined
Oct 27, 2016
Messages
85
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.

Code:
@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


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

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

Registered User.
Local time
Today, 18:16
Joined
Oct 27, 2016
Messages
85
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?


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

Well-known member
Local time
Today, 10:16
Joined
Mar 14, 2017
Messages
1,497
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

Registered User.
Local time
Today, 18:16
Joined
Oct 27, 2016
Messages
85
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

Well-known member
Local time
Today, 10:16
Joined
Mar 14, 2017
Messages
1,497
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

Registered User.
Local time
Today, 18:16
Joined
Oct 27, 2016
Messages
85
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

Well-known member
Local time
Today, 10:16
Joined
Mar 14, 2017
Messages
1,497
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.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom