Run SQL Server Proc from Access
I have an Access database using ODBC link to a SQL Server database.
I need to run a delete query from within the Access database, so thought about creating a Proc.
I need to run a delete query from within the Access database, so thought about creating a Proc.
- I don't know how to create a Proc in SQL Server
- How do I then run it from Access (presumably via some VBA code?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for doing the searching. I had done a search, but didn't find these gems
Glad to help!
ASKER
Vitor
Sorry, your answer came in after I had closed the question. If you have time, can you tell me what you mean please? I am happy to open another question so that you get the points
Sorry, your answer came in after I had closed the question. If you have time, can you tell me what you mean please? I am happy to open another question so that you get the points
Don't worry about the points.
I just want to mean if you're only using that DELETE in a single place then don't loose time creating a Stored Procedure. You can execute the DELETE command directly instead of calling a Stored Procedure.
I just want to mean if you're only using that DELETE in a single place then don't loose time creating a Stored Procedure. You can execute the DELETE command directly instead of calling a Stored Procedure.
ASKER
You mean write the delete query and execute it in VBA? I tried that, but it was extremely slow.
I tried that, but it was extremely slow.Then the SP won't help.
You might need to open a new question to help with the performance of the DELETE command.
Then the SP won't help.
Not true. I would say it depends. SP will have execution plans saved which can help rather than the random queries.
SP will have execution plans saved which can help rather than the random queries.Can you develop more about this statement? I'm not sure if you know what you're saying or it's only a bad formulation of the phrase.
You could also create a pass-through query in Access and simply change the SQL of that query.
The pass through could look like:
DELETE FROM yourTAble WHERE [ID] = 3
or it could simply call your stored procedure
EXEC sp_Delete_Record_FROM_Your
you then have to assign a connection string to the query. Finally, when you want to run the query, you would do something like:
Dim qdf as dao.querydef
set qdf = currentdb.QueryDefs("MyPas
qdf.SQL = "EXEC sp_Delete_Record_FROM_Your
qdf.Execute
The pass through could look like:
DELETE FROM yourTAble WHERE [ID] = 3
or it could simply call your stored procedure
EXEC sp_Delete_Record_FROM_Your
you then have to assign a connection string to the query. Finally, when you want to run the query, you would do something like:
Dim qdf as dao.querydef
set qdf = currentdb.QueryDefs("MyPas
qdf.SQL = "EXEC sp_Delete_Record_FROM_Your
qdf.Execute
Are you going to pass parameters for the DELETE command?
It will be used in more that one place?
If it's only for a single use then don't loose time and send the proper DELETE FROM Table command.