From: David W. Fenton on
Banana <Banana(a)Republic.com> wrote in
news:4C18D475.9030502(a)Republic.com:

> If I wanted to update several tables, I'm more
> inclined to want to use a single unbound form and execute a stored
> procedure instead. Of course, if the client doesn't want a
> separate form for inserting vs editing, then we'll have to deal
> with that somehow.

If I'm not mistaken, in this case, it's multiple tables in the
recordsource, but only one is being edited (and, I presume, having
records added to it).

I have always been frustrated trying to use a single form of any
kind for both adding and editing, and that's why in most of my apps,
adding is done through a separate unbound form, then the editing
form is requeried and then I navigate via bookmark navigation to
thenewly-added record. This keeps the two processes separate. It
also makes it harder for the user to accidentally add a record, as
well as making it easier to abandon a record. The unbound form
collects the fields that are required to create the record (and if
not strictly-speaking required in the table definition, at least the
fields that are necessary to have a usable, identifiable record).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: AVG on
Yes, the ADO source is 'select * from myview where ...'.
Yes, it is updating tables not specified in the UniqueTable property.
Trying different providers:
SQL Native Client - form is not updatable.
SQL Server - error, class not registered.
MSDASQL - error, Data source name not found and no default driver specified.

The construct of the view can be easily queried from SQL Server, although I
don't see it in the trace.

Following is the trace when the form opens. Note, the extra selects are for
combo box rowsources.

SET NO_BROWSETABLE ON
go
SELECT vwPreappraisal.* FROM vwPreappraisal WHERE
((([vwPreappraisal].[ContactID])=6992) And (([vwPreappraisal].[ModeID])=11)
And (([vwPreappraisal].[BatID])=5783)) ORDER BY [RegionCode], [WineName],
[Designation], [Vintage], [Producer], [SortOrder]
go
set implicit_transactions off
go
SELECT "dbo"."tblMstWineNames"."WineNameID"
,"dbo"."tblMstWineNames"."WineName" ,"dbo"."tblMstWineNames"."RegionID"
,"dbo"."tblMstWineNames"."WineAppelID" ,"dbo"."tblMstRegions"."RegionCode"
FROM {oj "dbo"."tblMstWineNames" LEFT OUTER JOIN "dbo"."tblMstRegions" ON
("dbo"."tblMstWineNames"."RegionID" = "dbo"."tblMstRegions"."RegionID" ) }
ORDER BY "dbo"."tblMstWineNames"."WineName"
,"dbo"."tblMstRegions"."RegionCode"
go
SELECT "WineNameID" ,"WineName" ,"RegionID" ,"WineAppelID" FROM
"dbo"."tblMstWineNames"
go
SELECT "DesignationID" ,"Designation" FROM "dbo"."tblMstDesignations" WHERE
("DesignationID" > 0 ) ORDER BY "dbo"."tblMstDesignations"."Designation"
go
SELECT "dbo"."tblMstBottleSizes"."SizeID"
,"dbo"."tblMstBottleSizes"."BottleName"
,"dbo"."tblMstBottleSizes"."RegionId" ,"dbo"."tblMstRegions"."RegionCode"
FROM {oj "dbo"."tblMstBottleSizes" LEFT OUTER JOIN "dbo"."tblMstRegions" ON
("dbo"."tblMstBottleSizes"."RegionId" = "dbo"."tblMstRegions"."RegionID" ) }
ORDER BY "dbo"."tblMstBottleSizes"."BottleName"
,"dbo"."tblMstBottleSizes"."RegionId"
go
SELECT "SizeID" ,"BottleName" ,"RegionId" ,"SortOrder" ,"IsLargeFormat"
,"BottleCode" FROM "dbo"."tblMstBottleSizes"
go
SELECT "ProducerID" ,"Producer" FROM "dbo"."tblMstProducers" WHERE
("ProducerID" > 0 ) ORDER BY "dbo"."tblMstProducers"."Producer"
go
SELECT "VintageId" ,"Vintage" FROM "dbo"."tblMstVintages" ORDER BY
"dbo"."tblMstVintages"."Vintage"
go
SELECT "VintageId" ,"Vintage" FROM "dbo"."tblMstVintages"
go


Following is trace on an update:

