Detect broken backend connection at startup

[ygauthie]

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]

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]

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.

 

[gemma-the-husky]

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]

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]

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]

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]

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]

something like this

autoexec macro
runcode startup

 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]

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]

...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...

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]

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]

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...

    Debug.Print Dir("\\SomeServer\ExistingFolder[COLOR="Red"]\[/COLOR]", [COLOR="Purple"]vbDirectory[/COLOR])

 

[DNewman]

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]

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]

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.