Importing all records from multiple files (.accde)

[MikeLeBen]

I've spent several hours searching the internet but haven't found a proper solution yet.

I have a few hundred access databases all with the same tables and structure, and want to import all the data in a 'mother' database (which i created with the same tables and structure).

I am using a sub that feeds a string to another public sub as follows:

Private Sub cmdImport_Click()

Dim strFolder   As String  'source folder path for import
Dim strFile     As String  'individual file path

strFolder = GetFolder(Environ$("USERPROFILE") & "\Documenti\")

If Len(strFolder) <= 1 Then Exit Sub
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\" 'adds trailing backslash
strFile = Dir$(strFolder & "*.accde")

Do While Len(strFile) > 0

Call AppendAll(strFolder & strFile, "tblRispCheckLocal", "tblRispCheck")

strFile = Dir$()

Loop

End Sub

Where I would like my sub AppendAll to do the job:

Public Sub AppendAll(strDBName As String, strTableName As String, strNewTableName As String)

Dim MySQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = OpenDatabase(strDBName)
Set rs = db.OpenRecordset(strNewTableName)

MySQL = "INSERT INTO " & strTableName & _
        "???" 'should I use recordsets? I failed to use SQL to access data on other files, but if there's a way it would be optimal!

rs.Close
Set rs = Nothing

End Sub

 

[Mihail]

This is not a solution. It is only a idea (pseudo-code).

For Each OtherDB
  With OtherDB
    For Each Record in OtherDB
      Store the field's values in local variables
    Next Record
  End With
  With CurrentDB
     Add new record using stored values (by using .AddNew method)
  End With
Next OtherDB

 

[MikeLeBen]

This is not a solution. It is only a idea (pseudo-code).

For Each OtherDB
  With OtherDB
    For Each Record in OtherDB
      Store the field's values in local variables
    Next Record
  End With
  With CurrentDB
     Add new record using stored values (by using .AddNew method)
  End With
Next OtherDB

Indeed my final solution can be represented by this pseudo-code. Here it is in case anyone will find themselves in my same situation one day:

Public Sub AppendAll(strDBName As String, strTableName As String, strNewTableName As String)

Dim dbLocal As DAO.Database
Dim rsLocal As DAO.Recordset
Dim dbForeign As DAO.Database
Dim rsForeign As DAO.Recordset

Dim fldCount As Integer
Dim fldName As String

Set dbLocal = CurrentDb
Set rsLocal = dbLocal.OpenRecordset(strTableName)
Set dbForeign = OpenDatabase(strDBName, dbOpenDynaset)
Set rsForeign = dbForeign.OpenRecordset(strNewTableName)

If Not (rsForeign.BOF And rsForeign.EOF) Then
    If rsForeign.RecordCount <> 0 Then
        
        rsForeign.MoveFirst
        Do While Not rsForeign.EOF
        
            rsLocal.AddNew
            
            For fldCount = 1 To (rsForeign.Fields.Count - 1)
                fldName = rsLocal.Fields(fldCount).Name
                rsLocal.Fields(fldName) = rsForeign.Fields(fldName)
            Next
            
            rsLocal.Update
            rsForeign.MoveNext
            
        Loop
        
    End If
End If

rsForeign.Close
dbForeign.Close
Set rsForeign = Nothing
Set dbForeign = Nothing

rsLocal.Close
dbLocal.Close
Set rsLocal = Nothing
Set dbLocal = Nothing

End Sub

And this is the call

Private Sub cmdImport_Click()

Dim strFolder   As String  'percorso cartella questionari compilati
Dim strFile     As String  'percorso singolo questionario

strFolder = GetFolder(Environ$("USERPROFILE") & "\Documenti\")

If Len(strFolder) <= 1 Then Exit Sub
If Right(strFolder, 1) <> "\" Then strFolder = strFolder & "\" 'aggiunge slash per la stringa folder
strFile = Dir$(strFolder & "*.accde")

Do While Len(strFile) > 0

Call AppendAll(strFolder & strFile, "tblRispCheckLocal", "tblRispCheck")
'Call AppendAll(strFile, "tblRispMemoLocal", "tblRispMemo")
'Call AppendAll(strFile, "tblRisposteLocal", "tblRisposte")

strFile = Dir$()

Loop

End Sub

 

[Mihail]

Glad to help you, even that now I see a error in my "code".
Should be:

For Each OtherDB
   With OtherDB
     For Each Record in OtherDB
       Store the field's values in local variables
       With CurrentDB
          Add new record using stored values (by using .AddNew method)
       End With
     Next Record
   End With
Next OtherDB