exec sp_executesql N'UPDATE "InsertTest"."dbo"."tblMstItems" SET
"VintageID"=@P1 WHERE "WineNameID"=@P2 AND "AppellationID"=@P3 AND
"DesignationID"=@P4 AND "ProducerID"=@P5 AND "SizeID"=@P6 AND
"VintageID"=@P7 AND "ClassCode"=@P8 AND "UPC" IS NULL AND
"ItemID"=@P9',N'@P1 int,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8
varchar(1),@P9 int',108,1251,0,0,0,1,113,'N',3242949
go
exec sp_executesql N'SELECT
"WHID","ContactID","AuctionID","Quantity","LowBase","HighBase","LowEstimate","HighEstimate","Reserve","CalcOverride","PaGroup","Seq","NoteChoice","NoteId","ModeID","BatID","BoxCodeID","PaIsGroupLot","ChgHandl","HistNote","IsCurrent","WhTs"
FROM "InsertTest"."dbo"."tblWineHistory" WHERE "WHID"=@P1',N'@P1
int',3666809
go
exec sp_executesql N'SELECT "DesignationID" FROM
"InsertTest"."dbo"."tblMstDesignations" WHERE "DesignationID"=@P1',N'@P1
int',0
go
exec sp_executesql N'SELECT "ProducerID" FROM
"InsertTest"."dbo"."tblMstProducers" WHERE "ProducerID"=@P1',N'@P1 int',0
go
exec sp_executesql N'SELECT "AppellationID" FROM
"InsertTest"."dbo"."tblMstAppellations" WHERE "AppellationID"=@P1',N'@P1
int',0
go
exec sp_executesql N'SELECT
"WLID","ItemID","Levels","Label","Cork","Color","Other","DateCreated","CreateInitials","WlTs"
FROM "InsertTest"."dbo"."tblWineLots" WHERE "WLID"=@P1',N'@P1 int',5245783
go
exec sp_executesql N'SELECT "RegionCode","RegionDescription","RegionID" FROM
"InsertTest"."dbo"."tblMstRegions" WHERE "RegionID"=@P1',N'@P1 int',20
go
exec sp_executesql N'SELECT "WineName","RegionID","WineNameID" FROM
"InsertTest"."dbo"."tblMstWineNames" WHERE "WineNameID"=@P1',N'@P1 int',1251
go
exec sp_executesql N'SELECT "Vintage","VintageId" FROM
"InsertTest"."dbo"."tblMstVintages" WHERE "VintageId"=@P1',N'@P1 int',113
go
exec sp_executesql N'SELECT "BottleName","SortOrder","BottleCode","SizeID"
FROM "InsertTest"."dbo"."tblMstBottleSizes" WHERE "SizeID"=@P1',N'@P1 int',1
go
exec sp_executesql N'SELECT
"WineNameID","AppellationID","DesignationID","ProducerID","SizeID","VintageID","ClassCode","UPC","ItemID"
FROM "InsertTest"."dbo"."tblMstItems" WHERE "ItemID"=@P1',N'@P1 int',3242949
go

On starting a new record (first time only), the trace showed sp_fkeys and
sp_MShelpcolumns executing for each table contained in the view.

Following is the trace on attempting to save a new record. This is whre the
'Invalid Input Parameter...' error occurs in Access, which makes sense, as
it is trying to insert directly into a table.

SELECT ((1))
go
SET FMTONLY ON select
"WHID","ContactID","AuctionID","Quantity","LowBase","HighBase","LowEstimate","HighEstimate","Reserve","CalcOverride","PaGroup","Seq","NoteChoice","NoteId","ModeID","BatID","BoxCodeID","PaIsGroupLot","ChgHandl","HistNote","IsCurrent"
from "InsertTest"."dbo"."tblWineHistory" SET FMTONLY OFF
go
SET NO_BROWSETABLE ON
go
declare @p1 int
set @p1=-1
exec sp_prepare @p1 output,N'@P1 int,@P2 int,@P3 int,@P4 smallint,@P5
int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 bit,@P11 varchar(3),@P12
smallint,@P13 varchar(1),@P14 int,@P15 smallint,@P16 int,@P17 int,@P18
bit,@P19 bit,@P20 varchar(255),@P21 bit',N'INSERT INTO
"InsertTest"."dbo"."tblWineHistory"("WHID", "ContactID", "AuctionID",
"Quantity", "LowBase", "HighBase", "LowEstimate", "HighEstimate", "Reserve",
"CalcOverride", "PaGroup", "Seq", "NoteChoice", "NoteId", "ModeID", "BatID",
"BoxCodeID", "PaIsGroupLot", "ChgHandl", "HistNote", "IsCurrent")
VALUES(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21)',1
select @p1
go


