I need to run a simple select statement from one database against the MSysObjects tables in multiple databases. I have a list of databases and their paths. Since RunSQL method cannot be used with Select statements, this could use Insert statements or Create Table, if necessary.
Background: I have 300+ Access databases on the server. I want to find out how many of them have links to DBF tables. The basic select is this:
SELECT [CurrentProject].[FullName] AS [Database], MSysObjects.Connect, Count(MSysObjects.Id) AS CountOfId
GROUP BY [CurrentProject].[FullName], MSysObjects.Connect
I realize the [CurrentProject] won’t return the target database name if run from another db. That info can come from the list of databases used in the loop.