Unanswered: Running SQL Server Stored Procedures through access
Can someone help me with this problem.
I have a stored procedure in SQL Server that updates a particular table. When I run it in SQL server Query Analyser, it works fine. But I want to invoke this stored procedure when I click a button on an MS Access Form. The code I'm using is:
Dim cn, cmd
Set cn = CreateObject("ADODB.Connection")
cn.Open "SQL" //Data Source Name
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
cmd.CommandText = "LoadApplicants" //Stored Procedure Name
cmd.CommandType = adCmdStoredProc
for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?
There is definitely a reason, although I can't see what it is yet.
My first guess would be an object ownership problem... I'd prefix any object names that don't contain a period (.) with "dbo." to make them valid "two part names" for SQL Server. This may not be your problem, but it is the best place I can think of to start.
a quick check that has worked for me has been to create an access db PROJECT (*.adp)
set the datasource to your sql server and the db that you want to connect to and try to access your stored procedures
if it works you can assume (to a degree) that you have parity.