Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2004
    Posts
    1

    Unanswered: 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?

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    lets see the code for the sp

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    not sure but here are a few suggestions

    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.

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •