From: Richard Finnigan on
Hi

Has anyone out there encountered problems with linked tables using mdb
files developed in Access 2003 but moved to Access 2007 with MS SQL
Server Express 2008 ?

The database server is on a local machine and the machine is running
XP Service Pack 2, I am going to install Service Pack 3 in order to
make sure that the most recent version of MDAC is installed.

What happens is that when an Access is project is run with the by pass
key and a form does not load automatically the linked tables are okay
but when I run the file normally the tables do not relink properly.

I've had to programatically relink tables on start up but this is not
an ideal solution because that means that the users now can't share
the same database file via shared folder and these files bloat when
they are constantly relinked.

I'm left with impression (correct or otherwise ?) that Access 2007 has
very poor backward compatibility with Access 2003. This issue has
almost resulted in me dumping a major project that I have spent
thousands of hours on which will mean that my employer will have to
spend large amounts of money going to a specialist MIS provider. :
( ..... not good.
From: Roger on
On Apr 13, 4:27 pm, Richard Finnigan <stjohns...(a)googlemail.com>
wrote:
> Hi
>
> Has anyone out there encountered problems with linked tables using mdb
> files developed in Access 2003 but moved to Access 2007 with MS SQL
> Server Express 2008 ?
>
> The database server is on a local machine and the machine is running
> XP Service Pack 2, I am going to install Service Pack 3 in order to
> make sure that the most recent version of MDAC is installed.
>
> What happens is that when an Access is project is run with the by pass
> key and a form does not load automatically the linked tables are okay
> but when I run the file normally the tables do not relink properly.
>
> I've had to programatically relink tables on start up but this is not
> an ideal solution because that means that the users now can't share
> the same database file via shared folder and these files bloat when
> they are constantly relinked.
>
> I'm left with impression (correct or otherwise ?) that Access 2007 has
> very poor backward compatibility with Access 2003.  This issue has
> almost resulted in me dumping a major project that I have spent
> thousands of hours on which will mean that my employer will have to
> spend large amounts of money going to a specialist MIS provider. :
> (  ..... not good.

why are you relinking the tables every time you start the front end ?
what error do you get when '... the tables do not relink
properly...' ?
are you sharing one frontend mdb with all users ?
From: Richard Finnigan on
On 14 Apr, 12:53, Roger <lesperan...(a)natpro.com> wrote:
> On Apr 13, 4:27 pm, Richard Finnigan <stjohns...(a)googlemail.com>
> wrote:
>
>
>
> > Hi
>
> > Has anyone out there encountered problems with linked tables using mdb
> > files developed in Access 2003 but moved to Access 2007 with MS SQL
> > Server Express 2008 ?
>
> > The database server is on a local machine and the machine is running
> > XP Service Pack 2, I am going to install Service Pack 3 in order to
> > make sure that the most recent version of MDAC is installed.
>
> > What happens is that when an Access is project is run with the by pass
> > key and a form does not load automatically the linked tables are okay
> > but when I run the file normally the tables do not relink properly.
>
> > I've had to programatically relink tables on start up but this is not
> > an ideal solution because that means that the users now can't share
> > the same database file via shared folder and these files bloat when
> > they are constantly relinked.
>
> > I'm left with impression (correct or otherwise ?) that Access 2007 has
> > very poor backward compatibility with Access 2003.  This issue has
> > almost resulted in me dumping a major project that I have spent
> > thousands of hours on which will mean that my employer will have to
> > spend large amounts of money going to a specialist MIS provider. :
> > (  ..... not good.
>
> why are you relinking the tables every time you start the front end ?
> what error do you get when '... the tables do not relink
> properly...' ?
> are you sharing one frontend mdb with all users ?

I dont want to relink tables everytime the database starts but if I
don't and I let the default form load automatically then the tables
lose their connection and I get error messages when the form loads.

The odd thing is that today I took out the procedure that relinks the
tables the problem has disappeared !!

Intermittant faults, dotcha just love em !!!... I hope this one does
not return.

Richard Finnigan

From: Richard Finnigan on
On 14 Apr, 20:29, Richard Finnigan <stjohns...(a)googlemail.com> wrote:
> On 14 Apr, 12:53, Roger <lesperan...(a)natpro.com> wrote:
>
>
>
> > On Apr 13, 4:27 pm, Richard Finnigan <stjohns...(a)googlemail.com>
> > wrote:
>
> > > Hi
>
> > > Has anyone out there encountered problems with linked tables using mdb
> > > files developed in Access 2003 but moved to Access 2007 with MS SQL
> > > Server Express 2008 ?
>
> > > The database server is on a local machine and the machine is running
> > > XP Service Pack 2, I am going to install Service Pack 3 in order to
> > > make sure that the most recent version of MDAC is installed.
>
> > > What happens is that when an Access is project is run with the by pass
> > > key and a form does not load automatically the linked tables are okay
> > > but when I run the file normally the tables do not relink properly.
>
> > > I've had to programatically relink tables on start up but this is not
> > > an ideal solution because that means that the users now can't share
> > > the same database file via shared folder and these files bloat when
> > > they are constantly relinked.
>
> > > I'm left with impression (correct or otherwise ?) that Access 2007 has
> > > very poor backward compatibility with Access 2003.  This issue has
> > > almost resulted in me dumping a major project that I have spent
> > > thousands of hours on which will mean that my employer will have to
> > > spend large amounts of money going to a specialist MIS provider. :
> > > (  ..... not good.
>
> > why are you relinking the tables every time you start the front end ?
> > what error do you get when '... the tables do not relink
> > properly...' ?
> > are you sharing one frontend mdb with all users ?
>
> I dont want to relink tables everytime the database starts but if I
> don't and I let the default form load automatically then the tables
> lose their connection and I get error messages when the form loads.
>
> The odd thing is that today I took out the procedure that relinks the
> tables the problem has disappeared !!
>
> Intermittant faults, dotcha just love em !!!... I hope this one does
> not return.
>
> Richard Finnigan

I suspect that the problem has been caused by not reffering to the
right SQL driver in the DSN string it was

ODBC;DRIVER=SQL Server;SERVER=FINNIGANTOSH
\SQLEXPRESS;UID=staffUser;PWD=red2stop;APP=2007 Microsoft Office
system;

It should have been

ODBC;DRIVER=SQL Native Client 10;SERVER=FINNIGANTOSH
\SQLEXPRESS;UID=staffUser;PWD=red2stop;APP=2007 Microsoft Office
system;

I will test this.