Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Stop import if certain criteria exists

I have the following code which executes a saved import.

Private Sub cmdImportData_Click()


    If MsgBox("This function will import the named range of your Excel file and append the data table.  You must be sure to do this only one time for each new Excel file.  Do you wish to continue??", vbYesNo + vbQuestion + vbDefaultButton2) = vbYes Then
        DoCmd.RunSavedImportExport "ImportData"
        MsgBox "The data from the Excel file has successfully been imported."
    Else
        Exit Sub
    End If

End Sub

Open in new window


But if a record, or row in the Excel file, containing for example "5/8/2011" in a field named "Week starting date" in the Excel file and in the resulting Access table already exists I do not want that record to be imported.

How can I do this?

--Steve
0
SteveL13
Asked:
SteveL13
1 Solution
 
Rey Obrero (Capricorn1)Commented:
if that is the case, you have to import the excel file into a temp table, then create an append query like this

insert into FinalTable
select tempTable.*
from temptable left join FinalTable on temptable.[fieldname]=FinalTable.[fieldname]
where FinalTable.[fieldname] is null
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Or use OLE automation to control Excel directly and do the import.

 I would use Rey's approach however as it's far simpler.   But I thought I'd mention it for future reference in case you find your needs getting a little more complex.

Jim.
0
 
SteveL13Author Commented:
Rey,  That suggestion works perfectly.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now