We have a third party application for our (project) estimates and tracking the bids and their status. They create a seperate access database for each proposal and while there is a main db with some information (not always current) the actual data resides in the job db for each job.

If I wanted information from these dbs I (believe) I need to open the directory and get a listing of file names which I could store in an array. Then I need to open each db linking no more that three tables (I hope) collect the data close the db and do it again for each.

I'm should be able to get a directory list but...

What is the best way to get information from many (could be in the hundreds though probably less than 100 right now) databases.

Thank you for any thoughts.

Hi Kenln,

Just found an interesting site but noticed it only looks for one table, might be a start?

'******************** Code Start ************************
' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
'
Function fExistTable(strTableName As String) As Integer
Dim db As Database
Dim i As Integer
Set db = DBEngine.Workspaces(0).Databases(0)
fExistTable = False
db.TableDefs.Refresh
For i = 0 To db.TableDefs.Count - 1
If strTableName = db.TableDefs(i).Name Then
'Table Exists
fExistTable = True
Exit For
End If
Next i
Set db = Nothing
End Function
'******************** Code End ************************


Website: http://www.mvps.org/access/tables/tbl0001.htm

Hope it helps? :rolleyes:

This code will list any file in a folder;

Private Sub CmdReadfolder_Click()

Dim Drivename As String

Drivename = C:\data

Dim dbs As Database
Dim Mysize
Set dbs = OpenDatabase("C:\Test\Test.mdb")

'Clear the tblFilename table before scan is added.
dbs.Execute "DELETE * FROM tblFilename;"

Set fs = Application.FileSearch
With fs
.LookIn = Drivename
.FileName = "*.*"
.SearchSubFolders = True
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For I = 1 To .FoundFiles.Count
Mysize = FileLen("" & .FoundFiles(I) & "")
'send data to a message box for test purpose.
'MsgBox .FoundFiles(I)

'send data from Sql to variable Sql.
SQL = "INSERT INTO tblFilename" & _
"(fldFieldname, fldFileSize) Values " & _
"('" & .FoundFiles(I) & "', '" & Mysize & "');"
'send data from sql to message box
'MsgBox Mysize
'send data form Sql to database tblFilename.
dbs.Execute SQL

Next I
MsgBox "The folderpath and filenames have been written to table tblFilename succesfully."
'close database connection.
dbs.Close
Else
MsgBox "There were no files in the folder."
End If
End With
'close the database
dbs.Close

End Sub

Good Luck.
Robert88

The second code looks good for determinining what DB are there. Thanks, my version wasn't quite the same way and I was building an array though with a table I don't have to worry about the count.


I'm not really worried about the tables existing. If the DB is there the table should exist.

I am more curious as to the best way to read the tables from a DB, keeping in mind I may have several hunderd DBs. Each will have three tables. The tables names are identical regardless of DB.