From: Banana on
On 6/18/10 4:52 PM, AVG wrote:
> I have gotten a tremendous amount of pressure this week to finish the
> project, so since the issue only rears it's head if user deletes the data in
> a non-required field, it will be taking a back seat to the balance of the
> project for now.

Best of luck. I do hope you find a solution your client will be happy -
if it's something different or creative, I'd love to hear about it.

> Thanks again to both you and Banana. While we did not find a solution, I
> certainly learned some things - one of the reasons that I like this
> business.

FWIW:

As part of teaching myself PostgreSQL, I actually thought of your
situation and put this to practice. I was able to write a INSTEAD OF
rule upon a three-table view that would represent one row collectively
and found that it even manage to handle the case where a non-required
field is filled then deleted prior to actually inserting the whole row
into view "virtually". I didn't get a #Deleted in that case.

I don't have an analyzer result - too immature with my PostgreSQL-fu at
this point. I can only say that a rule isn't a true trigger.
PostgreSQL's rules are basically a way to rewrite/expand the incoming
SQL statement and happens after parsing the query but before creating an
execution plan (so the docs say...). Whether the fact that it was a rule
or because PostgreSQL / its ODBC driver does something right (even if it
technically is a bug), Access didn't mind that at all.

Not that I would think it practical at this point to suddenly change the
backend server under your client. ;) But at least we know it _could_
work somewhere else.
From: David W. Fenton on
Banana <Banana(a)Republic.com> wrote in
news:4C1D6BE3.7070504(a)Republic.com:

> On 6/18/10 3:40 PM, David W. Fenton wrote:
>> ODBC may be evolving, but is Jet/ACE's interface to it evolving
>> along with it? So far as I'm aware, it's not.
>
> Good point. I hope it'll be the case for next version. I think the
> biggest favor they can do at this point is to open up this part
> for configuration. If you're familiar with ADO.NET, there's a
> means of specifying InsertCommand, UpdateCommand, and
> DeleteCommand. Just to have those properties and thus override
> Access' default (which tend to work OK for several cases but will
> fail in boundary cases just like this one we looked at in this
> thread) would be significantly simpler than trying to update and
> fixing bad assumptions Access makes in executing those commands in
> response to bound forms' activity.

Is it Access or Jet/ACE that's making the bad assumptions?

>> I see ODBCDirect as similar in purpose to the ADP, which is to
>> avoid Jet, which I see as completely misguided from the
>> beginning. Note that both ADPs and ODBCDirect are de facto
>> deprecated (though ODBCDirect is *really* deprecated in A2010).
>
> As I said, it's deprecated and I believe it's actually so in 2007,
> not 2010.

Well, while it may have been deprecated in A2007, I believe you
can't use it at all in A2010 (but I could be misremembering).

[]

> It'd be utterly backward of Access team to not provide a
> VBA-like replacement.

Yes, but I understand why they'd want to get away from such a
completely open-ended scripting language.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: AVG on
AG
Email: npATadhdataDOTcom


"Banana" <Banana(a)Republic.com> wrote in message
news:4C1D6BF7.2010801(a)Republic.com...
> On 6/18/10 4:52 PM, AVG wrote:
>> I have gotten a tremendous amount of pressure this week to finish the
>> project, so since the issue only rears it's head if user deletes the data
>> in
>> a non-required field, it will be taking a back seat to the balance of the
>> project for now.
>
> Best of luck. I do hope you find a solution your client will be happy - if
> it's something different or creative, I'd love to hear about it.
>
>> Thanks again to both you and Banana. While we did not find a solution, I
>> certainly learned some things - one of the reasons that I like this
>> business.
>
> FWIW:
>
> As part of teaching myself PostgreSQL, I actually thought of your
> situation and put this to practice. I was able to write a INSTEAD OF rule
> upon a three-table view that would represent one row collectively and
> found that it even manage to handle the case where a non-required field is
> filled then deleted prior to actually inserting the whole row into view
> "virtually". I didn't get a #Deleted in that case.
>
> I don't have an analyzer result - too immature with my PostgreSQL-fu at
> this point. I can only say that a rule isn't a true trigger. PostgreSQL's
> rules are basically a way to rewrite/expand the incoming SQL statement and
> happens after parsing the query but before creating an execution plan (so
> the docs say...). Whether the fact that it was a rule or because
> PostgreSQL / its ODBC driver does something right (even if it technically
> is a bug), Access didn't mind that at all.
>
> Not that I would think it practical at this point to suddenly change the
> backend server under your client. ;) But at least we know it _could_ work
> somewhere else.
Interesting. Was that with Access 2007? Or an earlier version?
Does PostgreSQL pass the identity (autonumber) property of a field in the
view?
If it does, that would explain why it works.




From: Banana on
On 6/19/10 8:32 PM, David W. Fenton wrote:
> Is it Access or Jet/ACE that's making the bad assumptions?

I was tempted to say Access because I observe this problem far more
often via a bound form scenario than when I manipulate Jet/ACE via VBA.
However, thinking about it, it could actually be Jet/ACE - So, I set up
a quick test running a recordset adding new record, editing the same
record then deleting it, all in VBA and watched MySQL's general log as I
stepped through the operation. I actually couldn't finish the operation
because as soon as I passed .AddNew, Jet asked for a new record with
criteria "WHERE ID IS NULL", which is sort of reasonable since it was
null when I added it but that's no longer true and should have passed
back a @@identity or whatever. Because of that, my attempts do
".Bookmark = .Lastmodified" landed me on a "deleted" record. So it's
definitely Jet/ACE making the bad assumptions.

Thinking about it, it seems that it just happen that I run into that
problem more often with a bound form only because when I do it in VBA,
it's typically some variant of PTQ or where navigating won't be againts
an edited recordset.

> Well, while it may have been deprecated in A2007, I believe you
> can't use it at all in A2010 (but I could be misremembering).

Hmm. I know for fact that DAP is completely gone in 2010, but don't know
if that's true for ODBCDirect. No matter as it was junky technology anyway.

> Yes, but I understand why they'd want to get away from such a
> completely open-ended scripting language.

Well, if we are to assume that VSTO is the future, I can see how it'd
work out in such way that the Access wouldn't come with open-ended
language out of the box but if you had VSTO, you could do anything with
it. That'd probably be the most neat solution for all parties involved,
save for the open question of whether one really can continue to create
Access applications with just macros. I'd imagine by far large majority
of Access applications simply wouldn't exist without VBA and thus a
suitable replacement and restricting it to requiring a Visual Studio
license may be too excessive.


From: Banana on
On 6/20/10 6:34 PM, AVG wrote:
> Interesting. Was that with Access 2007? Or an earlier version?

On 2003.

> Does PostgreSQL pass the identity (autonumber) property of a field in the
> view?
> If it does, that would explain why it works.

Come to think of it, the sample data I used had natural keys. There is a
table that does have a serial data type (which is what PG call their
autonumber/identity) but in that view it's a child table to the main
table that uses a natural key. The view doesn't show the serial number
for that child table. FWIW, though, I have configured PG ODBC driver to
emulate SQL Server's identity behavior.

I should try and switch the order other way to verify that it works even
with the autonumber being used as a keyset to navigate the recordset.
Probably won't be until next weekend before I can get to play with PG
again. Thanks for pointing that one out.