asked on

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

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?

ASKER CERTIFIED SOLUTION

SidFishes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SidFishes

you should also add at the end

set qdf1 = nothing
set dbs1 = nothing

eatham111

ASKER

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.

SidFishes

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

eatham111

ASKER

Perfect. That was it - thanks!