Getting data from other databases

[Kenln]

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.

 

[Robert88]

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?

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

 

[Kenln]

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.