How to make the Linked Tables in Access to Share only ONE connection?

[cyberdave]

Hello,

I appreciate it if someone can help me.

I have linked few Tables in Access form an ODBC-compliant database.
But when I check the database, it shows the number of connections as
the same as the number of tables that I've linked in Access.

My question is: Is there a way to share only ONE connection for those
linked tables?

Thanks.

DK

[Jerry Porter]

I think my database uses one connection for the tables. I refresh my
links as follows when the database opens. I don't know if that's what
makes the difference.

Here's a snippet to refresh non-temporary ODBC attachments. You do have
to determine the connect string sConnect first.

set db = Currentdb()

For Each tdf In db.TableDefs
If (tdf.Attributes And dbAttachedODBC) = dbAttachedODBC _
And Left(tdf.Name, 1) <> "~" Then
tdf.Connect = sConnect
tdf.RefreshLink
End If
Next

Jerry Porter

[cyberdave]

Hello, Jerry,

Thanks for your reply. In my scenario, I have linked and opened
(simultaneously) 50 tables in Access. If I go back to the database
where I linked my tables from, I can see 9 connections. Do you think
if there is a way to use only one connection for my scenario?

BTW, how do I add those code in Access? Thanks.

DK

[Jerry Porter]

I had only one connection for my tables. I added another linked table,
and now I have two connections (though both sleeping), and I can't get
rid of the 2nd one, even after deleting the new attachment.

I tried stopping and restarting SQL Server.

So I don't have an answer.