Update If EXISTS, Else INSERT

[Only4Access]

Hi forum,



I have a table tblAct with 4 fields

ActNo: Text (PK)
RefID : Number
SiteID: Number
RollID : Number

I have another temporary table for data entry (for entering bulk data) with 11 fields. This tbl will have 4 fields form tblAct & other fields. So through this table appending multiple data to master table.

What i am trying to achieve is, If ActNo is Not EXISTS in tblAct, then INSERT INTO tblAct, else UPDATE it's correspondence field. Currently I am doing this

Dim strSQL as String

strSQL = "INSERT INTO tblAct (ActNo, RefID, SiteID, RollID) & _
           "SELECT ActNo, RefID, SiteID, RollID FROM tblTemp " & _
           "WHERE ActNo NOT IN (SELECT ActNo FROM tblAct)"

CurrentDb.Execute strSQL, dbFailOnError

How can i include UPDATE in this SQL, if already exists?

Thank you for your help

[JoeM]

I don't think do both UPDATE and INSERT in a single SQL Query.
Just create a second SQL script to do you Update query, and you can have your VBA run both of them, "back-to-back" (I would do the UPDATE first, and the INSERT second).

[Only4Access]

Thank you Joe for your quick reply.

I am a newbie in access. I will have multiple records in tbltemp, so the below should work?

Dim strSQLInsert As String
Dim strSQLUpdate As String


strSQLInsert = "INSERT INTO tblAct (ActNo, RefID, SiteID, RollID)" & _
               "SELECT ActNo, RefID, SiteID, RollID FROM tblTemp " & _
               "WHERE ActNo NOT IN (SELECT ActNo FROM tblAct)"


strSQLUpdate = "UPDATE tblAct SET ActNo = [tblTemp].[ActNo], RefID = [tblTemp].[RefID]" & _
               "SiteID = [tblTemp].[SiteID], RollID = [tblTemp].[RollID]" & _
               "WHERE (((tblAct.ActNo) In (SELECT ActNo FROM [tblTemp])));"




CurrentDb.Execute strSQLUpdate, dbFailOnError
CurrentDb.Execute strSQLInsert, dbFailOnError

Is this a correct syntax?

[John_G]

Hi -

Can tblTemp contain duplicate ActNo values? If there are duplicate ActNo values in tblTemp you might get duplicates in tblAct as well.

Using Select Distinct should work.

John

[Only4Access]

Hi John,

Yes. Duplicates might be possible, haven't think about that . So where should I add DISTINCT? Also the above syntax is correct?

Thank you for your help.

[JoeM]

Basically, if you create the queries using the Query Builder, you can switch to SQL view to get the exact SQL code syntax you need to put into your VBA code.

I would recommend doing the Update before the Insert.
Otherwise, you may end up Updating the records you just Inserted!
Probably won't hurt anything, but is totally unnecessary.

[Only4Access]

Hi Joe,

if you create the queries using the Query Builder, you can switch to SQL view to get the exact SQL code syntax you need to put into your VBA code

Since I am newbie, this how i do it. But in Update query 'Update To' I enter a single value. Here have to look whole field then update if exists. So i do not know the above syntax i posted is correct or not.

[John_G]

Hi -

for the INSERT, you could use this:

strSQLInsert = "INSERT INTO tblAct (ActNo, RefID, SiteID, RollID)" & _
"SELECT DISTINCT ActNo, RefID, SiteID, RollID FROM tblTemp " & _
"WHERE ActNo NOT IN (SELECT ActNo FROM tblAct)"


However, if there are different combinations of RefID, SiteID and RollID for the same ActNo in tblTemp, then it still won't work because ActNo will be duplicated.

The SQL for the update isn't correct, because in order for it to work you have to tell it which records are to be updated, which are the ones where the ActNo is the same in both tblAct and tblTemp. The WHERE clause has to be changed:

strSQLUpdate = "UPDATE tblAct SET ActNo = [tblTemp].[ActNo], RefID = [tblTemp].[RefID]" & _
"SiteID = [tblTemp].[SiteID], RollID = [tblTemp].[RollID]" & _
"WHERE tblAct.ActNo = tblTemp.ActNo;"


But the same caution about duplicate ActNo values in tblTemp still applies - if there are different combinations of RefID, SiteID and RollID values for the same ActNo in tblTemp, then you don't know what the final values in tblAct will be - which set of values will it use for the update?

You are going to have to look at what you have in tblTemp and decide how to deal with duplicates.

HTH

John

[Only4Access]

Thank you John. I'll explain about table.

tblAct will NEVER have different variations. If any of related info is changes then it must be changed to every where that particular AcNo used. That's why i keep them in separate table. ie

tblAct: ActNo (PK), RefID, SiteID, RollID

123, 1, 2, 3
ABC, 1, 2, 3
A1A, 2, 2, 1

So what I am trying to do is, if tblTemp contains '123' in different options than currently in tblAct then Update those in tblAct. If tblTemp have new ActNo, then INSERT those in tblAct.

So with the current code, does work this way?

Thank you All for your valuable advice & time.

[John_G]

Hi -

From what you have given, then the code should work. Your only concern should be that tblTemp might contain two different lines like this:

ABC, 1, 2, 3 ....
ABC, 1, 2, 4 ....

Ask yourself if than can EVER happen, and what do you do if it does happen?

John

[Only4Access]

Thanks John. Since All ActNo are unique, either any of these info is OK on tblAct. in the next level users have options to update 'ABC' to it's appropriate details. I wonder, if that happens, does this code works? or will give error? If 'ABC' exists update to 1, 2, 3 Or 1, 2, 4 is OK, else INSERT one 'ABC' with 1, 2, 3 Or 1, 2, 4?

[John_G]

If the tblTemp contains those two different lines, the INSERT will fail with a duplicate data error, because it will try to add two records with the same PK value, ABC.

The UPDATE probably won't give you an error, but you cannot be sure of what update will be applied to tblAct. That is, will it be ABC, 1, 2, 3 OR ABC, 1, 2, 4? You can't tell.

Your problem is this: "...users have options to update 'ABC' to it's appropriate details."

If different users make different changes to 'ABC' - which one is right? You need to look at your business process here, because it appears there may be problems with it.

John