Import and update access from excel (1 Viewer)

[ili_sophia]

Hi There,

I have an access userform that allows the users to import in their excel worksheet and it will append the data to the tables.

However, now I am facing the problem of updating the table.
In the case where the users have already uploaded the worksheets into access but suddenly finds an error in some of the fields of excel worksheet, they would need to make changes to the data.

Is there a way to allow the users to reimport the excel worksheet and it will update the access table?
can it match the excel and access table through the primary key and update the changes in the field?

My module code:
Option Compare Database
Option Explicit

Public Sub ImportExcelSpreadsheet(fileName As String, tableName As String)
On Error GoTo BadFormat
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, tableName, fileName, True
Exit Sub

BadFormat:
MsgBox "The file you tried to import was not an Excel Spreadsheet."

End Sub


My browse button on the userform:
Private Sub btnSettingBrowse_Click()
Dim diag As Office.FileDialog
Dim item As Variant


Set diag = Application.FileDialog(msoFileDialogFilePicker)
diag.AllowMultiSelect = False
diag.Title = "Please select an Excel Spreadsheet"
diag.Filters.Clear
diag.Filters.Add "Excel Spreadsheet", "*.xls, *.xlsx"

If diag.Show Then

For Each item In diag.SelectedItems
Me.SettingFileName = item
Next

End If
End Sub

My import excel worksheet on the userform:
Private Sub btnSettingImportSpreadsheet_Click()
On Error GoTo ErrorMessage

Dim FSO As New FileSystemObject


If Nz(Me.SettingFileName, "") = "" Then
MsgBox "Please Select a File"
Exit Sub
End If

If FSO.FileExists(Nz(Me.SettingFileName, "")) Then

If MsgBox("Do you want to import this file?", vbYesNo) = vbYes Then


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Setting", Me.SettingFileName, True, SettingComboBox & "!"


MsgBox "File Imported"

ErrorMessage:
If Err = 2391 Then
MsgBox "Sorry, you have selected the wrong file to import. Please select the correct file. Thank You."
Exit Sub



End If
End If
End If

DoCmd.SetWarnings False

End Sub

 

[Pat Hartman]

If Excel is the "master", then use a BatchID when you import a spreadsheet. That will allow the user to delete batch 482 and reimport it. You won't have to worry about what got changed but the bigger problem was realy figuring out what got added and what got deleted not what got changed.

 

[arnelgp]

excel don't have Primary Key so this one is ruled out.
also there is no way to determine whether the rows from excel file is being retrieved sequencially or randomly.


what i would do is add another yes/no field (Validated). set this field to Yes if all the rows from your excel has been successfully imported and been verified to be correct. otherwise, you may delete the record which this field has not been marked as Yes.


you may also elect to save the import first to temporary table for verification and validation and insert it later to the final table.

 

[Cronk]

If each spreadsheet is being imported into a unique table, you could simply,
if the table already exists,delete all the existing records in the table.

If you are appending excel data to the same table, there's 2 ways I can think of to handle it if you are restricted to using docmd.transferspreadsheet.

(1) Add a timestamp field to your table, prompt the user to enter the date/time when the previous import took place, delete those entries with that timestamp and then import the spreadsheet.

(2) If you have a unique identifier like an email address, import the spreadsheets into an empty temporary and delete those records before importing into the table.