Avatar of Darlyne Pitt
Darlyne PittFlag for United States of America asked on

Unanticipated Results when Executing a SQL stored procedure from Access 2010

I’ve created a pass-through query in Access which executes a stored procedure that searches for a string across all tables in my SQL database.  The stored procedure on the SQL server runs as expected with results that show all the tables that contain the value in my search string.  However, when I run that same SP using the pass-through query in Access, the Access “view” returns only one table instead of multiple tables that contain my value.  I know the Access interface behaves differently than SQL, but I have a form which is bound to the search result of the pass-through query and I need the users to view all of the tables which contain their search string.
Microsoft AccessMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
Bitsqueezer

8/22/2022 - Mon
tigin44

did you check the privileges that the access connection have when you run the query across the sql server? This may be a privilege problem
ASKER
Darlyne Pitt

I'm connecting via trust connection to the SQL server as a domain admin so privileges aren't an issue.  There are no error messages and the query executes just fine except it returns only one table.
PatHartman

I don't use stored procedures that return multiple recordsets for this very reason.  You can probably get to the other recordsets if you work with the results in code but not when it is bound to a form.

Create a separate sp for each recordset.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Darlyne Pitt

Sorry, I misworded my question and should have said that my form is not bound to the search result, I am using the .Connect and .sql properties in code to list the results in an unbound control, but I think I’m incorrectly using the pass-through query.  I will try your approach and let you know how it goes.
ASKER CERTIFIED SOLUTION
Bitsqueezer

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Darlyne Pitt

Christian,

Thanks for this.  I changed my method and decided to not use a pass-through query and just call the SP from SQL using VBA:
   'Stored procedure + parameters called from Form
    strSQL = "Exec sqlsp_searchalltables " & Me.txtTables & _
       ", " & "'%" & Me.txtSearchTerm & "%'"

Open in new window

But I think I can now still use your suggestion and use the UNION ALL method in my SP to concatenate the results.  Not sure how to do that, but I'll post a new question if I get stuck.

Darlyne
Bitsqueezer

Hi,

if you use "Execute" in a SQL string you can't use DAO and you must use ADO, that means: It is also a "pass-through" query as you are working with T-SQL and not with Access SQL...;-)

So there would be no difference if you used a "real" pass-through query or ADO, it's both T-SQL. PT Queries are read-only, with ADO you can also write to the result if needed.

A "UNION ALL" query means that you must have the exact number of columns in both (all) SELECTs which are part of the query. For example:

SELECT a,b,c FROM MyTable WHERE ....
UNION ALL
SELECT x,y,z FROM MyOtherTable WHERE....

Open in new window


They also should have the same datatype in the corresponding columns (here: a and x, b and y, c and z) or you must cast them to the same datatype.

Cheers,

Christian
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Darlyne Pitt

Thanks for the additional information on pass-through queries, I didn't know the correct terminology.  I am using ADO, but I may have a problem using the UNION ALL because although my column count can be equal, I not sure I can have the same datatypes.  I'll check.
Bitsqueezer

Hi,

the datatype can be cast to the wanted one, any datatype can be cast as a string for example.

If all other methods fail you could also add an additional parameter to your sp which you can use to only output the wanted return table. In this case you would need to call the same sp as often as needed with a different parameter so that the code in the sp remains in one place but the result set can be switched using this additional parameter.

Cheers,

Christian