Solved

Execute parameterized stored procedure using VBA, not ADP, not passthrough

Posted on 2004-08-12
5
552 Views
Last Modified: 2008-01-09
My apologies if this has already been answered, but all of the solutions I could find referred to ADP or passthrough queries, neither of which is an option for this particular scenario.

I have a MS Access database which is mainly linked tables to several SQL server databases.  One of the ODBC connections is called intlDB.  I have a stored procedure called spCreateGroup which takes three parameters - groupID (integer), sOrder (integer), eOrder(integer).  

How can I execute this stored procedure such that groupID, sOrder, and eOrder are populated via a MS Access form?  Pay me a thousand dollars if I'm wrong, but passthrough queries do not accept Access forms parameters, right?
0
Comment
Question by:eatham111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 36

Accepted Solution

by:
SidFishes earned 500 total points
ID: 11788225
This should do the trick

    Dim dbs1 As DAO.Database, qdf1 As DAO.QueryDef
    Set dbs1 = CurrentDb
        strconnect = "your Connection string'
    Set qdf1 = dbs1.CreateQueryDef("")
        qdf1.Connect = strconnect
        qdf1.ReturnsRecords = False
        qdf1.SQL = "spCreateGroup " & me!groupID & "," & me!sOrder & "," & Me!eOrder
                                       
    qdf1.Execute

qdf1.Close
dbs1.Close
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11788289

you should also add at the end

set qdf1 =  nothing
set dbs1 = nothing
0
 

Author Comment

by:eatham111
ID: 11788436
It is giving me the error "Invalid connection string in pass-through query."

on this line:

qdf1.SQL = "spCreateGroup " & Me!newgroup & "," & Me!fromOrder & "," & Me!toOrder

My connection string is:

Driver={SQL Server};Server=trg1sql;Database=dbClub;UID=******;PWD=*********;

I know my UID and pwd to be correct - do you see any problems other than that?  I c&p'd your code directoy other than that.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 11788571
I think that's because you need to being using odbc and not native sqlserver (which is adp land)

try an odbc connection string

ODBC;DSN=dbClub;UID=******;PWD=*********;DATABASE=Orders
0
 

Author Comment

by:eatham111
ID: 11788624
Perfect.  That was it - thanks!
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

734 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