locked
VBA - Change a Record in an Access Table from Excel RRS feed

  • Question

  • Hi All,

    I have managed to be able to add a new Record to a table in Access, But is it possible to be able to find a record using the primary key and then change a field value from Excel?

    EG: My Access Database, Table1, Field QTY = Field QTY - Excel Variable.

    Thanks

    Tom

    Wednesday, February 8, 2012 9:16 PM

Answers

  • I seen your other post and Im guessing your using DAO so this should work

    strSQL = "SELECT QTY FROM TABLENAME WHERE (((PrimaryKeyField)=1));"

            rst.Edit
            rst.Fields("QTY").Value = NewQtyValue
            rst.Update




    • Edited by Mike7952 Wednesday, February 8, 2012 9:55 PM
    • Marked as answer by t0m46 Thursday, February 9, 2012 9:32 AM
    Wednesday, February 8, 2012 9:47 PM

All replies

  • How are you connecting to the database DAO or ADO?
    Wednesday, February 8, 2012 9:20 PM
  • I seen your other post and Im guessing your using DAO so this should work

    strSQL = "SELECT QTY FROM TABLENAME WHERE (((PrimaryKeyField)=1));"

            rst.Edit
            rst.Fields("QTY").Value = NewQtyValue
            rst.Update




    • Edited by Mike7952 Wednesday, February 8, 2012 9:55 PM
    • Marked as answer by t0m46 Thursday, February 9, 2012 9:32 AM
    Wednesday, February 8, 2012 9:47 PM
  • Hi Thanks Mike, I am using DAO, Sorry forgot to say.

    That works fine. Thanks

    Thursday, February 9, 2012 9:32 AM