From: AVG on
From what I have seen, the factor that determines how Access requests the
new record is whether or not it recognizes a field that is an 'autonumber'.
If it recognizes an autonumber, it will request the new record using
@@identity, otherwise it will use the fields that had data entered into
them.

Just open the linked view in design view in Access and see it there is a
field with data type of autonumber.
--

AG
Email: npATadhdataDOTcom


"Banana" <Banana(a)Republic.com> wrote in message
news:4C1EE33A.9040009(a)Republic.com...
> 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.



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

> 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.

Eh?

I'm not sure I understand what you're talking about with VSTO.

I would assume that if they replace VBA with .NET, the IDE will be
there within Access for writing .NET code. I would also assume it
would be limited to managed code, which means certain things about
what kinds of external components you can reference.

I don't consider that a terrible thing, actually.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Banana on
On 6/21/10 5:43 AM, AVG wrote:
> From what I have seen, the factor that determines how Access requests the
> new record is whether or not it recognizes a field that is an 'autonumber'.
> If it recognizes an autonumber, it will request the new record using
> @@identity, otherwise it will use the fields that had data entered into
> them.

If I open the linked table itself, Access correctly identifies it as an
autonumber. But in a view containing the same linked table, it's just a
"Number". Also, it seems that my early success was a false promise -
when I created a new multiple-table view using the identity column as
unique index, I get the same #Deleted behavior only if I insert then
delete non-required data.

PostgreSQL uses a sequence generator so I thought that if I went and
inserted the next value from the sequence it would save me. To my
surprise, it didn't. I would have thought that the problem was related
to the fact that it had to get the new ID back from the server by using
all other columns but shouldn't be a problem if ID was fed in and thus
available to Access for adding, as was the case with my first view that
used natural keys. I'm still scratching my head over that one.

In my efforts to understand the logs (PostgreSQL's logs are downright
ugly compared to either SQL Server or MySQL logs!), I thought of this
workaround and it does seem to work. See if you can replicate that with
the code below:

Private ctl As Access.Control 'iterator
Private KeyValues As New Collection

Private Sub Form_BeforeUpdate(Cancel As Integer)

Set KeyValues = Nothing

For Each ctl In Me.Controls
If ctl.Tag = "Insertable" Then
KeyValues.Add ctl.Value, ctl.name
End If
Next

Me.Undo

For Each ctl In Me.Controls
If ctl.Tag = "Insertable" Then
If Not IsNull(KeyValues(ctl.name)) Then
ctl.Value = KeyValues(ctl.name)
End If
End If
Next

End Sub
From: AVG on
In the beginning, I thought that the PK should be the driver, but
unfortunately, it is the Autonumber property that Access needs and I have
found no way to propogate that to Access.
Defining the PK in the view itself or within Access via DDL makes no
difference to the problem.

Your solution looks very promising. I won't get to try it for a few days,
but will do so.

--

AG
Email: npATadhdataDOTcom


"Banana" <Banana(a)Republic.com> wrote in message
news:4C200C8B.7050307(a)Republic.com...
> On 6/21/10 5:43 AM, AVG wrote:
>> From what I have seen, the factor that determines how Access requests
>> the
>> new record is whether or not it recognizes a field that is an
>> 'autonumber'.
>> If it recognizes an autonumber, it will request the new record using
>> @@identity, otherwise it will use the fields that had data entered into
>> them.
>
> If I open the linked table itself, Access correctly identifies it as an
> autonumber. But in a view containing the same linked table, it's just a
> "Number". Also, it seems that my early success was a false promise - when
> I created a new multiple-table view using the identity column as unique
> index, I get the same #Deleted behavior only if I insert then delete
> non-required data.
>
> PostgreSQL uses a sequence generator so I thought that if I went and
> inserted the next value from the sequence it would save me. To my
> surprise, it didn't. I would have thought that the problem was related to
> the fact that it had to get the new ID back from the server by using all
> other columns but shouldn't be a problem if ID was fed in and thus
> available to Access for adding, as was the case with my first view that
> used natural keys. I'm still scratching my head over that one.
>
> In my efforts to understand the logs (PostgreSQL's logs are downright ugly
> compared to either SQL Server or MySQL logs!), I thought of this
> workaround and it does seem to work. See if you can replicate that with
> the code below:
>
> Private ctl As Access.Control 'iterator
> Private KeyValues As New Collection
>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>
> Set KeyValues = Nothing
>
> For Each ctl In Me.Controls
> If ctl.Tag = "Insertable" Then
> KeyValues.Add ctl.Value, ctl.name
> End If
> Next
>
> Me.Undo
>
> For Each ctl In Me.Controls
> If ctl.Tag = "Insertable" Then
> If Not IsNull(KeyValues(ctl.name)) Then
> ctl.Value = KeyValues(ctl.name)
> End If
> End If
> Next
>
> End Sub



From: Banana on
On 6/21/10 5:56 PM, David W. Fenton wrote:
> Eh?
>
> I'm not sure I understand what you're talking about with VSTO.
>
> I would assume that if they replace VBA with .NET, the IDE will be
> there within Access for writing .NET code. I would also assume it
> would be limited to managed code, which means certain things about
> what kinds of external components you can reference.

Right now, VSTO is not packaged with the Office but with Visual Studio.
Therefore to do anything in the Office via managed code environment you
would need to get a certain version of Visual Studio. Also, it used to
be a separate product as well. Not anymore, it seems.

Who knows - they may change the structure and package VSTO to go with
Office when it actually replaces VBA editor rather than being an
alternative but that's not how things are set up right now AFAICT.

> I don't consider that a terrible thing, actually.

Nor do I. However, I've been surprised by backlash among other people
who has been programming in VB/VBA at the idea that managed code should
be the successor.