vba code to insert a field value from an access table into a sql table based on criteria

Posted on 2011-05-13
Last Modified: 2013-11-27
I have an access table called "ReceiverNoTableFinal" and a sql table called "iitdata".  The ReceiverNoTableFinal has three fields called "IITReportNo (no duplicates)", "IITDate", and "ReceiverNo (text)".  

The "iitdata" table has the same three fields and additional other fields.  The "iitdata" table records are uniquely identified by the field "IITReportNo (no duplicates)".  

What I need help is to use VBA to insert values of "ReceiverNo" FROM the "ReceiverNoTableFinal" INTO the "ReceiverNo" field of the "iitdata" table wherever the IITReportNo values MATCH in both tables.  Also the insertion has to occur IF the ReceiverNo field is null in the "iitdata" table.  Could really use expert help with this.
Question by:sxxgupta
    LVL 119

    Accepted Solution

    so you mean to update the sql table "iitdata"

    dim uSql as string
    uSql="update iitdata inner join ReceiverNoTableFinal"
    uSql=usql & " on iitdata.IITReportNo=ReceiverNoTableFinal.IITReportNo"
    usql=usql & " set iitdata.ReceiverNo=ReceiverNoTableFinal.ReceiverNo"
    usql=usql & " where iitdata.ReceiverNo is null"

    currentdb.execute usql,dbseechanges

    Author Closing Comment

    Thanks Cap!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now