Solved

sp returning variables to ms access

Posted on 1998-11-09
9
224 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
  • 4
  • 4
9 Comments
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
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
Comment Utility
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:spiridonov
Comment Utility
Try doing a SELECT @var1,...FROM any_table_with_one_row (or using where , that will return one row from any table.)
0
 
LVL 1

Author Comment

by:JeroenW
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
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
Comment Utility
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:spiridonov
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now