Solved

Updateable Recordset from SQL Server stored procedures using ADO and variables

Posted on 2004-09-11
20
1,287 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

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)

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 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

20 Experts available now in Live!

Get 1:1 Help Now