Detect broken backend connection at startup (1 Viewer)

ygauthie

New member
Local time
Today, 07:01
Joined
Feb 17, 2015
Messages
3
I have a split Access 2010 database. If the backend file is not accessible (for instance, if our shared folder connection is broken), I get no warning message at all when opening the front end. Just the home screen you would get when launching access from the Start menu.

I was thinking of adding some VBA code to my startup form to give users a warning that there is a connection problem, but Access won't even load the startup form... Any advice?
 

MarkK

bit cruncher
Local time
Today, 07:01
Joined
Mar 17, 2004
Messages
8,178
If the startup form is bound to a datasource that it can't find, then that problem will occur before your code runs. Use an unbound startup form instead, or use the AutoExec macro, which runs before your startup form. If you create a macro called AutoExec, it will run before your startup form, and you can run code from that macro.

Hope this helps,
 

ygauthie

New member
Local time
Today, 07:01
Joined
Feb 17, 2015
Messages
3
I thought this would do the trick, but if remove or rename the backend file to test it, the AutoExec does not even get to run. I checked by placing a simple MsgBox in the AutoExec and nothing shows up. Just the normal Access page from where I can create or open a new database. :confused:
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Sep 12, 2006
Messages
15,613
take a table you know should be connected.

get the path from tabledef("tablename").connect

check that dir finds the path.


if you use more than one backend, it's a bit more complex.
 

ygauthie

New member
Local time
Today, 07:01
Joined
Feb 17, 2015
Messages
3
Thanks Gemma,

I understand what you are suggesting, but my point is that I cannot do such connection check as part of an AutoExec macro or unbound startup form, because I never get there. I want to do the connection check automatically to advise front end users when there is a connection problem instead of giving them no information at all.

Weirdly, I just tried this with a brand new, very simple, single-table database and the AutoExec connection check works. So there is a problem with the startup options of my real database. I can't figure where though.

Yvan
 

MarkK

bit cruncher
Local time
Today, 07:01
Joined
Mar 17, 2004
Messages
8,178
You have to remove your startup form. Just use AutoExec. Do your checks. If everything checks out, then open your startup form from your AutoExec macro.
I think,
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 10:01
Joined
Oct 17, 2012
Messages
3,276
That's how I did it when I last needed to check that the backend was available (as otherwise Paul Brown's auto-update code does Bad Things(tm) to the front end). As long as there's no startup form assigned, the AutoExec runs first.
 

DavidAtWork

Registered User.
Local time
Today, 14:01
Joined
Oct 25, 2011
Messages
699
why don't you write a function that will delete & reconnect the back tables as it opens, build in some error trapping when the path to the backend is not available. The path can be checked before trying to reconnect. This function can be run from the Autoexec

David
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Sep 12, 2006
Messages
15,613
something like this

autoexec macro
runcode startup

Code:
 function startup
  
 path = mid(currentdb.tabledefs("sometable").connect,11)
 on error goto pathnotfound
 if dir(path)="" then goto dirnotfound
 docmd.open "startupform"
 exit function
  
 pathnotfound:
    'network error
  
 dirnotfound
   'no such dir
  
 msgbox "sorry. quitting"
  
 docmd.quit
 end function
 

DNewman

Registered User.
Local time
Today, 14:01
Joined
Oct 12, 2012
Messages
60
I guess this tread is long dead! BUT anyway:
I use Access 2010 on windows 7 and 10
I use the following to check if the backend is linked:

'get path of linked DATA database
Set Db = CurrentDb
Set App = Db.TableDefs("Appointments")
BE_Path = App.Connect
Equal = InStr(BE_Path, "=")
BE_Path = Right(BE_Path, Len(BE_Path) - Equal)
If Dir(BE_Path) = "" Then
'link is broken - find DATA and link

The problem I have is that if the network path is not found it comes up with Error 32 which is, apparently, not an Access Error, so I can't error trap it to force the relink.
Any suggestions to get round this.
 

MarkK

bit cruncher
Local time
Today, 07:01
Joined
Mar 17, 2004
Messages
8,178
...if the network path is not found it comes up with Error 32 which is, apparently, not an Access Error, so I can't error trap it...
What line causes the error? The call to Dir()? I can generate an error 52, "Bad file name or number," by testing for a file on a non-existant network share, but it is trappable...
Code:
Sub Test1938461802471970()
On Error GoTo handler
    Debug.Print Dir("\\server\path\notfound.accdb")
    Exit Sub
handler:
    MsgBox Err & " " & Err.Description
End Sub
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 09:01
Joined
Feb 28, 2001
Messages
26,999
Perhaps the REAL solution to this is one suggested by another forum member some time ago for automatically downloading a new front end. Make the "launch" icon for this database run a script that tests for the existence of the BE directory. The script won't fail catastrophically; you can just make it say "No can do" and have it exit gracefully. If the BE folder is present, then launch the FE file from a command line. And of course, if you aren't sure what you want that command line to say, create a shortcut and then look at its properties to see what Windows would have said for the launch.
 

MarkK

bit cruncher
Local time
Today, 07:01
Joined
Mar 17, 2004
Messages
8,178
Oh, and also, if you are using Dir() to check for a folder, you need to use a trailing backslash AND the second parameter of the function needs to be vbDirectory, like...
Code:
    Debug.Print Dir("\\SomeServer\ExistingFolder[COLOR="Red"]\[/COLOR]", [COLOR="Purple"]vbDirectory[/COLOR])
 

DNewman

Registered User.
Local time
Today, 14:01
Joined
Oct 12, 2012
Messages
60
Many thanks

Yes, you are right, the Dir() throws the error

I think my problem is that I have:
Dir("\\UNKNOWN_NETWORK_LOCATION\path\notfound.accdb")
- I am installing an update developed on my system to a client whose network has different nomenclature.

I wonder if Windows is giving the error because it can't even begin to find the path??
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 14:01
Joined
Sep 12, 2006
Messages
15,613
Many thanks

Yes, you are right, the Dir() throws the error

I think my problem is that I have:
Dir("\\UNKNOWN_NETWORK_LOCATION\path\notfound.accdb")
- I am installing an update developed on my system to a client whose network has different nomenclature.

I wonder if Windows is giving the error because it can't even begin to find the path??

You should still be able to error-trap the routine.
As far as the path goes, I now tend to use an INI file/Text file to store the required path so the connect tables routine reads the path from the inifile first, and reconnects the tables if necessary.
 

speakers_86

Registered User.
Local time
Today, 10:01
Joined
May 17, 2007
Messages
1,919
When I do this, I also try to write to a text file on the shared drive to test for read/write access before even connecting to the backend. If that routine fails, I give the user the option to relink to a new backend (which triggers the test again) or quit entirely.
 

Users who are viewing this thread

Top Bottom