I am importing data from excel (file name is ImportToAccess.xlsx)
It works fine.
I now only need to import certain records.
The records I would not import would be if there is a match on certain fields.
The fields are:
[Trade #] property is short text
[Buy CP] property is short text
[Quantity BBLS] property is number
[Batch] property is short text
How could I modify the attached code to only import records that do not match between ImportToAccess.xlsx and the file in access that is named the tblImportToAccess.
I am currently deleting all data from tblImportToAccess before I import and I think that part would have to be modified.
I hope that makes sense.
Let me know if you require more information. FYI: I am a novice user of Access and do not have much coding experience.
below please find the code:
Private Sub btnImport_Click_Click()
If MsgBox("Do you want to Import and Delete data?", vbYesNo) <> vbYes Then
CurrentDb.Execute "delete * from [tblImportToAccess]"
Dim strName As String
Dim xlApp As Object
Dim xlWB As Object
Set xlApp = New Excel.Application
.Visible = False
Set xlWB = .Workbooks.Open("C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", , False)
strName = "ToImport"
Set xlWB = Nothing
Set xlApp = Nothing
DoCmd.TransferSpreadsheet acImport, , "tblImportToAccess", "C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", True, strName & "!"
MsgBox "Data has been imported. The table will now appear and need to check for accuracy.", vbInformation
On Error GoTo EH
If Err.Number = 2450 Then
'resume next 'don't know what this is for.
ElseIf Err.Number <> 0 Then
MsgBox "Error " & Err.Number & ": " & Err.Description