Looking at a few more examples of the ResyncCommand, I think it's intention
is to resync edited records, rather than inserts.
I see what you mean about the parameter, but on on insert, there is no PK
value to substitute for the parameter.

Based on these results and David's info, while it was a good suggestion, I
think it is time to scrap the ADO solution.

--

AG
Email: npATadhdataDOTcom


"Banana" <Banana(a)Republic.com> wrote in message
news:4C17D91C.2040702(a)Republic.com...
> On 6/15/10 12:06 PM, AVG wrote:
>> I was able to try the ADO solution.
>> The form was not updatable at all until I set the UniqueTable property.
>> I could then perform updates. However, apparently ADO has a long reach.
>> The trace showed that the view itself was not being updated, but the
>> individual source tables.
>
> Crikey. That's just not right. I'm assuming your form is bound to
> something like "SELECT ... FROM myView ...", right? Can you post a trace
> of what SQL ADO passes back? Come to think of it, how could ADO know the
> source tables... Is it also updating other tables that wasn't specified in
> UniqueTable property?
>
> Also, it could be a driver-specific issue - does this persist if you
> select different driver/provider? (e.g. SQL Server instead of SQL Native
> Client for example -- even try MSDASQL)
>
>> I set the UniqueTable property to the name of the table that generates
>> the
>> PK,
>> and set the ResyncCommand to 'SELECT * FROM myview WHERE PkField =
>> SCOPE_IDENTITY().
>
> Hmm, I think we are supposed to give it a parameter so it should be
> actually "WHERE PkField = ?". Have a look at this page, especially the
> second part as that may give you more control:
>
> http://msdn.microsoft.com/en-us/library/ms676094(VS.85).aspx
>
> Another article discussing same concept:
> http://support.microsoft.com/kb/251021
>
>> Don't know where the error is actually generated from, but must be
>> completely within Access. I tried to catch it in the form_error event,
>> but
>> it doesn't appear there.
>
> For testing purposes, it's sometime desirable to try to do the same thing
> you'd have done via a form in the VBA. That way you can then examine the
> ADO's (or DAO's) Errors collection and thus get more specific error
> messages/information. Does it give you any more information?
>
> BTW, I'm not clear if you were able to insert anything with first attempts
> (without ReSyncCommand, etc) or just when you did the SCOPE_IDENTITY()
> thingy.
>
>> As for which tables are updatable, the view consists of 10 tables, only
>> three of which get updates or inserts.
>> The triggers are necessary because, one of the three tables should not be
>> updated. Where it appears to the user that they are changing a value, the
>> trigger either substitutes a different record or inserts a new one.
>> The other tables are necessary for display of related data and to be able
>> to
>> utilize custom sorting in (and from) other processes, like reports.
>
> Yeah, that's similar to my case where I used an ADO recordset because we
> had five tables joined and used for user-directed sorting/filtering and
> UniqueTable + ADO recordset was the perfect solution, though my case is
> much simpler because we only needed to update one table and not a view
> with a INSTEAD OF trigger.



From: Banana on
On 6/16/10 1:12 PM, David W. Fenton wrote:
> If I'm not mistaken, in this case, it's multiple tables in the
> recordsource, but only one is being edited (and, I presume, having
> records added to it).

Actually, AVG said 3 tables are being updated/inserted via a INSTEAD OF
trigger. He was following a MSDN article by Andy Baron that recommended
this very thing for Access.

> I have always been frustrated trying to use a single form of any
> kind for both adding and editing, and that's why in most of my apps,
> adding is done through a separate unbound form, then the editing
> form is requeried and then I navigate via bookmark navigation to
> thenewly-added record. This keeps the two processes separate. It
> also makes it harder for the user to accidentally add a record, as
> well as making it easier to abandon a record. The unbound form
> collects the fields that are required to create the record (and if
> not strictly-speaking required in the table definition, at least the
> fields that are necessary to have a usable, identifiable record).

