Solved

Stop import if certain criteria exists

Posted on 2015-01-17
3
224 Views
Last Modified: 2015-01-17
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
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40555191
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
 
LVL 58
ID: 40555197
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
 

Author Closing Comment

by:SteveL13
ID: 40555220
Rey,  That suggestion works perfectly.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question