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).