General discussion

Locked

Access 2000 - SQL if

By JohnGreer ·
Here's what I want to do:

INSERT INTO History
SELECT [Imported].[Price] AS Price, [Imported].[Date] AS [Date]
FROM Imported;

DELETE Imported.*
FROM Imported;

BUT I only want to run the DELETE if the INSERT worked. Does SQL have the capability to check things that ran before and conditionally run other things? e.g. IF abc THEN def

This conversation is currently closed to new comments.

3 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000 - SQL if

by Bob Sellman In reply to Access 2000 - SQL if

I assume you are using a SQL back end connected to an Access front end.

Write a stored procedure in SQL. Include in the stored procedure both the insert into statement and the delete statement (modified so they are SQL language compliant). Surround (within the procedure) them with the begin transaction and commit statements. Inlcude within the procedure some test to be sure that the insert statement worked...if it didn't work then ROLLBACK rather than COMMIT.

In Access, after the has been entered into the Imported table (which should actually be located in the SQL database for this to work), just execute the stored procedure by calling a pass through query that you have set up in Access. Set your connection parameters properly toconnect to the SQL back end database. The "query" will actually read:

EXEC {name of stored procedure}

You could even include in the stored procedure something that would return a value of 1 or 0 so you could set a value in Access equal to thevalue returned and display an appropriate message to the user ("Data imported successfully" or "Error-data import failed").

Collapse -

Access 2000 - SQL if

by JohnGreer In reply to Access 2000 - SQL if

Sorry, either I did it wrong or I don't understand or...

Collapse -

Access 2000 - SQL if

by JohnGreer In reply to Access 2000 - SQL if

This question was closed by the author

Back to Software Forum
3 total posts (Page 1 of 1)  

Related Discussions

Related Forums