Calling a Stored Procedure from An Access Form

  • Hi,

    I need some help with regards to calling stored procedures on SQL server 2000 from within Access on a form. That will narrow down the records based on a user entered parameter.

    So when the user is in the form he can enter a GM_Code and it will call the stored procedure with that GM_Code and will return all that are "like" it and return those reocrds so that they can be browsed by the form.

    The Stored Procedure Name : SearchGmCode

    The stored procedure Code :

    CREATE PROCEDURE dbo.SearchGmCode(@GMCODE nvarchar(50))

    AS SELECT     *

    FROM         dbo.product

    WHERE     (GM_Code Like @GMCODE + '%')

    GO

    The form code and setup :

    Form Name :Search product Form

    Source : product <---Linked SQl table in access

    Search Criteria Field name : strFirst

    Command Button Name : Command187

    The Command Button code :

    Private Sub Command187_Click()

        Dim cnn As ADODB.Connection

        Dim cmd As ADODB.Command

        Dim rst As ADODB.Recordset

        Set objConn = CreateObject("ADODB.Connection")

     

        ' Create an ADO connect string

        sDataConnect = adoConnectSQL("123Access", "Productsearch", "ProductSQL", "GMSQL1")

     

        Set cnn = New ADODB.Connection

        cnn.Open sDataConnect

       

        Set cmd = New ADODB.Command

        With cmd

            .CommandType = adCmdStoredProc

            .Parameters.Append .CreateParameter( _

               strfirst, _

               adVarChar, _

               adParamInput, _

               50, _

               strfirst)

            .CommandText = SearchGmCode

            Set .ActiveConnection = cnn

        End With

        Set rst = New ADODB.Recordset

        With rst

            .CursorLocation = adUseClient

            .LockType = RstLockType

            .Open cmd, options:=adCmdStoredProc

        End With

        Set rst.ActiveConnection = Nothing

        Set Forms!MyForm.Recordset = rst

    The adoConnectSQL Module code :

    Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource)

      ' Returns SQLOLEDB ADO connect string

      ' Uses SQL Server security

      Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource

      sProvider = "Provider=SQLOLEDB.1;"

      sPassword = "Password=" & psPassword & ";"

      sPersist = "Persist Security Info=True;"

      sUser = "User ID=" & psUser & ";"

      sCatalog = "Initial Catalog=" & psCatalog & ";"

      sDataSource = "Data Source=" & psDataSource

      adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource

    End Function

    Am I even going in the right direction?? FYI this is a access Db not a project. It jsut has the tables linked direc into SQL.

    I think ive got everything you would ask of me, although slightly frustrated cus when I clicked preview it wiped my info and didnt show me a preview so I have had to re write this 

    Time to make a change
  • I think you might want to try using a SQL Pass Through query. Set this up by going in to design mode to create a query then with the design window up select Query from the menu. Then select SQL Specific and then SQL Pass Through. Put in the procedure name. You'll have to work with it a bit but I think you can pass in the parameters by putting in the Forms!frmName!txtBox type of statement.

    Stuart

  • I don't see a sence of creating a stored procedure here. The select statement is short and not complicated, only code ID  is returned so it may or may not be performance gain especially if the field is indexed properly.

    I would try first to use ADO Recordset.Open with your Command object that contains CommandText your SQL Statement.

    If it does not work I would then follow Stuart's advice and create the Pass Through query.

    Your table is a linked SQL Server to Access table. If you will be using explicit SQL, not stored procedure you will not need to have your SQL connection to SQL Server. You can select from the linked table like you select from Access table.

    Regards,Yelena Varsha

  • There are roughly 90 fields in this one table and just about all of them are requried to be viewed by the form. There are 2700 + records totalling around 2Gb of information.

    I am a total noob with regards to best practices for performance and have been trying to read the best way to do it.

    I used this forum post for a reference http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=131&messageid=279076#bm281340 It seemed a nuumber of other people suggested this being the best way too.

    Time to make a change
  • One incredibly stupid way to do it is to link to the table and simply do

    me.Filter = "GMCode Like " & txtbox & "*"
    me.FilterOn = True

    i.e. do it all in Access. Try that and see how slowly it runs.

    As to your code it's basically OK except that

        With cmd

            .CommandType = adCmdStoredProc

            .Parameters.Append .CreateParameter( _

               strfirst, _

               adVarChar, _

               adParamInput, _

               50, _

               strfirst)

    The first strfirst should be "@GMCode" the name of the parameter  and the second should be the actual value which I suspect comes from a text box.

    One 'nice' trick is to change the Caption of the from so that it displays the filter i.e.

    Me.Caption = "GMCode Like " & txtbox & "*"

    then at least the users can see what is happening.

  • Hello Antony,

    I would use a stored procedure, because you are only using Access as a container to hold the forms & reports etc. Which means that the less you have to think about "Access sql syntax" the better off you'll be.

    Also, if you have access to SQL Server Query Analyzer, you can design & test all your sql right there, before you copy it over into Access.

    This is what I would do personally:

    Create your stored procedure in SQL Server, just like you did above, except that I would use a prefix like 'proc' or 'usp' or 'prc'...

    CREATE PROCEDURE dbo.prcSearchGmCode (@GMCODE nvarchar(50))

    AS SELECT     *

    FROM         dbo.product

    WHERE     (GM_Code Like @GMCODE + '%')

    ...then in Access I would create a pass-thru query with this sql statement:

    prcSearchGmCode ''

    ...and then call it qrySearchGmCode to keep things organized.

    Now, let's say you want GMCodes starting with 'Joe' - all you have to do in Access is this (excuse any line breaks):

    CurrentDb.QueryDefs("qrySearchGmCode").SQL="prcSearchGmCode 'Joe' "

    And when you execute that Access query, you get what you want.

  • very very very very late reply, but this has very much helped me in my further endevaours in stored procedure running.

    And you simplistic way of explaining this made it all possible.

    /salute

    Time to make a change

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply