[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2011-05-13
Medium Priority
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 120

Accepted Solution

Rey Obrero (Capricorn1) earned 2000 total points
ID: 35753689
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

ID: 35754204
Thanks Cap!

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

873 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