dynamic database from multiple excels RRS feed

  • Question

  • Hello,

    I am looking to merge multiple excels into one database. However, the excels would change (data rows being added). 

    I can create the database using the append function in Access and I can create a link to the excel for the dynamism. But how to I combine the two? 

    Specifically: How do I create a single database which merges multiple excels and reflects the latest data when the any/all of the base excel data changes.



    Sunday, May 19, 2019 4:18 AM

All replies

  • Link the worksheets (or Named Ranges).

    When the linked tables are read, the current data from the workbooks will be read.

    Gustav Brock

    Sunday, May 19, 2019 8:48 AM
  • Hi Gustav,

    Thank you for your reply. However, I don't seem to follow. Are you suggesting I individually link the excels? But how will I create a single merged database?

    To elaborate, I have monthly files, say, Jan, Feb etc. I want to create one single DB which appends Jan, Feb etc and call it say RawData. And any changes made to the Jan/Feb excel should reflect in the RawData DB



    Monday, May 20, 2019 3:58 AM
  • You could either "merge" the month sheets using a union query which you modify (expand) month by month, or you could - as the workbooks arrive - run an append query that copies (appends) the content to a single table.

    Gustav Brock

    Monday, May 20, 2019 7:06 AM
  • sorry for being ignorant.

    I appended the files to create one DB . If I link the excels, I get individual files 

    Any modification to the base data modifies the individual files which is good.

    However, I am still unable to get the modification reflected in the appended data. So the question remains as to how to both append and link multiple files into one single DB

    Monday, May 20, 2019 8:44 AM
  • Then either use the union query, not a table, as the source. That will work, but maybe slow.

    Or, run and append/update query when a workbook is updated to append new records to the table or update these. It requires, though, that the tables (worksheets) have a unique key.

    This old tip from Smart Access is one of my favourites:

    Update and Append Records with One Query

    By Alan Biggs

    Did you know that you can use an update query in Access to both update
    and add records at the same time? This is useful if you have two
    versions of a table, tblOld and tblNew, and you want to integrate the
    changes from tblNew into tblOld.

    Follow these steps:

    1. Create an update query and add the two tables. Join the two tables
    by dragging the key field of tblNew onto the matching field of tblOld.

    2. Double-click on the relationship and choose the join option that
    includes all records from tblNew and only those that match from

    3. Select all the fields from tblOld and drag them onto the QBE grid.

    4. For each field, in the Update To cell type in tblNew.FieldName,
    where FieldName matches the field name of tblOld.

    5. Select Query Properties from the View menu and change Unique
    Records to False. (This switches off the DISTINCTROW option in the SQL
    view. If you leave this on you'll get only one blank record in your
    results, but you want one blank record for each new record to be added
    to tblOld.)

    6. Run the query and you'll see the changes to tblNew are now in

    This will only add records to tblOld that have been added to tblNew.
    Records in tblOld that aren't present in tblNew will still remain in

    Gustav Brock

    Monday, May 20, 2019 8:57 AM
  • There are so many ways to go with this.  You could import separate Excel files into separate Access Tables, and then combing those multiple tables into a single table using an Union Query.  Here is some VBA to import data from multiple Excel files.

    Private Sub Command0_Click()
      Dim strFolder As String
        Dim strfile As String
        Dim strTable As String
        Dim lngPos As Long
        Dim strExtension As String
        Dim lngFileType As Long
        Dim strSQL As String
        With Application.FileDialog(4) ' msoFileDialogFolderPicker
            If .Show Then
                strFolder = .SelectedItems(1)
                MsgBox "No folder specified!", vbCritical
                Exit Sub
            End If
        End With
        If Right(strFolder, 1) <> "\" Then
            strFolder = strFolder & "\"
        End If
        strfile = Dir(strFolder & "*.xls*")
        Do While strfile <> ""
            lngPos = InStrRev(strfile, ".")
            strTable = Left(strfile, lngPos - 1)
            strExtension = Mid(strfile, lngPos + 1)
            Select Case strExtension
                Case "xls"
                    lngFileType = acSpreadsheetTypeExcel9
                Case "xlsx", "xlsm"
                    lngFileType = acSpreadsheetTypeExcel12Xml
                Case "xlsb"
                    lngFileType = acSpreadsheetTypeExcel12
            End Select
           DoCmd.TransferSpreadsheet _
                TransferType:=acImport, _
                SpreadsheetType:=lngFileType, _
                TableName:=strTable, _
                FileName:=strFolder & strfile, _
                HasFieldNames:=True ' or False
            strfile = Dir
    End Sub

    Also, consider this.

    Private Sub Command0_Click()
    Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly 
    As Boolean
    Dim lngCount As Long
    Dim objExcel As Object, objWorkbook As Object
    Dim colWorksheets As Collection
    Dim strPathFile as String, strTable as String
    Dim strPassword As String

    ' Establish an EXCEL application object
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
          Set objExcel = CreateObject("Excel.Application")
          blnEXCEL = True
    End If
    On Error GoTo 0

    ' Change this next line to True if the first row in EXCEL worksheet
    ' has field names

    blnHasFieldNames = False

    ' Replace C:\Filename.xls with the actual path and filename
    strPathFile = "C:\Filename.xls"

    ' Replace tablename with the real name of the table into which
    ' the data are to be imported

    strTable = "tablename"

    ' Replace passwordtext with the real password;
    ' if there is no password, replace it with vbNullString constant
    ' (e.g., strPassword = vbNullString)

    strPassword = "passwordtext"

    blnReadOnly = True ' open EXCEL file in read-only mode

    ' Open the EXCEL file and read the worksheet names into a collection
    Set colWorksheets = New Collection
    Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
    For lngCount = 1 To objWorkbook.Worksheets.Count
          colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
    Next lngCount

    ' Close the EXCEL file without saving the file, and clean up the EXCEL objects
    objWorkbook.Close False
    Set objWorkbook = Nothing
    If blnEXCEL = True Then objExcel.Quit
    Set objExcel = Nothing

    ' Import the data from each worksheet into the table
    For lngCount = colWorksheets.Count To 1 Step -1
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                strTable, strPathFile, blnHasFieldNames, colWorksheets(lngCount) & "$"
    Next lngCount

    ' Delete the collection
    Set colWorksheets = Nothing

    ' Uncomment out the next code step if you want to delete the
    ' EXCEL file after it's been imported
    ' Kill strPathFile
    End Sub


    Wednesday, June 5, 2019 2:35 AM