Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2002
    Posts
    6

    Unanswered: Calling Stored Procedure in Access

    I'm trying to move some VBA query calls server side to stored procedures to speed things up in a linked table AccessXP/SQL Server 2000 application.

    I made a basic stored procedure named CustomerSelect that is -- SELECT * FROM tblCustomer

    I have a lot of sql pass through queries in my program like:
    db.execute("delete * from tblError"), dbSQLPassThrough

    so I tried:
    db.execute("Exec CustomerSelect"), dbSQLPassThrough

    but it won't run since it is a SELECT query. How do I call this stored
    procedure? I tried making a pass through query with CustomerSelect as the only SQL text but every time I run it, it asks for the DSN which is
    obviously obnoxious if I want it as part of a larger function eventually.

    Any tips?

    Thomas

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Have a look at the Microsoft Knowledge base. Might be just what you want.

    http://support.microsoft.com/default...;en-us;Q128408

    David

  3. #3
    Join Date
    Aug 2002
    Posts
    6
    Thanks! I actually got it to work like this:

    ***********

    Dim mydb As Database
    Dim myq As QueryDef
    Dim myrs As Recordset

    Set mydb = CurrentDb()
    Set myq = mydb.CreateQueryDef("")

    myq.Connect = "ODBC; DSN=Viewmanager; DATABASE=Viewmanager"
    myq.SQL = "TM_Customer"
    Set myrs = myq.OpenRecordset()
    MsgBox myrs!Cust_ID

    **********

    I had to explicitly call the DSN and DATABASE. Good stuff.

  4. #4
    Join Date
    Oct 2002
    Location
    BC Canada
    Posts
    61
    When I try this, I get a Type MisMatch at the
    Set myrs = myq.OpenRecordset()

    line???


    Originally posted by UThomas
    Thanks! I actually got it to work like this:

    ***********

    Dim mydb As Database
    Dim myq As QueryDef
    Dim myrs As Recordset

    Set mydb = CurrentDb()
    Set myq = mydb.CreateQueryDef("")

    myq.Connect = "ODBC; DSN=Viewmanager; DATABASE=Viewmanager"
    myq.SQL = "TM_Customer"
    Set myrs = myq.OpenRecordset()
    MsgBox myrs!Cust_ID

    **********

    I had to explicitly call the DSN and DATABASE. Good stuff.

Posting Permissions

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