CODEWITHSUNDEEP
excelvbams-access
Sophie
Sophie

Reputation: 1

How to update an existing access table

I created an Excel macro, where every week employees update it. From it, I created an Access form, where employees can browse the file and update it in a Access table, to create a database.

However I am facing a problem, when updating the file through the Access form, a new table is being created instead of being updated.

Therefore instead of having one database, I end up with 4 different table

My Module is as follow : 

Public Sub ImportexcelSpreadsheet(filename As String, tablename As String)
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tablename, filename, True, "Database!"
    MsgBox " Importation Database done", vbInformation

End Sub

My browse btn is as follow: 

Private Sub btnBrowse_Click()

    Dim diag As Office.FileDialog
    Dim item As Variant

    Set diag = Application.FileDialog(msoFileDialogFilePicker)
    diag.AllowMultiSelect = False
    diag.Title = "please select an excel spreadhseet"
    diag.Filters.Clear
    diag.Filters.Add "Excel Spreadsheets", "*.xls, *.xlsx, *.xlsm"

    If diag.Show Then
        Me.txtFileName = diag.SelectedItems(1)
    Else
    MsgBox "This is not a excel spreasheet"
    End If
End Sub

My import file is as follow : 

Private Sub Import_Click()

    Dim FSO As New FileSystemObject

    If FSO.FileExists(Me.txtFileName) Then

        Module1.ImportexcelSpreadsheet Me.txtFileName, FSO.GetFileName(Me.txtFileName)
    Else
        MsgBox "the file you tried to import was not found"
    End If

End Sub

The code is working properly. But I would like to have one common database, rather than a table per week.

Upvotes: 0

Views: 126

Answers (2)

Siyon DP
Siyon DP

Reputation: 514

The table name is set by you to the short file name

ImportexcelSpreadsheet Me.txtFileName, FSO.GetFileName(Me.txtFileName)

You should rename one of the tables to a significant name 'MyBigTable'

Then 

ImportexcelSpreadsheet Me.txtFileName, MyBigTable

Upvotes: 0

Sam
Sam

Reputation: 5721

The easiest way to approach this is to import it into a temporary table and work from there. Something like this (untested, but I hope the idea is clear):

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "temptable", filename, True, "Database!"
CurrentDb.Execute "INSERT INTO " & tablename & " SELECT * FROM temptable"
CurrentDb.TableDefs.Delete "temptable"

Upvotes: 1

Related Questions