Intercepting linked table error message on startup

[bravofoxtrotuk]

I'm using A2003 and am just setting up for splitting my database. I'm
trialling Dev Ashish's code to relink tables etc, but if I rename the back
end to test for loss of the file, which is what my users are most likely to
do, Access fires up a message that it can't find the file (which is
understandable). I've tried putting a call to Dev's code in autoexec, and in
the switchboard form, but it's obviously firing before that. Can I intercept
this message to put my own up? If not I can probably work round it.

Bob

 

[Jim Burke in Novi]

You need some code that gets run as soon as your application starts up,
either an AutoExec macro or a startup form. I use a startup form that is a
splash screen - it just displays a message showing the name of the appl and
the version. I have a timer set that shuts that form aftrer a couple of
seconds. BUt the first thing it does is make sure the data DB is there. In
your autoexec macro or startup screen, for example, try to reference a table
that you know should be there. Try to open a recordset, try to do a DLookup,
something like that. Have an on error statement right before that that will
go to a line of code that will intercept the error if there is one, print
your error message and then close the application. If referencing the table
doesn't generate an error, go on with your initiailization, etc.

 

[bravofoxtrotuk]

Jim,

thanks for advice. I've tried all of that but Access STILL jumps on before,
so I guess it's happening even before autoexec etc. But - I've carried on
hacking about since posting this, and tho it's a bit of a pest, I can live
with it. It just needs a click on OK, then my own message fires up. I can
then sort out re-linking the tables as I need.

Well it's a partial answer...

:9)

 

[Klatuu]

Nothing happens before that. Somewhere you have code that is trying access
the linked tables.

As a test, I just renamed the be file name of an app and opened the fe. No
error until I tried to open one of the linked tables. That is when I got
the errror.

So, somewhere in your app, something is trying to open or in some other way
refer to one of the linked tables.

 

[Jim Burke in Novi]

Klatuu is right. You must either have some code somewhere that's trying to
reference a table, either in a function initiated by AUtoexec, or your form
that gets fired up at appl start is bound to a table or something along those
lines, or else a startup form has some code that refernces a table in a
form-related Event that gets triggered at startup (e.g.On Open, On Current,).
There's no reason for Access to to try to open the back end DB until you
attempt to reference it somehow. You just need to do your check before any of
that happens. You must be missing something. Do you have an autoexec macro or
a startup form?

 

[bravofoxtrotuk]

Guys,

you're right, I do have stuff on the switchboard tied to a table, as well as
autoexec running, but I've tried every combination of disabling both of these
to intercept it. Hmm - if there's nothing else happens prior to that then I
need to go away and dig around some more to see if I can isolate it. I'll
let you know...

As always, your help on this forum is very much appreciated.

Cheers,

Bob

 

[dch3]

If one of your concerns is whether or not the file exists, I'd add a bit of
code that checks that the file exists in and of itself. You can do this
independent of the relinking code.

 

[bravofoxtrotuk]

Guys,
I've done some more experimenting, and must admit that my understanding of
how Access starts was clearly a bit wrong - but then you can always learn
something! The problem *is* with the switchboard as it has fields with
dblookups on it. However, I always assumed that the events code behind it
would be run before anything else as in 'Open>Load>Activate>Current' etc, but
it seems not. Putting messages in at these points shows they don't even get
reached before Access complains as it starts the form up - which is set in
the startup options. So the VBA code behind it is not even being run at this
point. Which, when I think more about it, kind of makes sense now. I should
have realised that, but have not been able to find anything anywhere which
told me. Even autoexec doesn't run until the switchboard (or whatever startup
form) is fully loaded. If I disable the switchboard, or specify a different
form on startup it does as you say Klatuu, and ignores the fact there's no
linked tables there yet.

So to summarise what I now believe - any lookup fields in a startup form to
any missing linked tables will cause Access to complain *before* any code
(and including autoexec) takes effect.

Knowing this now, I can set to and sort out a workaround within the
constraints I've set myself on that dratted switchboard!

If I'm still wrong, by all means let me know, and many thanks again for your
help.

Cheers,
Bob

 

[John Spencer]

You might add a "Splash Screen" form as the start up form instead of using the
switchboard form.

You can check your connections in the splash screen and if all is good then
close the splash screen form and open the switchboard form. If the
connections are bad then you can use code to relink or just shut down.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County