Access 2007 bug with SQL Server Back End
in [Help]
|
From: AVG on 21 Jun 2010 08:43 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 21 Jun 2010 20:56 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 21 Jun 2010 21:06 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 22 Jun 2010 09:05 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 22 Jun 2010 13:46
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. |