Table Link Problems in Access 2007

[Mark]

Hi

I've been using Access since v2 but only recently started developing with
2007 and have come to a real halt when table links get broken.

I've imported tried and tested code to check and fix up tables links if the
path to the data changes for any reason and this seems to run ok in Access
2007, BUT... I'm finding that if I move the backend data to a different
path, Access 2007 won't even open the front end!! Nope, doesn't open my db
at all, just sits there at the Access welcome page letting me click away at
my recently used front end. So, because the FE won't open my AutoExec and
relink code never fire.

I've tried disabling the AutoExec macro and have no form loading on startup
so db should open to db window... but nothing. If I move the backend back to
original location the FE opens fine!

Maybe I'm missing something basic as it's hard to believe this should happen
and would basically render any FE useless when the data path changed.

The database is Access 2003 format and I've installed Office 2007 SP1.

I've nearly completed this project and just split the db ready to deploy so
any feedback greatfully received!!

Thanks
Mark

 

[Chris O'C via AccessMonster.com]

Is the backend signed with a trusted digital certificate? Is the new back
end in a trusted folder? Is macro security set to low?

Chris
Microsoft MVP

 

[Mark]

Chris

Backend is not signed. Location is trusted. Macro security is set to low.

I now think this issue is related to the Split Database feature in Access
2007. I used this to create the BE from my original development db. I have
since used the manual approach where I made 2 copies of the original db and
deleted appropriate objects from each copy and then created links from FE to
BE.

This FE does not exihibit the same issue as the FE modified by the Split
Database feature.

I have re-run the split feature on the original db and the resultant FE
suffers the same problem so it appears the issue is reproduceable.

I'm pleased to have a way forward but dissapointed to have wasted so much
time and what looked like a real time saving feature.

Steps to reproduce:

Run the split db feature on a "forms and tables" db.
Open the original db to confirm tables have moved and replaced by links
Close all dbs
Rename BE db
Access now will not open the original FE db
Restore original BE name and Access WILL open the FE

One thing I did notice and thought was odd; the split db feature didn't
remove the relationships from the FE. Don't know if this is relevant or not.

Regards
Mark

 

[Albert D. Kallal]

I've tried disabling the AutoExec macro and have no form loading on
startup so db should open to db window... but nothing. If I move the
backend back to original location the FE opens fine!

Maybe I'm missing something basic as it's hard to believe this should
happen and would basically render any FE useless when the data path
changed.

I tried to re-produce you above behavior and it not occurring for me at
all.....

The database opens instantly and without ANY delay at all. When I try to
open a linked table, I get the message:

"Could not find file 'c:\...bla bla la"

So, I don't experiance ANY hanging at all. I would try holding down the
shift key during startup.

I tested this this with both a mdb (2003) format database using a2007,
and it worked just fine. And, I also tried this with a 2007 (accdb)
database, and again it worked just fine.

Some code, or some permissions to the directory or something is not working
here.

As a general rule the behaviors is not any different then I experienced
using previous
versions of ms-access....

 

[Chris O'C via AccessMonster.com]

The relationships in the front end don't matter because they can't be
enforced on tables in other files. Your workaround is interesting. Thanks
for sharing.

Chris
Microsoft MVP

 

[Mark]

Albert

In that case maybe there is something specific with my db or environment.

FWIW I tried opening with shift and with no autoexec and with no initial
form. I am working on a WinXP SP2 machine and local drive so no network or
share permission issues.

Everything I am trying to do WORKS in the db where I manually moved the
tables and manually linked to the tables in their new location and I agree
that the behaviour works the same as previous Access versions. It's not that
my code fails if I use the Split Db feature, Access just won't open the FE
if the BE is moved/renamed. I tried an Autoexec macro with msgbox Hello
World... but the db just doesn't open and nothing gets displayed.

I've never before seen Access fail to open a db without some kind of
message. It doesn't hang, it basically does nothing: I use File, Open browse
to FE and then Access just pops me back to its main App window, quietly
ignoring the Open. (I checked Task Manager and no toher instance running or
hung.) Only putting back the BE where it was last seen by the FE allows
Access to open the FE.

Given that manually splitting a db only takes a few minutes longer than
using the split feature it's no big deal but it is a shame it didn't like,
and actually broke, my application. I'm thankful I took a backup
immediately prior to running the split and would advise anyone else to do
the same in case they hit the same issue.

Regards
Mark

 

[Robin]

Given an hour I couldn't have written a more concise explanation of what has
happened to me as well. Except my project was already split FE/BE prior to
upgrading from Access 2002 to Access 2007 and I'm running on Vista Ultimate.

So I cannot go back to the pre-splitting procedure and recover, I'm still in
the mud. I think I may have made the mistake of "testing" my linking code by
changing the name of the BE. Unfortunately, I think this was done while
Access was open and linked to the BE. I was not able to recover by renaming
the BE back to the original name.

So I used Albert's checklist to immediately, copy, Decompile, etc. I was
able to import everything and all seemed well except now in my "clean"
project I have the problem lingering that you describe.

Three different code modules for testing the links and refreshing the links
give the same result.

Any additional suggestions or revelations you've had would be greatly
appreciated.

Thank you,
Robin

 

[Robin]

I had the exact same problem and struggled for a week since my data base was
already split prior to migrating to Access 2007. It turns out that since I
had a custom ribbon which I stored in the BE. When the BE was moved or
renamed, the FE would not open at all. Even holding the shift key.
Apparently Access looks for the ribbon prior to firing the AutoExec Macro.
Microsoft has listed this fix in KB926533

http://support.microsoft.com/kb/926533

Simply moving the custom Ribbon/QAT to the FE solved the problem.

HTH,
Robin