dynamic database from multiple excels

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.

Regards

Shalabh

Answer 1

Link the worksheets (or Named Ranges).

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

---

Gustav Brock

Answer 2

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

Regards

Shalabh

Answer 3

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

Answer 4

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

Answer 5

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
tblOld.

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
tblOld.

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
tblOld.

---

Gustav Brock

Answer 6

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)
        Else
            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
    Loop
    
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
Err.Clear
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, , _
      strPassword)
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

---

MY BOOK