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.
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").
If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.
Access 2000 - SQL if
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