How to overwrite an old record when using INSERT INTO statement?

[Joe Y]

My Access DB handles product formulation and specification. In the formula form, many text boxes, such as Cost or ingredient statement, are calculated fields based on each ingredient data in the formula.

Once a formula is complete, it becomes an ingredient. The new ingredient ID and Version in the ingredient table (table name TProduct) is supposed to inherit the formula ID and Version. I built a command button to copy ID, Version, cost, and other information from Formula form to Ingredient form using INSERT INTO statement.

However, I am stuck with one issue. If the record already exists, INSERT INTO statement does not update new information in the existing record. User will not notice that the record in Ingredient Table is not updated.

I don’t know how to modify the code to (1) warn user that same ID and Version exists in Ingredient table; (2) delete the existing ingredient record; (3) create a new record with updated information from formula table.

Below is my current INSERT INTO code. Is there a simple way to handle this? Thanks.

 Dim dbs As Database
Set dbs = CurrentDb
 
Dim strRID As String
Dim strRver As String
Dim strCost As Currency
Dim strStatement As String
 
strRID = Me.[Txt_ID]
strRver = Me.[txt_Version]
strCost = Me.[txt_Total_Cost]
strStatement = Me.[txt_RecipeStatement]
 
dbs.Execute " INSERT INTO TProduct " _
& "(productID, ProductVersion, UnitCost, spec, vendorID, VendorLocation) VALUES " _
& "('" & strRID & "' , '" & strRver & "', '" & strCost & "', '" & strStatement & "', '10000', 'NYC');"
 
dbs.Close 

[mshmyob]

Unfortunatley Access does not have something like "INSERT OR IGNORE" like SQLite but what you could do is

1) do a DLOOKUP based on the PK
2) If the DLOOKUP returns NULL (ie: not found) the do step 3 else do step 4
3) INSERT statement
4) UPDATE statement

cheers,

[nico5038]

When you replace the existing record with the INSERT record, you could first issue a DELETE * FROM tblX x WHERE EXISTS (select ID from tblY y where x.id = y.id)
This will remove the dupes and the INSERT will add them with the most recent information.

[Joe Y]

Although Nico5038's method looks easier, I have already got it done using mshmyob's method. Both methods are valuable lesson for my vba journal.

Thanks for both of you.

Joe

[NeoPa]

Actually, if you use an UPDATE query, it will add any records that don't already exist anyway.

[Mariostg]

@NeoPa
I remember I read something about this that you mentionned somewhere else and indeed that works (I think it was a link with example you gave). But in the SQL statement, you have to indicate a JOIN clause.

[NeoPa]

A curious comment Mario. I agree 100%, of course. It would indeed be required, but so would various other components of the required SQL. I suppose, because that's almost self-evident, I'm just curious that you'd bring it up.

[Mariostg]

@NeoPa
I just wanted to emphasize the UPDATE point, because before I read that other post of you (I am too lazy to find it again, or maybe you have to many posts lol) it never occured to me an UPDATE statement could be used to also INSERT provided the proper syntax of course.

[NeoPa]

Interesting point Mario. Like yourself, I had no idea about that for many years. I'd been working in Access (and SQL generally) and bemoaning the absence of such a facility. It was only while researching something for someone on here that I happened to see a reference to it and so I checked to find that it did, indeed, handle the add/amend situation perfectly.

PS. I couldn't find where I found this information. I have no reference in my database and neither MSDN nor Jet SQL Help (Finding Jet SQL Help) seems to have the explanation that I obviously found somewhere. Frustrating :-(

[mshmyob]

It is easy to create an update statement without using the Join clause though.

But your point of using UPDATE to insert a record (in Access) if it does not exist is new to me. Other systems use the INSERT OR IGNORE statement, or the REPLACE statement.

I will have to look up the exact syntax because I wouldn't want it to inadvertantly add a new record if I didn't want one added.

cheers,

[NeoPa]

Indeed. That was one of the first things I thought about when I came across it originally.