Code to search mdb in directory.

donnie91910
donnie91910 used Ask the Experts™
on
I need code that can search in a directory and find all the database's (*.mdb) in that directory and then find a "table name" that I am looking for in those databases.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Brook BraswellApplication Development Manager

Commented:
using the DIR you can look at a specific folder


MyFile = Dir(MDBPath & "*.mdb")
Do while MyFile <> ""
    IF MyFile <> "." and MyFile <> ".." then
      '  you should have a database here...
      ' open your database and look for the Table you want.
      ' Trap for an error trying to access that table and continue until you find it...
      SQL = "SELECT COUNT(*) AS RCOUNT FROM MYTABLE"
     
   End If
   MyFile = Dir
LOOP
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
This seems to work for me.  It searches through the path you provided (does not look at subfolders) for files ending in ".mdb".  When found, it attempts to determine the number of fields in the table that was provided.  If that step doesn't generate an error, it drops out of the loop and pops up a message box with the filename.

Public Sub FindTable(FilePath As String, TableName As String)

Dim strFileName As String, intFields As Integer
Dim bFound As Boolean
Dim db As DAO.Database

On Error GoTo ProcError

If right(FilePath, 1) <> "\" Then FilePath = FilePath & "\"

strFileName = Dir(FilePath & "*.mdb")
Do While Len(strFileName) > 0

     Set db = DBEngine.OpenDatabase(strFileName)
     On Error Resume Next
     intFields = db.TableDefs(TableName).Fields.Count
     If Err.Number = 0 Then
         bFound = True
         Exit Do
     End If
     On Error GoTo ProcError

     strFileName = Dir

Loop

If bFound Then
    MsgBox "Table found in: " & strFileName
End If

ProcExit:
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub
ProcError:
    Debug.Print Err.Number, Err.Description
    MsgBox Err.Number & vbCrLf & Err.Description
    Resume ProcExit
End Sub

Author

Commented:
Can either of these be written as a VB Script?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Top Expert 2014

Commented:
@donnie91910

>>Can either of these be written as a VB Script?
Yes...and this problem can be solved with PowerShell, Jscript, and use other database inspection methods.  The code, as posted, can also be invoked with command line parameters.

What is the context of this question?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Sorry, don't know anything about VBScript.

You might want to add VBScript to your zone list by clicking the Request Attention hyperlink in the bottom right corner of your original question.  Then ask the moderators to add the VBScript zone.
Give the code below a try.  It uses WMI to find the mdb files and then uses ADO to connect to the file.  Once connected, it checks the schema for the database to see if the required table exists.

Make sure you change the constants toward the top of the code to have the path you want to search and the table name you want to find.

Just copy the code into notepad and save as a .vbs file.

Option Explicit
' **** YOU NEED TO SET YOU DEFINING VALUES HERE ****
' The Drive and Path to search.  If a root, then put "C:\"
' Make sure you have a trailing \
Const STRFILEPATH = "C:\DBTest\"
' Set the Name of the table to find below
Const STRTABLENAME = "TestFind"

' Declare Needed Variables
Dim strDrive, strPath
Dim strQuery, objWMIService, colFiles, objFile
Dim objConn, rsTableList
Const adSchemaTables = 20

' Instantiate ADO Connection Object
Set objConn = CreateObject("ADODB.Connection")

' Fix the File Path to match the WMI Service query
strDrive = Left(STRFILEPATH, 2)
strPath = Replace(STRFILEPATH, "\", "\\", 3)

' Access the WMI Service
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")

' Build the WMI Query
strQuery = "Select * from CIM_DataFile where Drive = '" & strDrive & "'"
strQuery =  strQuery & " and PATH = '" & strPath & "' and EXTENSION = 'mdb'"

' Run the Query to find mdb files in provided directory
Set colFiles = objWMIService.ExecQuery(strQuery)

' Loop Through each found mdb file and see if it contains a certain table
For Each objFile in colFiles
	' Open Connection to found mdbFils
	objConn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & objFile.Name
 
	' Get a records set of the databases' schema which has table names
	Set rsTableList = objConn.OpenSchema (adSchemaTables)
	
	Do While Not rsTableList.EOF
		If STRTABLENAME = rsTableList("TABLE_NAME") Then
			msgbox STRTABLENAME & " was found in " & objFile.Name
		End if
		rsTableList.MoveNext
	Loop
	
	' Close RecordSet
	rsTableList.Close
	
	' Close DB Connection
	objConn.Close
Next

msgbox "Done Searching"

Open in new window


- Bear

Author

Commented:
The .vbs code seems to work.  Is there any way to do a "Like" on the table name?
Thanks.
Sure.  I have updated the code below.  Just make sure you set STRFILENAME to have the file name you want to search with the percent sign (%) as your wildcard.

Option Explicit
' **** YOU NEED TO SET YOU DEFINING VALUES HERE ****
' The Drive and Path to search.  If a root, then put "C:\"
' Make sure you have a trailing \
Const STRFILEPATH = "Const STRFILEPATH = "C:\DBTest\""
' Set the Name of the table to find below
Const STRTABLENAME = "TestFind"
' Set the File Name Query 
Const STRFILENAME = "test%"

' Declare Needed Variables
Dim strDrive, strPath
Dim strQuery, objWMIService, colFiles, objFile
Dim objConn, rsTableList
Const adSchemaTables = 20

' Instantiate ADO Connection Object
Set objConn = CreateObject("ADODB.Connection")

' Fix the File Path to match the WMI Service query
strDrive = Left(STRFILEPATH, 2)
strPath = Replace(STRFILEPATH, "\", "\\", 3)

' Access the WMI Service
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\.\root\cimv2")

' Build the WMI Query
strQuery = "Select * from CIM_DataFile where Drive = '" & strDrive & "'"
strQuery =  strQuery & " and PATH = '" & strPath & "' and EXTENSION = 'mdb'"
strQuery =  strQuery & " and FILENAME Like '" & STRFILENAME & "'"

' Run the Query to find mdb files in provided directory
Set colFiles = objWMIService.ExecQuery(strQuery)

' Loop Through each found mdb file and see if it contains a certain table
For Each objFile in colFiles
	' Open Connection to found mdbFils
	objConn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=" & objFile.Name
 
	' Get a records set of the databases' schema which has table names
	Set rsTableList = objConn.OpenSchema (adSchemaTables)
	
	Do While Not rsTableList.EOF
		If STRTABLENAME = rsTableList("TABLE_NAME") Then
			msgbox STRTABLENAME & " was found in " & objFile.Name
		End if
		rsTableList.MoveNext
	Loop
	
	' Close RecordSet
	rsTableList.Close
	
	' Close DB Connection
	objConn.Close
Next

msgbox "Done Searching"

Open in new window

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial