sp returning variables to ms access

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
LVL 1
JeroenWAsked:
Who is Participating?
 
mikkonCommented:
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
 
Victor SpiridonovCommented:
You need to return the parameters as one record and use recordset.Fields to get them in Access.
0
 
JeroenWAuthor Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Victor SpiridonovCommented:
Try doing a SELECT @var1,...FROM any_table_with_one_row (or using where , that will return one row from any table.)
0
 
JeroenWAuthor Commented:
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
 
Victor SpiridonovCommented:
I do have Technet. The solution ,that i suggested (SELECT...) worked fine for me. Please post the code you are having difficulty with.
0
 
JeroenWAuthor Commented:
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
 
Victor SpiridonovCommented:
Sorry, for not explaining proprly. I wanted to see the code you use to execute sp in Access.
0
 
JeroenWAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.