Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    stored procedure (access 97)

    I am a newbie to SQL. I want to know how to call a SQL stored procedure from Access. I am using SQL as the database and access as the front end. I am sure this is a very easy thing to do but I can't seem to get started. I have written the SP and it resides in the Stored Procedure area of the enterprise manager.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: stored procedure (access 97)

    It isn't all that easy. Here's a sample from my stash:

    <code>'Execute SP From Access
    'Declare the variables
    Private wkTest As Workspace, conTest As Connection
    Private DSNName As String, CONNECT_STR As String

    'Populate the connection strings
    DSNName = DLookup("DatabaseLocation1", "Setup", "CurrentConnection = " & True)
    CONNECT_STR = "ODBC;DSN=" & DSNName & ";UID=" & sqluser & ";PWD=" & sqlpassword & ";DATABASE = " & DSNName

    'populate the object variables
    Set wkTest = CreateWorkspace("ODBCWorkspace", "admin", "", dbUseODBC)
    Set conTest = wkTest.OpenConnection("CONNECTION", dbDriverNoPrompt, True, CONNECT_STR)
    conTest.QueryTimeout = 0

    'Execute the stored procedure (SQL Server 7.0)
    DoEvents
    conTest.Execute "EXEC PurgeOldServiceData '" & Me.Text77.Value & "'"</code>

    This assumes you have the DSN Name string for the connection stored in a table Named Setup. After you retrieve the DSN name, you populate the rest of the connect string and use that to open a connection in an ODBC workspace. The you use the connection to execute the stored procedure. In the sample above, a value is being passed into the custom sp PurgeOldServeData.

    Does this help?
    Charlotte

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: stored procedure (access 97)

    A strategy different to what Charlotte suggests is to use what is called a "Pass Through" query. You can read about it in help - they are quite useful if you want to return one record from a large number of records, and the query is forced to run on SQL Server that way.
    Wendell

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stored procedure (access 97)

    Thanks I am getting there.
    I am able to execute the stored procedure using the pass through query!!! Now I want to pass in a parameter

    I am having a small problem with syntax. . This is the syntax I am using. I think I must be missing a comma or something.

    exec Ptest & me.txtstate

    Ptest is the stored SQL procedure

    me.txtstate is the name of the text box on my form that will contain the state.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: stored procedure (access 97)

    You're telling it that the stored procedure is named Ptest & me.txtstate, not that you're passing in a parameter to Ptest. Look again at the execute line in the code I posted, which is doing exactly what you want to do.
    Charlotte

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stored procedure (access 97)

    I am confused.

    I set up a pass through query with one line of code


    If I do it this way

    exec Ptest "nj" it works yet

    exec Ptest 'forms![testform]![txtstate]'

    returns no records. TXTSTATE is a field on the form where the query is run. It seems to me that it should work if I use me.txtstate or forms![testform]![txtstate] since it works when I hard code a state.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: stored procedure (access 97)

    Try it like this:

    <code>exec Ptest "'" & forms![testform]![txtstate] & "'"</code>
    Charlotte

  8. #8
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stored procedure (access 97)

    Thanks for all of your help. It took a little bit of time but it is finally working. I used a pass through query.

  9. #9
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stored procedure (access 97)

    Hi, Cathy, you've already got it resolved, but I thought I'd post this for just another sample. BTW, I thought pass-through queries couldn't pass parameters, at least that was my recent experience on Access XP.

    Dim cnn1 As ADODB.Connection
    Dim myset As ADODB.Recordset

    Set cnn1 = New ADODB.Connection
    cnn1.ConnectionString = "driver={SQL Server};server=(put your server name here);database=(put your database here) (;you could put login, password, workstation, trusted connection stuff here)
    cnn1.ConnectionTimeout = 30
    cnn1.Open

    'Execute the stored proc with sent parameters, assuming you have a combo select w/ the stored proc name:
    Set myset = cnn1.Execute("EXEC " & Chr$(34) & ListReports.Column(1) & Chr$(34) & Chr$(39) & CDate(txtStartDate) & Chr$(39) & ", " & Chr$(39) & CDate(txtEndDate) & Chr$(39))

    (The chr$(39) is the single quote.)

    thx
    Pat

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: stored procedure (access 97)

    Hi Pat,

    The basic trick here is to actually modify the pass-through query on the fly so the parameter becomes a part of the query sent to SQL Server. We use this approach to do searches where large numbers of records need to be narrowed down to a few.
    Wendell

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stored procedure (access 97)

    This is how I used a pass through. A colleague of mine gave this to me. I do not know about XP I am still using Access 97. I also do not know if this will work for update or append queries. I am using it for reports only, at this point and it does the trick. I am passing in a state and returning only the records for that state.

    1. Create a pass-through (PT) query in Access. Name it the exact same as the stored procedure you it to execute. So in your case you should have a stored prod and a pass-through query named "pteststate3"

    2. Inside the PT query just use a static statement like you already have done, such as:
    exec pteststate3 'NJ'

    3. Create your form that has controls to specify report parameters and has a button to open a report. Such as:



    The click event of the button should contain something like:
    Private Sub cmdOpenReport_Click()
    On Error GoTo Proc_Err

    DoCmd.OpenReport "YourReportName", acPreview

    Proc_Exit:
    Exit Sub

    Proc_Err:
    MsgBox Err.Description
    Resume Proc_Exit

    End Sub

    4. Create you report based on the static PT query that was created in step 1&2. After you finish report formatting and field layout, add the following code to the Open event of the report (modify form name and textbox name as necessary):

    Private Sub Report_Open(Cancel As Integer)

    Const csProcName As String = "pteststate3"

    CurrentDb.QueryDefs(csProcName).SQL = _
    "exec " & csProcName & " '" & Forms![YourFormName]![YourParameterTextBoxName] & "'"

    Me.RecordSource = csProcName

    End Sub

    This should work. I tried it locally. This method is not a good approach if you forsee multiple concurrent users accessing the same reports at the same time since the underlying QueryDef is changed each time the report is run. So potentially if 2 people run the report at the same time, both people will see same result.

  12. #12
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: stored procedure (access 97)

    Oh, right! I didn't even think of going that route (brain dead!).
    So many ways of doing things -- we're RICH (hee hee)
    thx
    Pat

Posting Permissions

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