Certainly understandable, and it does make things much more
straightforward. I've used similar technique but there are also places
where having a subform that does both editing/adding is more efficient
in terms of data entry than forcing a separate form. As long the client
understand the inherent complexity, tradeoffs and have been told of all
possible alternatives, I'm not going to argue with the client in area of
what UI/data entry process they want to have. (designing a
database/tables/relationship is entirely another matter, though)
From: Banana on
On 6/16/10 1:08 PM, David W. Fenton wrote:
> This is a known issue with ADO, most often encountered when you
> design your app so that users have no permissions on the base tables
> and use views to provide access. If you write a DML statement using
> the views, and it's not updatabase, ADO will try to do the updates
> on the underlying tables, bypassing all the security. It will fail,
> of course, since the user doesn't have the permission on the base
> tables.

Thanks. Better that I know about it by now. I also have to say that is
pretty stupid behavior.

> This is one of the many problems with ADPs (which depend on ADO)
> that causes Steve Jorgensen, for one, to conclude they were simply
> not usable in a production app.

I don't know who Steve Jorgensen is, other than that he used to post
here long, long ago. But I'm not surprised that ADP were problematic,
and ADO does have its baggage. This is why I tend to stick to DAO/ODBC
first and only turn to ADO if it can be used to solve a specific problem
(and has done with good results). Until Access team decide to give us a
replacement for ADO (specifically the ability to have disconnected
recordsets, asynchronous operations, along few other goodies) that's
what we have to work with.
From: Banana on
On 6/16/10 1:16 PM, AVG wrote:
> Yes, the ADO source is 'select * from myview where ...'.
> Yes, it is updating tables not specified in the UniqueTable property.
> Trying different providers:
> SQL Native Client - form is not updatable.
> SQL Server - error, class not registered.
> MSDASQL - error, Data source name not found and no default driver specified.

RE: MSDASQL, I think it requires a bit more tweaking to get it to work -
I can't remember off the hand but I remember I couldn't just change from
NCLI to MSDASQL and use same SQL statement or something like that. But
eh, forget it.

> The construct of the view can be easily queried from SQL Server, although I
> don't see it in the trace.

Good point. I suppose it could be done and may have been done using a
low level call or doesn't show up in trace because it doesn't match the
trace's criteria. (e.g. not a BatchCompleted statement for example)

> Looking at a few more examples of the ResyncCommand, I think it's intention
> is to resync edited records, rather than inserts.
> I see what you mean about the parameter, but on on insert, there is no PK
> value to substitute for the parameter.

That's true though I was thinking it still has to resync anyway after
getting the PK.

> Based on these results and David's info, while it was a good suggestion, I
> think it is time to scrap the ADO solution.

I would have had expected there to be a configuration option to suppress
ADO's silly long-reaching but if there were, it'd have been known by now.

The fact remains that you're still stuck with no good way to insert new
records without getting errors, be it #Deleted in DAO/ODBC or Invalid
Input Parameter in ADO/OLEDB. David already has mentioned using an
unbound form and keeping the addition/edit process separate. I think you
already indicated that this is not an acceptable option for you. The
alternatives are:

1) Use a local temporary table that mirrors the view structure and
commit the edits/inserts as a batch rather than one-by-one live. That
way you can then use VBA and thus leverage the server-side processing to
handle your data correctly while having the appearance of a bound form.
The trade off is that the changes are not live and your users would have
to commit them at a certain point of time. If you can live with few
minutes worth of delay then that shouldn't be a big deal. If client is
willing to pay for extra programming effort, you could shrink that
window by doing one-by-one update behind the scene.

2) I've seen but never used the idea of dynamically binding/un-binding
form. The idea seems to me more hassles than benefits.

3) Use Form Footer section to contain the unbound fields for inserting
new records. The problem is that you have to requery to get it to show
up once committed, and DAO doesn't support dynamic cursors (well, it
could via ODBCDirect but I fear that is even worse alternative than ADO
and it's deprecated as of 2007 anyway). At least the editing would stay
live and they could then insert more than one rows without problems but
need to requery to see those new rows.

I realize those are not the ideal solutions but that's basically the
cards we've been dealt. Maybe one of those will be able to meet your
client's requirement?

HTH.