Software

Ask something
Discussion Locked

Access 2000 - SQL if

0 helpful
3 replies

by johngreer

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

3 replies

by bob sellman

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").

This discussion is closed to new replies.