Software

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 question was closed by the author