Run a simple select statement from one database against the MSysObjects tables in multiple databases.

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
FROM MSysObjects
GROUP BY [CurrentProject].[FullName], MSysObjects.Connect
 HAVING ((Left([connect],5)="dBASE"));

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.
LVL 1
upsfaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:
What version of Access on there server databases?
0
upsfaAuthor Commented:
A mix of 2003 and 2010.  All users are moving to 2013 shortly and dbf is no longer supported, so I need to identify what dbs will be affected.
0
jerryb30Commented:
So, you want just the dbf path and name, and the Access db path and name?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

jerryb30Commented:
Create a module, and paste in the code found here:

http://access.mvps.org/access/api/api0002.htm

Create a table called tblDBs, with 3 fields
SourceDBName, memo
LinkDBFName, memo
Connect, Memo

Have  Microsoft Scripting Runtime set as a reference.

Paste following in new module.  Modules must have different names than any of  the functions.
FunctionFindDatabases(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
on error resume next
Dim FSO As Scripting.FileSystemObject

Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
dim rs as dao.recordset
dim rs2 as dao.recordset
dim strsql as string

    Set FSO = New Scripting.FileSystemObject
    
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    
    For Each FileItem In SourceFolder.Files
      if fileitem.name like "*.mdb" or fileitem.name like "*.accdb" " then
strsql = "Select [name], [connect] from msysobjects in '" & fileitem.name & "' where left(connect,5) = 'dBase'"
set rs = currentdb.openrecordset(strsql)
set rs2 = currentdb.openrecordset("tblDBs")
rs.movefirst
do while not rs.eof
rs2.addnew
rs2!SourcedbName = fileitem.name
rs2!LinkDBFName = rs![name]
rs2!Connect = rs!Connect
rs2.update
rs.movenext
loop
rs.close
rs2.close

endif

'here is where you execute
Function FindDBFConnect()
Dim strfilepath As String
strfilepath = BrowseFolder("Browse to selected folder")
FindDatabases strfilepath, True
End Function

Open in new window


I don't have an environment with linked dbf's.
0
upsfaAuthor Commented:
Thanks!  Looks good, but I got some errors.  I also attached a sample dbf you can use, if that will help (just change the extension to dbf after you download).
SAMPLE.txt
errors.JPG
0
jerryb30Commented:
How did I miss this?
in Function FindDatabases
after:
rs.close
rs2.close

endif
next  <-add this
end function <-add this
0
upsfaAuthor Commented:
Thanks, will try.  Also, there was no space between Function and FindDatabases in the first line and there was an extra set of quotes on line 18.
0
upsfaAuthor Commented:
Looks like BrowseFolder variable was never created.
BrowseFolderError.JPG
0
upsfaAuthor Commented:
I see - BrowseFolder is not a variable, but a function that I don't have.

strfilepath = BrowseFolder("Browse to selected folder")

I just keyed in my test folder directly.  It's running now.

strfilepath = "C:\temp\"
0
upsfaAuthor Commented:
Not getting an error, but it just hangs.  There is only one file in the folder I am searching.
0
jerryb30Commented:
I found some more errors.
I'll post new code shortly.
I want to do some more error checking.
0
jerryb30Commented:
I think I have it.
In code I did, I got full path and file name of the mdb in one field, and the concatenation of [DataBase] and [Name] from msysobjects where [Connect] is like 'dbase'
Is that what you need?
0
upsfaAuthor Commented:
Exactly!
0
jerryb30Commented:
See attached. You could probably use it as is, except for reference versions.
FindDBF.mdb
0
jerryb30Commented:
Out for a while.
0
upsfaAuthor Commented:
Very nice, but it is not searching sub folders.  I see the argument "IncludeSubfolders" being passed in the function, but I can't figure out how to get it to work.  Thanks!
0
jerryb30Commented:
Is the server (or starting folder) mapped to a drive letter?
0
upsfaAuthor Commented:
yes it is.
0
jerryb30Commented:
Did you use the db I posted, or copy things?
0
upsfaAuthor Commented:
I used your database and when I run  the function FindDBFConnect() only databases in the folder I select get added to the tblDBs table.  There are many sub folders within that contain databases with links to dbfs.
0
jerryb30Commented:
Hmmm. I did not use sub-folders to test. Doing so now. Some flaw in code?
0
upsfaAuthor Commented:
I've been trying some things, but have not got it yet.  There needs to be some recursive code that actually moves to the sub folder.  "For Each FileItem In SourceFolder.Files" Looks like we need to search SubFolder at some point.
0
jerryb30Commented:
I forgot the check on IncludeSubFolders

Add to end of FindDatabases:


Next '<---Add After here
    If IncludeSubfolders Then '--->Add
        For Each SubFolder In SourceFolder.SubFolders '--->Add
            FindDatabases SubFolder.Path, True '--->Add
        Next SubFolder '--->Add
    End If '--->Add
End Function 'End of addition
0
jerryb30Commented:
here's new db.
NewFindDBF.mdb
0
upsfaAuthor Commented:
It works, but we have various errors:

"the microsoft access database engine stopped the process because you and antother user are attempting to change the same data at the same time" - running during off-hours should reduce or eliminate this.

"Records cannot be read, no permissions on msysobject" - we can remove that db from lan and run again.  

Then we might get "Invalid database object" - I repaired and compacted that one and it worked.  

Slow going since we have 800+ database files and 70+ users on the network.
0
jerryb30Commented:
You might want to catch the different errors, and do a Resume Next when they occur.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
upsfaAuthor Commented:
Added some error handling, and it worked like a charm.  Great job on the solution!  Much appreciated!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.