Importing Data from many Databases into one

[Dana123]

Hi everyone, I am trying to get some specific values from a set of access databases, there is a file, within this file there are more than 100 folders, each folder contains 2 databases from which I am willing to import only 2 tables from one and 2 tables from the other, all 100 folders contain the same database structure only a different ID nr.
Until now I have been able to loop through the files and filter if the value on Field "F22" is "JA". Now I want to get the values from specific columns and loop these until there are no more values and continue with the next database.

Hope u understand what I mean, i am not a programmer and this is my first time working with VBA

Sub LoopThroughFilePaths()
    Dim myArr
    Dim i As Long
    Dim j As Long
    Dim MyFile As String
    Dim Tablename As String
    Dim Table1 As String
    Dim Table2 As String
    Dim Aktdb As DAO.Database
    Dim AktVdb As DAO.Database
    Dim AktRow
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim Bez1 As Recordset
    Dim Bez2 As Recordset
    Dim strFeld As String
    Dim intFeldPos As Integer
    Const strPath As String = "C:\PATH..."
    Table1 = "_Ist1"
    Table2 = "_Ist2"
    Counter = 0
    myArr = GetSubFolders(strPath)
        For j = 1 To UBound(myArr)
            MyFile = Dir(myArr(j) & "\*.mdb")
            'In myFile is the.mdb
            Tablename = Replace(MyFile, ".mdb", "")
            Set Aktdb = OpenDatabase(strPath + Tablename + "\" + MyFile)
            Set AktVdb = OpenDatabase(strPath + Tablename + "\V" + MyFile)
            Set rs1 = Aktdb.OpenRecordset("Ist1", dbOpenDynaset)
            Set rs2 = Aktdb.OpenRecordset("Ist2", dbOpenDynaset)
            Set Bez1 = AktVdb.OpenRecordset("Bez1", dbOpenDynaset)
            Set Bez2 = AktVdb.OpenRecordset("Bez2", dbOpenDynaset)
            Do While Not rs1.EOF
                strFeld = rs1![F22]
                intFeldPos = InStr(strFeld, "Ja")
                If intFeldPos > 0 Then
                    strFeld = Replace(strFeld, " ", "")
                    'MsgBox "Found"
                Else
                    intFeldPos = InStr(strFeld, "Nein")
                    If intFeldPos > 0 Then
                     strFeld = Replace(strFeld, " ", "")
                     'MsgBox "notfound"
                     Exit Sub
                End If
                End If
                If strFeld = "Ja" Then
 
 
 
 
                End If
                rs1.MoveNext
            Loop

[jimatqsi]

"Now I want to get the values from specific columns and loop these until there are no more values and continue with the next database. Hope u understand what I mean,"

No, I don't understand what you mean. What does "loop these" mean? You already have the looping through the rows down okay.

What are the "specific columns" you are interested in? What do you want to do with them?

Also,

For j = 1 To UBound(myArr)

should be

For j = 0 To UBound(myArr)-1

because the first element of an array is (0) unless you declare it differently.

Please use the [Code/] button to put any vba code in between Code tags for easier reading.

Jim

[Dana123]

Hi thanx for the reply, the specific columns are in Database1(Aktdb): table Ist1 F1-F22, from table Ist2 column f51-f64
and Database2(AktVdb): from Table Bez1 F1-F22 and Bez2 Column F51-F64
I want to have all the information read from those folders merged into one only database having a table pro folder

[jimatqsi]

It's not very clear to me what exactly you want. But the way to move data from one recordset to another is like this:

rs1.edit
    rs1!fieldname = rs2!fieldname
    rs1!fieldname2 = rs2!fieldnamewhatever
    ....
rs1.update
 

Jim

[Dana123]

Thank you! I will try to make it work, I'll keep you posted.