Does "If exists (select..." Exist? (1 Viewer)

[GregoryWest]

I am trying to do something like this:
IF EXISTS (SELECT 1 FROM Table1 WHERE Column1 = 1) BEGIN -- UPDATE QUERY END ELSE BEGIN -- INSERT QUERY END
ENDIF

Does this work in Access 2013 VBA? What I need to do is check to see if a hashcode already exists in the database. If it does update, if it doesn't create new record.

 

[jdraw]

You could create a unique index on your hashcode, then Access will give a duplicate can't be added type message that you can intercept.

 

[theDBguy]

Hi. T-SQL code is different from VBA. You would have to convert what you have to make it work in Access. I usually use the DCount() function to test for duplicates.

 

[GregoryWest]

Not looking for duplicated persay. What is happening is; I get a new records from the mainframe system. I then have to 'import' these records into my tables. Problem is some of the records might already exist as is, others might exist but have been changed (Treat as new in this case). So what I do is create a hash of the records, and store this hash along with the data. Later I can check the hash on the incoming record, if exists do one thing, other wise do something else.


Just having a bear of a time doing the 'check' The reset is fairly trivial.

 

[Micron]

Suggest that during the import process, run append query - trap error for violation and just ignore it.
Then run update against same target table. Regardless if target values are the same or not, update won't error. You don't need to identify which records already exist unless you have some other reason for doing so.

 

[June7]

So you need to run two queries: INSERT and UPDATE or try what is called an UPSERT action that accomplishes both. Review https://stackoverflow.com/questions/6199417/upserting-in-ms-access

How are you accessing the mainframe data? Do you have a link to table or worksheet or CSV?

Unless there is some other unique record identifier, possibly build a query on the new data that calculates the hash then join that query to your local table on the hash fields.

 

[GregoryWest]

I get sent a CSV file every year, or when requested.

So you need to run two queries: INSERT and UPDATE or try what is called an UPSERT action that accomplishes both. Review https://stackoverflow.com/questions/6199417/upserting-in-ms-access

How are you accessing the mainframe data? Do you have a link to table or worksheet or CSV?

Unless there is some other unique record identifier, possibly build a query on the new data that calculates the hash then join that query to your local table on the hash fields.

 

[GregoryWest]

DCount works like a charm!! Thanks for the suggestion.

Hi. T-SQL code is different from VBA. You would have to convert what you have to make it work in Access. I usually use the DCount() function to test for duplicates.

 

[theDBguy]

DCount works like a charm!! Thanks for the suggestion.

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.