Microsoft Access 2000 missing ODBC Links

I have a Microsoft Access 2000 with several ODBC linked tables.
The back end DB is SQL Server 2000.
At the beginning everything was running OK.
Apparently randomly, Access returns the following error:
"ODBC -- connection to SQL Server failed" when I try to open a linked table
that previously work.
I must delete the linked table and create the link again to let the system w
ork again.
Please Help !
Thanks
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...That happens whenever you make a schema change to the table in SQL
Server. One way to avoid these errors is to write VBA code that runs
on startup. It deletes any old links and re-creates them when the
Access app opens, guaranteeing that the links are always valid.
--Mary
On Fri, 22 Oct 2004 02:40:27 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>I have a Microsoft Access 2000 with several ODBC linked tables.
>The back end DB is SQL Server 2000.
>At the beginning everything was running OK.
>Apparently randomly, Access returns the following error:
>"ODBC -- connection to SQL Server failed" when I try to open a linked table
that previously work.
>I must delete the linked table and create the link again to let the system
work again.
>Please Help !
>Thanks
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Thanks for the help, but the base SQL Server tables are always the same !
No schema update have been made to them.
PS: Sorry for my POOR english...
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||It might be a network error if you're not making any schema changes.
I'd still recommend creating a procedure to deletelink/relink tables.
This will save you from having to do it manually every time there's an
error and the connection gets dropped.
--Mary
On Fri, 22 Oct 2004 08:33:12 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for the help, but the base SQL Server tables are always the same !
>No schema update have been made to them.
>PS: Sorry for my POOR english...
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Thanks for answering me !
I'll keep on trying.
Table relinking solution is OK but it's too slow...
Thanks anyway.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||It shouldn't be slow at all if you have a valid SQL Server connection
over a LAN. It should only take a couple of seconds for the code to
execute.
--Mary
On Mon, 25 Oct 2004 01:38:45 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Thanks for answering me !
>I'll keep on trying.
>Table relinking solution is OK but it's too slow...
>Thanks anyway.
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||Relinking one or two tables is OK, but I have 110 tables linked :-O !
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||I'm not talking about doing it manually, I'm talking about running VBA
code that uses DAO to delete and recreate tabledef objects. This
should run very quickly since all it's doing is creating tabledef
objects in the local .mdb and setting their connection properties. You
don't access the actual tables on the server until you open them. The
only way this code takes longer than a few seconds is if you're
linking thousands of tables, which no one in their right mind is going
to do anyway.
--Mary
On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
wrote:

>Relinking one or two tables is OK, but I have 110 tables linked :-O !
>
> ****************************************
******************************
>Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
>Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resourc
es...|||You shouldn't need to delete and recreate the TableDef objects Umberto. Just
call the RefreshLink method. Mary is correct it should go very fast -
seconds not minutes. Use this code:
For Each tdf In CurrentDb.TableDefs
If InStr(1, tdf.Connect, "ODBC") > 0 Then
tdf.RefreshLink
End If
Next
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:ivlsn0p3bhju561t3i46vu8g01j2a73d37@.
4ax.com...
> I'm not talking about doing it manually, I'm talking about running VBA
> code that uses DAO to delete and recreate tabledef objects. This
> should run very quickly since all it's doing is creating tabledef
> objects in the local .mdb and setting their connection properties. You
> don't access the actual tables on the server until you open them. The
> only way this code takes longer than a few seconds is if you're
> linking thousands of tables, which no one in their right mind is going
> to do anyway.
> --Mary
> On Tue, 26 Oct 2004 06:41:18 -0700, Umberto Tullo (tullo@.tin.it)
> wrote:
>
ASP.NET resources...[vbcol=seagreen]
>|||I have been having the same trouble lately, but I think I just fixed it. Whe
n linking the table there is a check box to save password (I swear I have ne
ver seen it before). I reestablished one table from each database I am linke
d to and it seems to work. Previously every time I closed and reopened Acces
s I had to relink the tables.