Executing a stored SQL Server procedure from Access

Posted on 2007-10-04
Last Modified: 2008-01-09
I have a stored procedure in SQL Server that performs an update on my tables (no parameters needed and no data returned).  In SQL Server it is called dbo._Test_MissingProjectIDs.

I have created an ADO connection which works fine

I'm having difficulty with the syntax in Access to execute the procedure.  This is where I'm currently at and the resulting error message:

Set qdf = CurrentDb.CreateQueryDef("")
qdf.SQL = "Exec dbo._Test_MissingProjectIDs"

on the qdf.sql step I get error 3129: Invalid SQL statement; expecting DELETE, INSERT, PROCEDURE, SELECT, or UPDATE.

Any thoughts?

Question by:deedub84
    LVL 14

    Expert Comment

    Try removing the "exec" keyword and running it again
    LVL 4

    Accepted Solution

    Execute it through an ADO connection
            Dim objConn
          Set objConn = New ADODB.Connection
          objConn.Open ("Your Connection String")
          objConn.Execute "Exec dbo._Test_MissingProjectIDs"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now