Solved

Updateable Recordset from SQL Server stored procedures using ADO and variables

Posted on 2004-09-11
20
1,296 Views
Last Modified: 2008-02-01
Hi there,

I've ran into an issue and hope someone can shed some light on the subject.

Here are the basics
  Access 2003 (mdb not adp)
  SQL Server 2000
  Windows 2003 Advanced Server (OS)
  ADO 2.8

I've created a stored procedure that uses variables to create the pieces of the SQL statement and then the exec command to retrieve the records.  I did this because of the 10 different possibilities within the where clause.

Here is what the final line of the stored procedure looks like
exec (@strSELECT + @strFROM + @strWHERE + ' ORDER BY d.DateCheckReceived')

A few other notes about the stored procedue.  It contains a left join to a derived table and I aliased all the tables for readability.


The stored procedure works like a champ and is oh so fast.  The users would just love it. Except for one problem.

Here is the problem.  It's not updateable and I'm trying to figure out why.

I've done this
Set the UniqueTable property of the form to the name of the table that contains the fields that I want to update.
I've added all the PK's to the returned field list for all tables in the stored proc
The recordset is opened wit the following properties set, adopenkeyset, lock optimistic, and I've tried use server cursor location and use client location.

I'm thinking that because I'm using the EXEC to execute the SQL it's being flagged as read-only.  Or because my from clause contains a left join to a derived table, it's read-only.

Any takers.......I would like to resolve this tonight.

I've search EE, but haven't found a question that answers my question.

Thanks in advance,
Mike



0
Comment
Question by:Data-Man
  • 9
  • 7
  • 2
  • +1
20 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12036190
It can't be updateable because the source (a stored procedure rather than a table) isn't updateable. I think your best bet is to make sure your sp is returning the primary key values for the table(s) you want to update, and then execute individual update statements for each row a user changes.
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12036198
Stored procedures are updateable.....I do this all day long.  It has always worked.

The form in Access is in continuous forms, I need them to see as many records as possible.  

Mike
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12036209
Have you ever updated a stored procedure using dynamic SQL?
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 18

Author Comment

by:Data-Man
ID: 12036229
I have passed a dynamic SQL statement instead of calling a stored proc....is that what you mean?

Mike
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12036275
Sorry I wasn't clear. I mean this stored procedure generates the recordset it returns by executing dynamic SQL. I don't think you can update a recordset when the query is created dynamically in the sp. If you've done this before, I'd love to be corrected, cause I'll find some occasions to use that ability myself...
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12036306
I just checked another stored proc that I bind to a subform on an Access form.  It is fully updateable.  It uses variables to store the various pieces of the SQL statement and uses EXEC to give me a final recordset.

I think I'll remove the left join and the derived table for testing and see if the remaining fields are updateable.
Any thoughts on how to handle a left joined derived table without making it a left joined drevired table....I need to get a life.


The stored proc I checked it against was one I created over 3 years ago (SQL Server 2000 and Access 2000 using ADO).  The application was ahead of it's time.

Thanks...Mike
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12036338
Well, you posted the question, but I've learned more than you so far! Your plan to troubleshoot is solid, and I don't have any other suggestions. (But I do want to hear the results.)

JD
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12036351
I'll post the end result here.  If you want to talk about this type of approach, I have lots of code.  My e-mail is in my profile.  I'm in Philly this week and will be heading to NYC tomorrow for a week and then back here to Philly.

I hope the others aren't sleeping...I sure could use some help.

Mike
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12036380
I'm on to something.  

The only difference between the two VBA solutions is in the updateable one I only use the connection object treating the name of the stored proc as a method of the connection object.  And the final item in the list is the recordset object.  

cn.pMyProcName param1, pararm2, etc, rstTemp

In my current project I used a command object to append my parameters and then this line

set rstTemp = mycomm.execute

When I changed it to the connection object solution, it worked like a champ.

There must be a setting at the command object that is causing the returned recordset to not updateable.

Mike
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12036411
I'm just cheering from the sidelines...   :)

And I've bookmarked this Q as a good way to keep your contact info (through the link to your profile).
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12036429
I made some progress.

The form is updateable, even with the left join derived table.  When I add a field from the derived table to the returned field list, the recordset becomes NOT updateable, that's not good, there are two fields that I need to have to make this work.

The adrenaline is flowing.

Mike


0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12036496
That makes sense. All I can think of to solve this is to split your subform in two and to use separate binding. Yeah, I know it's unattractive, I just can't think of anything else.
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12036508
I think I solved it except for one final issue which may turn out to be my Achilles heel.

I changed the ADO to using only the connection object, no command object.

I modified the stored procedure to remove those fields that come from the left joined derived table.

Side Note: The store procedure also returns another recordset with totals using the FROM and WHERE clause created in the first query, and the first recordset is still updateable.  (I use the NextRecordset method)

My only remaining problem is that I need to have the three fields that were part of the left join in my query.

Is it possible to rewrite the SQL and not use a left joined derived table?  I think I'll post it in the SQL forum.

Mike
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12097186
Here is the bottom line.....

In order for a stored procedure to be updateable, only Inner joins can be used, even then the fields cannot be more than a single join away from the table being updated.

I tried to add in the other fields using a view.  But the view employed syntax that resulted in it not being updateable which in turn caused the stored procedure to be not updateable.

Using a stored procedure with an mdb form requires a whole new approach to building database.  If you can get it to work, the performance is unbelievable.  

I appreaciate JD for attempting to help me, but there was no satisfactory answer to this question or the question I posted in the SQL server area ( Posted the SQL for the stored procedure to see if someone could redesign the query to use someting other than a left join), I'll ask EE to refund the points for both questions.

Thanks again,
Mike
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12097766
Kinda hate seeing this Q deleted, since it might help others, but I can't object to Data-Man wanting to save the points. And, the system doesn't allow him to choose one of his own posts as the answer... too bad...
0
 
LVL 18

Author Comment

by:Data-Man
ID: 12100835
I agree...would be nice if there was an option to not delete the question.

Mike
0
 
LVL 34

Expert Comment

by:flavo
ID: 12130485
please PAQ with refund :-)
0
 
LVL 2

Accepted Solution

by:
Lunchy earned 0 total points
ID: 12138126
Closed, 500 points refunded.
Lunchy
Friendly Neighbourhood Community Support Admin
0
 
LVL 34

Expert Comment

by:flavo
ID: 12138144
Thanks Lunchy
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

856 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