Dropping the ODBC connection

[Guest]

Hi
I posted this in 'access:getting started' a few weeks ago, without success!

I have a few basic databases that draw information from our server using
ODBC. Once the database has got its ODBC data imported, Access automatically
leaves the ODBC connection open. If I quit Access and restart, the
connection clears - and other people are able to use it. Does anyone know of
a way of importing the ODBC data (from a linked table) and then dropping the
connection to the server?

Thanks.

 

[Douglas J. Steele]

You can simply delete the linked table once you've imported the data.

In fact, you don't even need a linked table: you can create a pass-through
query, use that to import the data, then delete the pass-through query.

 

[dawsdels]

Hi Doug - and thanks for your reply.

I need to use the ODBC connection as a link because I need to be able
to update it - when necessary. If I use it as an import - it's fine,
but I need to be able to leave it as a linked table so I can refresh
it. Once it's been refreshed, though, the ODBC connection stays up. It
means that other people in the company can't use the catalog file
until I close Access and restart.

Andy.

You can simply delete the linked table once you've imported the data.

In fact, you don't even need a linked table: you can create a pass-through
query, use that to import the data, then delete the pass-through query.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)







- Show quoted text -

Hi Doug - and thanks for your reply.

I need to use the ODBC connection as a link because I need to be able
to update it - when necessary.

 

[Douglas J. Steele]

So have you tried simply deleting the linked table when you're not using it,
and only adding it back when you need it?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi Doug - and thanks for your reply.

I need to use the ODBC connection as a link because I need to be able
to update it - when necessary. If I use it as an import - it's fine,
but I need to be able to leave it as a linked table so I can refresh
it. Once it's been refreshed, though, the ODBC connection stays up. It
means that other people in the company can't use the catalog file
until I close Access and restart.

Andy.

You can simply delete the linked table once you've imported the data.

In fact, you don't even need a linked table: you can create a pass-through
query, use that to import the data, then delete the pass-through query.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)







- Show quoted text -

Hi Doug - and thanks for your reply.

I need to use the ODBC connection as a link because I need to be able
to update it - when necessary.

 

[dawsdels]

Hi

The table stays as a linked table - and is used again when a query
requests it. If I delete the table, I'd have to recreate the link
every time to refresh - and then delete the table again. Is that a
good idea?

Thanks.
Andy.

 

[Douglas J. Steele]

As far as I know, there's no way to keep the linked table but break the
connection, so I'd say it's your only option.

Assuming you're using a DSN, is there a Shared property you can set in the
DSN?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi

The table stays as a linked table - and is used again when a query
requests it. If I delete the table, I'd have to recreate the link
every time to refresh - and then delete the table again. Is that a
good idea?

Thanks.
Andy.

 

[Rick Brandt]

Hi

The table stays as a linked table - and is used again when a query
requests it. If I delete the table, I'd have to recreate the link
every time to refresh - and then delete the table again. Is that a
good idea?

There is no reason why you having the connection open should prevent others
from connecting to the same table. ODBC links would be pretty useless if
only one person could use a table at a time.

If you are exclusively locking the table then fix THAT.

 

[viktor chuzhakin]

Hi Doug - and thanks for your reply.

I need to use the ODBC connection as a link because I need to be able
to update it - when necessary. If I use it as an import - it's fine,
but I need to be able to leave it as a linked table so I can refresh
it. Once it's been refreshed, though, the ODBC connection stays up. It
means that other people in the company can't use the catalog file
until I close Access and restart.

Andy.



Hi Doug - and thanks for your reply.

I need to use the ODBC connection as a link because I need to be able
to update it - when necessary.

 

[dawsdels]

Hi again - and thanks for your replies

You've all given me quite a few ideas - so I'll keep trying and report
back!

Cheers.