Results 1 to 7 of 7
  1. #1
    Join Date
    May 2008
    Posts
    4

    Unanswered: Open connection from linked tables

    I have several linked tables, each to a file (table) on a separate AS400 using ODBC -Machine Data Source.

    My question is, using VBA can I find and or close which tables have an open connection?

    Thanks in advance

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can delete and re-create the table links themselves as needed.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2008
    Posts
    4
    Agreed, but do you know a way to find out what connections are open, or to clear all open connections?

    I have linked tables in VBA, but I think Access leaves the connection open after the table is closed.

    I appreciate your response.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Can I ask why you need to do this? Curious mainly, as I've never felt the need to do it.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2008
    Posts
    4
    I am new to this company, which has several AS400s, in different regions, but with the same database. There is a need to pull and compile data from each.

    The existing solution is to link the tables in Access, create a query to pull the data, copy the query for each AS400, and then use a macro to run all queries.

    My solution is to use a VBA module to loop all of the AS400s (I have the connection info stored in a table) and to run a pass through query (ADO).

    There are a handful of Access databases in prod that use the first method. The issue is that a macro will end after five queries have been run. So instead a one macro, there are several. I was told the ODBC driver has a limit of 5 connections and I think Access leaves the connections open even after the query has been run.

    I was looking for a way to determine what connections are open and to close/clear the connection(s).

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Thanks for the explanation

    The only way I know how to clear such connections is to delete and re-create the table links.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2008
    Posts
    4
    I appreicate the responses.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •