How to make updatable subform

[Guest]

I have an access 2000 project and sql server 2000. On the sql server there is
a complex stored procedure downloading records to a subform. How can I make
the data on the subform updateable? Here is my code:

Dim PLonnraltVer As New ADODB.Parameter, PLRNR As New ADODB.Parameter, RS As
New ADODB.Recordset
Dim CMD As New ADODB.Command

CMD.ActiveConnection = CurrentProject.Connection
CMD.CommandType = adCmdStoredProc
CMD.CommandText = "dbo.lpsp_LonnramAltSelect"

Set PLonnraltVer = CMD.CreateParameter("Lonnralt_ver", adInteger,
adParamInput, , Me.txtlonnramver)
CMD.Parameters.Append PLonnraltVer

Set PLRNR = CMD.CreateParameter("lr_nr", adInteger, adParamInput, ,
Rammenr)
CMD.Parameters.Append PLRNR

Set RS = CMD.Execute

Set Me.SF.Form.Recordset = RS

SF is the name of the subform. The stored procedure, lpsp_LonnramAltSelect,
is makes use of "select case" and "group by". There is no primary key
available in the recordset, and the recordset on the datasheet becomes
not-updateable.

What I would like to do is to supply values into the datasheet subform, let
the operator edit values all over the datasheet and then press a button to
update the sql server tables using a stored procedure. How can I do this? I
have tried variations of unbound controls on the datasheet and ado recordsets
but so far without any success.

Regards

Tore

 

[Guest]

I could mention that the stored procedure performs a pivot operation,
transforming some field values to new columns. The operator should be able to
update the resulting pivot table in the access client, and then the sql
server tables need to be updated.

Regards

Tore

 

[Sylvain Lafontaine]

Sorry but you cannot do that directly with ADP. ADO - on which ADP is
based - cannot update the result of a query where there has been a group
operation, pivot operation, etc.

One possibility would be to store the values in a temporary table and use a
trigger to update the values in the *real* server tables or to setup a view
and use an Instead Of trigger to do the same (however, I don't know if this
second solution will work with ADP).

Overall, ADP is not really suited to this kind of complex updating; a better
solution would be to switch to .NET technologies.

 

[Sylvain Lafontaine]

Of course, if you are using a temporary table (or an intermediary table),
you are not obligated to make the update to the other tables using a
trigger; instead, you can simply use the OnAfterUpdate event of the subform
to do the same from VBA code.

Another possibility would be to use a disconnected recordset as the record
source of the subform by in my opinion, using a intermediate table to store
the values is probably the easiest and simpler way of doing this.

 

[Guest]

Thanks for your answers. I have done a bit of searching and reading and found
articles that support your views. Switching to .net technologies will not be
possible in this case. I will consider an intermediate table (not #
temporary) on the sql server for my transformed pivot table and let the
access client work against a "normal" table with the transformed pivot
structure.

Thanks again for your answers, I am impressed by your ability to provide
fast and efficient response.

Regards

Tore

 

[Sylvain Lafontaine]

In your case, another possibility could be to isolate the query with the
Group by and Case statement in a subquery and associate it with the parent
query to a normal table with its primary key.

By setting the UniqueTable property to the main table (and setting the
ResyncCommand?), it's possible that this query will be updatable if all the
fields that the user will edit will be part of the main table only. Never
tried personally but you may want to take a look at it. However, not only
I'm not sure if this will work with ADP but I'm not even sure if this
possible solution fit your description of the problem but I give it for the
sake of completness.

All in all, I think that using an intermediary table is still probably the
easiest way to solve your problem.

 

[Jamie Collins]

ADO- on which ADP is
based - cannot update the result of a query where there has been a group
operation

I think you picked a bad exmaple there. Consider the following which
uses a GROUP BY query on Northwind (Jet) and demonstrates that the ADO
recordset is indeed updateable:

Sub UpdateableGroupBy()
Dim con As ADODB.Connection
Set con = New ADODB.Connection
With con
.CursorLocation = adUseClient
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\" & _
"Microsoft Visual Studio\VB98\" & _
"NWIND.mdb"
.Open
End With
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = con
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Source = _
"SELECT CustomerID, CompanyName" & _
" FROM Customers" & _
" GROUP BY CustomerID, CompanyName;"
.Open
.Filter = "CompanyName = 'Around the Horn'"
If .RecordCount = 1 Then
.Fields("CompanyName").value = _
"Around the Bend"
.Update
End If
.Requery
.Filter = "CompanyName = 'Around the Bend'"
If .RecordCount = 1 Then
.Fields("CompanyName").value = _
"Around the Horn"
.Update
End If
End With
End Sub

Jamie.

--

 

[Sylvain Lafontaine]

Ah but first, your example is with JET and not with SQL-Server - the subject
of this newsgroup - and second, the fact that you are making a Group By on
the primary key mean that the whole Group By instruction is doing absolutely
nothing. (And possibly is even removed by JET before performing the query
but I'm not sure of this last point.)

I'm not sure that giving an exemple with an instruction that is performing
nothing and is outside of the topic of this newsgroup is a better example.

 

[Guest]

Thanks for all reponses. My problem is solved. I ended up with an
intermediate table to do pivoting, and it works perfectly.

Regards

Tore