Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

sp returning variables to ms access

Posted on 1998-11-09
9
Medium Priority
?
237 Views
Last Modified: 2008-07-03
Greetings,

I've build a sp which returns variables (instead of a recordset based on tables) to ms access.
The querydef which I declared in access has the property returnrecords set to true, but access complains that my sp doesn't return any records. Whenever I run it in the query-tool of SqlServer it returns the correct information.

Can anyone help me with this?

By the way: I use stored procedures extensivelly, but never before did it return variables.

Hope you can help,

Jeroen Westera
0
Comment
Question by:JeroenW
[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
  • 4
  • 4
9 Comments
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1091108
You need to return the parameters as one record and use recordset.Fields to get them in Access.
0
 
LVL 1

Author Comment

by:JeroenW
ID: 1091109
Dear spiridonov,

I return the variables using
SELECT @Variable1, @Variable2, @Variable3, etc

The trouble is that Access does not recognize the fact that the sp has output and generates an error that the sp doesn't have output while I specified that it did (and it does!).

Hope you can help,

Jeroen Westera


0
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1091110
Try doing a SELECT @var1,...FROM any_table_with_one_row (or using where , that will return one row from any table.)
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 1

Author Comment

by:JeroenW
ID: 1091111
Dear spiridonov,

Thanks for your suggestion. I've tried it (I had already, but I double checked) and it did not work. I even tried aliassing the variables by using: SELECT @Var1 as Var1, ... as Var15 FROM TheTable WHERE ...

The sp again had the correct results from the SqlSvr query-tool, but still Access complained.

I found an article on Technet (PSS ID Number: Q152174) which applies to my question. The only problem now is how to convert the example-code to VBA.
- Do I have to declare the ODBC-dll?
- Do I have to define the SqlServer variable-types (constants)?

Anyway,
I would like some example code (VBA) on how to handle this.

Spiridonov: If you do not have Technet, I will be happy to send you the article.

Thanks in advance,

Jeroen Westera
0
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1091112
I do have Technet. The solution ,that i suggested (SELECT...) worked fine for me. Please post the code you are having difficulty with.
0
 
LVL 1

Author Comment

by:JeroenW
ID: 1091113
Dear Spiridonov,

Here is the select-statement that(should) return the values in the variables (The variable-names are in dutch, but I think you get the idea):

/* return the outcome... */
 SELECT @Consistent AS Consistent, @SrtDv as SrtDv, @TypeVaststelling as TypeVaststelling, @TotAantDvsPerJaar as TotAantDvsPerJaar, @TotAantDvsPerMaandInPolis as TotAantDvsPerMaandInPolis, @AantPolismaandMeerDanNulDvs as AantPolismaandMeerDanNulDvs, @GemAantDvsInPolis as GemAantDvsInPolis FROM R05_AV_CONTRACT WHERE NR_CONTR_AV = @NrContrAv

/* I've tried these variants, but no cigar...
 SELECT @Consistent AS Consistent, @SrtDv as SrtDv, @TypeVaststelling as TypeVaststelling, @TotAantDvsPerJaar as TotAantDvsPerJaar, @TotAantDvsPerMaandInPolis as TotAantDvsPerMaandInPolis, @AantPolismaandMeerDanNulDvs as AantPolismaandMeerDanNulDvs, @GemAantDvsInPolis as GemAantDvsInPolis

Or

 SELECT @Consistent , @SrtDv , @TypeVaststelling , @TotAantDvsPerJaar , @TotAantDvsPerMaandInPolis , @AantPolismaandMeerDanNulDvs , @GemAantDvsInPolis  FROM R05_AV_CONTRACT WHERE NR_CONTR_AV = @NrContrAv
*/

Thanks in advance,

Jeroen Westera
0
 
LVL 7

Expert Comment

by:Victor Spiridonov
ID: 1091114
Sorry, for not explaining proprly. I wanted to see the code you use to execute sp in Access.
0
 
LVL 1

Author Comment

by:JeroenW
ID: 1091115
Spiridonov,

Here it is:

Dim db as Database
Dim qdDV As QueryDef
Dim rsDV As Recordset

Set db = CurrentDb()
Set qdDV = db.CreateQueryDef("")

qdDV.Connect = glbODBCConnect
qdDV.ReturnsRecords = True
qdDV.sql = "EXEC sp_BEPAAL_DVS " & NrOpdr & "," & NrWg & "," & NrVest & "," & NrContr & "," & Jaar & ",'" & CdSrtLoon & "'," & intIdCv & ",'" & strCdOP & "'," & intIdVp & "," & lngNrContrAv
Set rsDV = qdDV.OpenRecordset(DB_OPEN_SNAPSHOT)
If rsDV.EOF Then
  Blablabla

Jeroen Westera
0
 
LVL 1

Accepted Solution

by:
mikkon earned 1000 total points
ID: 1091116
The problem is probably that SQL Server returns empty recordsets for your first SELECT's. Access uses only the first one of them, and discards the rest, of which the last one contains your real results.

Access is not able to process multiple recordsets. To solve this, add "SET NOCOUNT ON" as the first statement in your stored procedure. If this doesn't help, add it Access code, just before the EXEC part.

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

610 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