Linked tables Access 2007

[Marsh]

We have an Access 2007 DB front end that is linked to SQL Server 2005 backend.
It appears for some reason, that the ODBC connection is lost each time we
exit Access, and it has to be re-established in each new session. Does
anyone have any suggestions as to what we should check to rectify this
problem. Thank you

Marsh

 

[Jeff Boyce]

Marsh

How are you determining that the connection is lost? Are there no tables
showing in the front-end, or perhaps, are you being prompted for a password
when you try to open/use the tables?

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[AccessVandal via AccessMonster.com]

Actually that's normal. When you quit Access, the connection is terminated.
How are you using the connection? Why do you quit Access when you still want
the connection?

 

[Mary Chipman [MSFT]]

This is probably more technical information than you need, but the
connection topics in the ODBC Programmer's Reference
http://msdn.microsoft.com/en-us/library/ms716224(VS.85).aspx describe
what goes on under the covers with linked tables, which use an ODBC
connection. Access pools connections, but they are returned to the
pool when Access exits and can't be reused by other instances. They
can only be reused by connections with the same connection string and
security credentials, so if you are using Windows authentication, each
user will have their own separate pool. HTH,

Mary

 

[Marsh]

Thanks for the offer to help

We get the message: ODBC connection failed

 

[Marsh]

I will check this site out, thank you.

This is probably more technical information than you need, but the
connection topics in the ODBC Programmer's Reference
http://msdn.microsoft.com/en-us/library/ms716224(VS.85).aspx describe
what goes on under the covers with linked tables, which use an ODBC
connection. Access pools connections, but they are returned to the
pool when Access exits and can't be reused by other instances. They
can only be reused by connections with the same connection string and
security credentials, so if you are using Windows authentication, each
user will have their own separate pool. HTH,

Mary


.

 

[Jeff Boyce]

When do you get that message?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Marsh]

In the morning, when first logging into netword and launching the Access
front end.
We are all required to logout nightly and leave machines on so the IT folks
can push down updates, patches, etc... So, when attempting to get back in,
the message that the connection failed is displayed.
I usually shut down Monday morning and then do a cold restart.

 

[Jeff Boyce]

I haven't run across that issue before -- ?perhaps one of the other readers?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Sylvain Lafontaine]

Never saw this problem before but ne possible solution would be to try with
the latest ODBC provider; available with the SQL-Server 2008 Feature Pack:

http://www.microsoft.com/downloads/...EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

This provider should work equally well against SQL-Server 2005.

 

[Marsh]

Thanks Sylvain, we will give that a try.

Never saw this problem before but ne possible solution would be to try with
the latest ODBC provider; available with the SQL-Server 2008 Feature Pack:

http://www.microsoft.com/downloads/...EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

This provider should work equally well against SQL-Server 2005.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com





.