[Webinar] Streamline your web hosting managementRegister Today


Passing Form references to a Stored Proc

Posted on 2000-01-25
Medium Priority
Last Modified: 2012-05-05
I have a stored procedure on SQLServer which requires a variable. This variable is the contents of a textbox on a form. I have tried many ways to do this (in code and a pass-through query but nothing works.

This is the contents of my ps query:-

EXEC procMyProc '" & Forms!myRef & "'"

Can anyone show me how to do this in either code with querydefs or in a ps query. BTW RRR has been trying to help me - Thanks RRR but I'm still struggling.

Question by:Tim_Kempton
  • 3
  • 2

Expert Comment

ID: 2387334
What version of Access are you using?  If you are using 97 and the DAO object library, try the following:
Private Sub CallQDsp()
    Dim qd As QueryDef
    Dim sConnect As String
    Dim sSQL As String, sServer As String
    Dim sLoginID As String, sPassword As String
    Dim cn As Connection
    Dim ws As Workspace
    sLoginID = "sa"
    sPassword = vbNullString
    sServer = "DAREK"
    'The type parameter sets the workspace to ODBCDirect
    Set ws = DBEngine.CreateWorkspace("NewODBCDirectWS", sLoginID, _
                sPassword, dbUseODBC)

    'Set the default Workspace type to ODBCDirect
    DBEngine.DefaultType = dbUseODBC
    'Define the DSN-less connection
    sConnect = "ODBC;Driver=SQL Server;UID=" & sLoginID & _
        ";PWD=" & sPassword & _
        ";Server=" & sServer & _
    'Open the new connection
    Set cn = ws.OpenConnection("", dbDriverComplete, False, sConnect)
    'Create the new procedure
    sSQL = "Create Proc CountStateRows " _
        & "(@state char(2), @rows Int Output) As " _
        & "Select @rows = Count(*) From authors Where state = @state"
    cn.Execute sSQL
    'Call the procedure
    sSQL = "{ Call CountStateRows (? ,?) }"
    Set qd = cn.CreateQueryDef("", sSQL)
    qd.Parameters(0).Value = SOME TEXT BOX ON A FORM
    qd.Parameters(1).Direction = dbParamOutput
    MsgBox "Number of rows: " & qd.Parameters(1).Value
End Sub

....that is, the above uses ODBCDirect to call a stored procedure that in this case returns a value (an output parameter).

Let me know if this is what you are looking for or if it is something else.  That is, from your question I am not sure what you are trying to accomplish exactly.

Author Comment

ID: 2388004
Could this be done easier with a pass-through query?  BTW I'm using Access 97 and DAO

Expert Comment

ID: 2390090
I suppose, however I personally use the above ODBCDirect wherever possible to call stored procedures.  The reason? Well, my stored procedure could have 10 output parameters and also return a resultset... ODBCDirect provides me with this ability.

Anyway, to use pass-through queries try the following:
1 - Create a pass-through query in your queries tab called "TempForSPs".  Place anything you want in the SQL (it does not matter what you put here at all)

2 - Create a new form and place on it a command button.

3 - On the click event of the command button, try the following code... again I do not know exactly what your stored procedure is trying to accomplish so am demonstrating a couple of things here.

Dim rs As Recordset

CurrentDb.QueryDefs("TempForSPs").SQL = "usp_RowCount"
DoCmd.OpenQuery "TempForSPs", acViewNormal
Set rs = CurrentDb.QueryDefs("TempForSPs").OpenRecordset
With rs
    Do Until .EOF
        Debug.Print rs.Fields(1)
End With


Author Comment

ID: 2443001
I think the ODBCDirect method is the best way. I asked this question because I was trying to populate forms and subforms the quickest way possible when using SQLServer with upto 100 users using Access as the front-end.

Accepted Solution

dtomyn earned 150 total points
ID: 2443284
So do you need any more information or have I answered your question enough?

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question