Running SQL Server Stored Procedures through access

Hi,
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
cmd.Execute

for some reason only a few records are updated everytime I click on the button. Is there any reason why this is happening?lets see the code for the sp|||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.

-PatP|||I would explicitly dim your cn and cmd objects.

Dim cn as new ADODB.connection
Dim cmd as new ADODB.command

At the bottom of your function, make sure you destroy the objects

cn.close
Set cn = Nothing
Set cmd = Nothing

Try using a OLEDB connection instead of a DSN.

Are there any parameters for this stored procedure? I do not see them here.

Maybe also try fully qualifying the stored procedure name because you might be hitting the wrong proc.

[databasename].[owner (usually dbo)].procedurename.|||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.