Results 1 to 4 of 4

Thread: in access calling a stored procedure in sql server

  1. #1
    Join Date
    Oct 2005
    Posts
    7

    Question in access calling a stored procedure in sql server

    Hi all,

    I have a report that I want to run from a form.

    Background:

    I created a stored procedure in SQL Server and it has two parameter values: Month (int) and year (int). I created a pass through query in Acess that executes the stored proc with the two parameter values I supplied:


    EXECUTE spM_bodies_and_units_detail_rpt 10, 2005


    This works and the report works. Now the issue I have is that I want the pass through query to take two parameter values from two fields on the form:

    [forms]![frmReportMenu]![txtMonth]
    [forms]![frmReportMenu]![txtYear]


    I have tried the following in the pass through query:

    EXECUTE spM_bodies_and_units_detail_rpt [forms]![frmReportMenu]![txtMonth], [forms]![frmReportMenu]![txtYear]

    this gives me an error:

    ODBC--call failed
    Incorrect syntax near '!'. (#170).

    Has anyone tried this? I was hoping someone might have an idea. Thanks for any help on this.

  2. #2
    Join Date
    Oct 2005
    Posts
    8

    Talking

    I'd use some vba-code to call stored procedures on a postgres server.
    Basicallay something linke:
    (GlobalDS might look like "DSN=my_dsn;" and refer to a user or system DSN setup by the ODBC Control )

    Dim cnn as ADODB.Connection
    Dim sqlstr as String

    sqlstr = "execute spM_bodies_and_units_detail_rpt (" & [forms]![frmReportMenu]![txtMonth] & ", " & [forms]![frmReportMenu]![txtYear] & ");"
    set cnn = new ADODB.Connection
    with cnn
    .ConnectionString = globalDS
    .Open
    .Execute(sqlstr)
    .Close
    end with

    set cnn = nothing

    -------------
    maybe you must double-double quote your procedure name - it would need to be done with postgres, when you use names in mixed cASes...

    An example of how I call stored procs from vba is in the attachement of this posting: http://forums.databasejournal.com/sh...ad.php?t=40887
    Last edited by lgkf; 10-20-2005 at 02:44 PM. Reason: typos

  3. #3
    Join Date
    Oct 2005
    Posts
    7

    Talking Thanks for the reply

    I was able to get it working through vba, though I did it a little different.

    I did some researching on why the pass thru query wasn't working as I thought it should, and basically I found that the pass through query works like the sql server's Query Anlalyzer, where it will be 'read' on the 'server' side' - so sql server would have no idea what a [Form]![field name] is since that is strictly an access object.

    I decided to go the Querydef route as I have learned from past experiences that you can create and modify an existing query through the querydef. I figured if I could simply change the query parameters through VBA. Below is the 5 lines of code I used to change the querydef before it opens the report - it works great

    (fyi: I did have to add the reference for DAO as I was using Access 2003 which uses ADO. ADO doesnt recognise the querydef but DAO does).

    Dim qdfList As QueryDef
    Dim strSQL As String

    Set db = CurrentDb

    Set qdfList = db.QueryDefs("BODIES_AND_UNITS_MAM") 'passthrough query
    strSQL = "EXECUTE spM_bodies_and_units_detail_rpt " & [Forms]![frmReportMenu]![txtMonth] & ", " & [Forms]![frmReportMenu]![txtYear] 'passing in variables
    qdfList.SQL = strSQL


    stDocName = "REPORT NAME"
    DoCmd.OpenReport stDocName, acPreview

  4. #4
    Join Date
    Nov 2006
    Posts
    3
    I am new to VB. Currently i am working in ms access.
    explanation.
    I have created a form named [Form3] consists of combo box "Department" values (eee,ece,mech,cse). and I created a table in SQL Server named register consists of the fields stud_name, register_num,mark1,mark2,mark3. And I am having a query query1. "select * from dbo.register where department=[forms]![form3].[combo3]. And I am having a report Report1 will display Name, mark1, mark2, mark3.

    When the user chooses the department as "ece".
    The report1 will be generated consists of the all the "ece" students names and their marks.
    It works fine.

    Now i need to do it with stored procedure.

    I wrote a stored procedure as follows.
    name of the pass through query: my_proc
    -------------------------------
    create procedure str_proc
    @dept
    as
    select stud_name,mark1,mark2,mark3 from dbo.register where department=@dept
    -----------------------------
    and executed this pass through query. I did not get any errors.

    I wrote another-pass through query as follows.

    execute str_proc 'ece'

    I got all the values (name and marks)

    I gave it like

    execute str_proc [form]![form3].[ combo3]

    It throws error message.
    Then i have given the following code.
    ------------------------
    Dim cnn as ADODB.Connection
    Dim sqlstr as String

    sqlstr = "execute spM_bodies_and_units_detail_rpt (" & [forms]![frmReportMenu]![txtMonth] & ", " & [forms]![frmReportMenu]![txtYear] & ");"
    set cnn = new ADODB.Connection
    with cnn
    .ConnectionString = globalDS
    .Open
    .Execute(sqlstr)
    .Close
    end with

    set cnn = nothing
    --------------------------
    in the report1 - on open event. But still values not populating in the text boxes on the report1. what is the solution for this problem.

    could you please help me to fix this problem.

Posting Permissions

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