Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2010
    Location
    London, UK
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I am currently working on a project where I need to pass back data from excel to access.

    The macro below move data to access by adding a new row into a table called data.

    What I need is the code that will allow me to check to see if the ID is already in the database and if so delete that row out and then add the new row using the macro I have already built.

    So it is basically if ID exists then delete row in access and then add new row with updated data. Or if ID doesn't exist then just add the data to access.

    Code:
    Sub AppendRecord()
    
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    
    strDatabaseLocation = "W:\Operations\London\UA & UST Shared Drive\Development\PFC & UFC Data.mdb"
    Set DB = DAO.DBEngine.Workspaces(0).OpenDatabase(strDatabaseLocation, False, False)
    Set RS = DB.OpenRecordset("Data")
    
        RS.AddNew
        RS![ID] = Worksheets("Upload").Range("A" & countera)
        RS![Date PFC First Created] = Worksheets("Upload").Range("B" & countera)
        RS![Date  Completed] = Worksheets("Upload").Range("C" & countera)
        RS![UA] = Worksheets("Upload").Range("D" & countera)
        RS![Last Updated By] = Worksheets("Upload").Range("E" & countera)
        RS![Written Date] = Worksheets("Upload").Range("F" & countera)
        RS![Quote UW Initials] = Worksheets("Upload").Range("G" & countera)
        RS![Product/COB] = Worksheets("Upload").Range("H" & countera)
        RS![Skeleton Set up?] = Worksheets("Upload").Range("I" & countera)
        RS![New/Renewal] = Worksheets("Upload").Range("J" & countera)
        RS![QQ/MIT Reference] = Worksheets("Upload").Range("K" & countera)
        RS![Regional Office] = Worksheets("Upload").Range("L" & countera)
        RS![Insured] = Worksheets("Upload").Range("M" & countera)
        RS![Slip/Contract 'quick glance' Completed?] = Worksheets("Upload").Range("N" & countera)
        RS![Line Of] = Worksheets("Upload").Range("O" & countera)
        RS![Second Line Of Stamp On The Slip?] = Worksheets("Upload").Range("P" & countera)
        RS![Are All Values Entered Correctly on QQ/MIT?] = Worksheets("Upload").Range("Q" & countera)
        RS![Workflow Set Up By?] = Worksheets("Upload").Range("R" & countera)
        RS![2nd Line Complete?] = Worksheets("Upload").Range("S" & countera)
        RS![Ref No] = Worksheets("Upload").Range("T" & countera)
        RS![Bureau?] = Worksheets("Upload").Range("U" & countera)
        RS![Bureau Stamp] = Worksheets("Upload").Range("V" & countera)
        RS![System] = Worksheets("Upload").Range("W" & countera)
        RS![Reference] = Worksheets("Upload").Range("X" & countera)
        RS![Portfolio/COB] = Worksheets("Upload").Range("Y" & countera)
        RS![Written Line] = Worksheets("Upload").Range("Z" & countera)
        RS![SCOB] = Worksheets("Upload").Range("AA" & countera)
        RS![SSCOB/COB2 or Sub Portfolio] = Worksheets("Upload").Range("AB" & countera)
        RS![RI Code] = Worksheets("Upload").Range("AC" & countera)
        RS![Trade Code] = Worksheets("Upload").Range("AD" & countera)
        RS![RI Type] = Worksheets("Upload").Range("AE" & countera)
        RS![Country Code] = Worksheets("Upload").Range("AF" & countera)
        RS![Financed] = Worksheets("Upload").Range("AG" & countera)
        RS![Claims?] = Worksheets("Upload").Range("AH" & countera)
        RS![Broker Contact] = Worksheets("Upload").Range("AI" & countera)
        RS![Wording Status] = Worksheets("Upload").Range("AJ" & countera)
        RS![Policy Issuance Required?] = Worksheets("Upload").Range("AK" & countera)
        RS![Where Policy Issuance Details] = Worksheets("Upload").Range("AL" & countera)
        RS![Transaction Type] = Worksheets("Upload").Range("AM" & countera)
        RS![Contract Certainty] = Worksheets("Upload").Range("AN" & countera)
        RS![Benchmark] = Worksheets("Upload").Range("AO" & countera)
        RS![Rate Change] = Worksheets("Upload").Range("AP" & countera)
        RS![Fixed Exrate?] = Worksheets("Upload").Range("AQ" & countera)
        RS![LODRA] = Worksheets("Upload").Range("AR" & countera)
        RS![Wages / Turnover] = Worksheets("Upload").Range("AS" & countera)
        RS![LRC] = Worksheets("Upload").Range("AT" & countera)
        RS![Is Additional Genius Question Sheet Available?] = Worksheets("Upload").Range("AU" & countera)
        RS![Additional Notes For Information] = Worksheets("Upload").Range("AV" & countera)
        RS![Complete?] = Worksheets("Upload").Range("AW" & countera)
        RS![UA Query Raised?] = Worksheets("Upload").Range("AX" & countera)
        RS![UA Query Area One] = Worksheets("Upload").Range("AY" & countera)
        RS![UA Reason One] = Worksheets("Upload").Range("AZ" & countera)
        RS![UA Date Queried One] = Worksheets("Upload").Range("BA" & countera)
        RS![UA Date Resolved One] = Worksheets("Upload").Range("BB" & countera)
        RS![UA Query Area Two] = Worksheets("Upload").Range("BC" & countera)
        RS![UA Reason Two] = Worksheets("Upload").Range("BD" & countera)
        RS![UA Date Queried Two] = Worksheets("Upload").Range("BE" & countera)
        RS![UA Date Resolved Two] = Worksheets("Upload").Range("BF" & countera)
        RS![UA Query Area Three] = Worksheets("Upload").Range("BG" & countera)
        RS![UA Reason Three] = Worksheets("Upload").Range("BH" & countera)
        RS![UA Date Queried Three] = Worksheets("Upload").Range("BI" & countera)
        RS![UA Date Resolved Three] = Worksheets("Upload").Range("BJ" & countera)
        RS![Query Raised?] = Worksheets("Upload").Range("BK" & countera)
        RS![Returned To UWA Via Workflow?] = Worksheets("Upload").Range("BL" & countera)
        RS![Date Query Raised] = Worksheets("Upload").Range("BM" & countera)
        RS![Query Field One] = Worksheets("Upload").Range("BN" & countera)
        RS![Reason One] = Worksheets("Upload").Range("BO" & countera)
        RS![Agreed Value One] = Worksheets("Upload").Range("BP" & countera)
        RS![Query Field Two] = Worksheets("Upload").Range("BQ" & countera)
        RS![Reason Two] = Worksheets("Upload").Range("BR" & countera)
        RS![Agreed Value Two] = Worksheets("Upload").Range("BS" & countera)
        RS![Query Field Three] = Worksheets("Upload").Range("BT" & countera)
        RS![Reason Three] = Worksheets("Upload").Range("BU" & countera)
        RS![Agreed Value Three] = Worksheets("Upload").Range("BV" & countera)
        RS![Query Raised By] = Worksheets("Upload").Range("BW" & countera)
        RS![Underwriting_Presentation] = Worksheets("Upload").Range("BX" & countera)
        RS![Underwriting_Rational] = Worksheets("Upload").Range("BY" & countera)
        RS![Pricing_Methodology] = Worksheets("Upload").Range("BZ" & countera)
        RS![Slip_Contract] = Worksheets("Upload").Range("CA" & countera)
        RS![Additional_Discussion_Notes] = Worksheets("Upload").Range("CB" & countera)
        RS![Wording] = Worksheets("Upload").Range("CC" & countera)
        RS![Evidence_of_Referreal_Sign_Off] = Worksheets("Upload").Range("CD" & countera)
        RS![Evidence_of_Outward_Prot] = Worksheets("Upload").Range("CE" & countera)
        RS![Subjectivities1] = Worksheets("Upload").Range("CF" & countera)
        RS![Subjectivities2] = Worksheets("Upload").Range("CG" & countera)
        RS![Subjectivities3] = Worksheets("Upload").Range("CH" & countera)
        RS![Subjectivities4] = Worksheets("Upload").Range("CI" & countera)
        RS![Final_ADJ_Prev_Period] = Worksheets("Upload").Range("CJ" & countera)
        RS![Risk_Surveys] = Worksheets("Upload").Range("CK" & countera)
        RS![UK_Terrorism_Quote] = Worksheets("Upload").Range("CL" & countera)
        RS![Final_ADJ_This_Period] = Worksheets("Upload").Range("CM" & countera)
        RS![PPW_Date_inst1] = Worksheets("Upload").Range("CN" & countera)
        RS![PPW_Date_inst2] = Worksheets("Upload").Range("CO" & countera)
        RS![PPW_Date_inst3] = Worksheets("Upload").Range("CP" & countera)
        RS![Underlying_terms] = Worksheets("Upload").Range("CQ" & countera)
        RS![Wages/Turnover] = Worksheets("Upload").Range("CR" & countera)
        RS![Benchmark_Req] = Worksheets("Upload").Range("CS" & countera)
        RS![Rate_Change_Req] = Worksheets("Upload").Range("CT" & countera)
        RS![Other_Information] = Worksheets("Upload").Range("CU" & countera)
        RS.Update
        RS.Close
            
    Set RS = Nothing
    Set DB = Nothing
    End Sub

    Thanks,
    Craig

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Unfortunately, I have forgotten what little I knew about DAO syntax...

    If the add fails, is it okay that the prior record was already deleted? In that case, I suggest running a query for that ID and, if the results are not empty, delete the record. This page on MSDN has some sample code for deleting from a DAO recordset: How to: Delete a Record From a DAO Recordset [Access 2007 Developer Reference].

    If you would prefer to keep the old record if the add is not successful, then a more complicated approach would be required.

  4. #3
    New Lounger
    Join Date
    Oct 2010
    Location
    Canton, MI
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just saw this 10-13-2010. Reply if you still need the code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •