Looping through DATABASES

[eshajoshi]

Hi everyone,

I have a list of an unknown number of Microsoft Access 2003 databases. All of my databases have the same tables but obviously different recordset data.

I am able to open one table from one of the databases (select a table from one database) and edit one recordset. Is there any possible way to update this change to all databases?

For example, if I open Database A and Table AA and edit a recordset called "Speed" from 1 to 2, can I change all values of "Speed" in every database under the same Table and recordset?

Sorry this is a bit confusing. I have been trying to figure this out for a while and do not know if I need to create an unknown number of databases.

Thank you!

[Klahr_R]

I think what you ask is not a simple question.
The "unknown number" bothers me.
Are they localized to a single drive or root folder? Too many unknowns.

If it was me I'd probably use the File System Object to loop through folders known to contain MDB files.

Option Explicit
' in Project/Reference check Microsoft Scripting Runtime
Dim FSO As New FileSystemObject

Private Sub TryThis()
Dim MyFolder As Folder
Dim AFile As File
    ' Assuming there are MDB files in ProgramData        
    Set MyFolder = FSO.GetFolder("C:\ProgramData") 
        For Each AFile In MyFolder
            If FSO.GetExtensionName(AFile) = "mdb" Then
                'You probably need to validate it is one of the MDB you want.
                'do your updates on the current database
            End If
        Next AFile
' Clean up objects
set myfolder = nothing
Set AFile = Nothing
Set FSO = nothing    
End Sub

That is very rough but maybe it will give you a starting point. You have to do code that will include sub folders and so on--depending on how the files are organized.
Regards

[eshajoshi]

Hi Klahr R,

Thanks for your response.

At work, for whatever reason, the company stores its data in Access databases.

My mentor would like me to design and program a module that will allow him to make changes to one database if he prefers, or uniform changes to a selected number of databases.

This number is "unknown" because he can choose to update one, two,...or all databases.

Thanks. Does this help?

[Klahr_R]

Gven that,
Perhaps you might want to list the availale databases using a listbox with style set to 1 giving a check box along with the file name. Or, depending on organization, there is a list box of folders that contain MDBs. Or perhaps one is used to populate the other, and so on.
I would think that you will still want to loop through given folders, finding MDB extensions to populate the Listbox. Still sounds like a job for FSO.
The question remains if the MDBs are organized by folder.
Hope that gives you a starting point.

[eshajoshi]

Yeah so all the MDBs are in one folder. And I actually have another list box in which the user can select the databases he wants to be processed. The mdbs selected will be put into this additional list box (file name). The file paths of these selected mdbs are stored into a separate textbox. So that's where the unknown factor comes into play.

I tried using the command List1.ListCount after the mdbs were selected and that did work in terms of giving me the int. Right now my program is able to manipulate and update pieces of data from one table in only one database. But enacting such a change(s) to ALL OF THE SELECTED mdbs is where I'm not too sure what to do next.

But thank you for your idea of using FSO. I will read more about it and try to implement that.

[Klahr_R]

If I understand correctly then you are almost there.
If you already have the selected db list in a separate list box, AND all dbs are in the same folder then Path is no problem. To loop through the "selected" list use the List property

dim Counter as integer
    Counter = 0 
    For Counter = 0 to SelectedLst.ListCount-1
        PathToNextDB = TextBox1.Text & "\" & SelectedList.List(Counter)
        ' That gets you to the next DB on the list
    next counter