?
Solved

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

Posted on 2004-08-12
5
Medium Priority
?
560 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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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