none
Update An Access Table from an Excel Macro Button RRS feed

  • Question

  • I am a ColdFusion programmer who has very little VBA expertise. I have an Excel worksheet with a macro button on it that opens an Outlook email, all nicely filled in and with the file Attached, ready to click Send. Someone else wrote it. The name of the file is something like MyExcelFile_P-nnnnn.xlsx where nnnnn is a five-digit number.

    I want to add some language to it which will update a field in an Access database table. If I were to write this in ColdFusion, it would look something like this:

    <cfquery name="UpdateRecord" datasource="MYDatabase">

      UPDATE
        MYTable
      SET
        MyField = Now()
      WHERE
        FieldName = nnnnn <!---that five-digit number noted above--->

    </cfquery>

    Can someone either point me to a tutorial with language I could adapt for this, or just write it for me? It seems like it should be pretty simple for a VBA person, though stripping out that nnnnn may be a bit tricky. Thanks!!

    Tuesday, October 16, 2018 6:50 PM

All replies

  • Is ColdFusion still around? Apparently!

    UPDATE
        MYTable
      SET
        MyField = Now()
      WHERE
        FieldName = nnnnn

    This seems to indicate a table with a separate field for each value of n. That is a REALLY BAD IDEA in any database language I can think of. Maybe you meant:

      WHERE myPrimaryKeyField = nnnnn

    Can you confirm?


    -Tom. Microsoft Access MVP

    Wednesday, October 17, 2018 3:38 AM