Access 2007 bug with SQL Server Back End
in [Help]
|
From: David W. Fenton on 22 Jun 2010 18:59 Banana <Banana(a)Republic.com> wrote in news:4C20F6FB.6080100(a)Republic.com: > 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. I don't think those people understand the issues that managed code addresses. I know VB.NET is very different from VBA, but if they make a version specific to Office/Access, then I think I can likely adapt. It will be interesting to see if they can convert old code, though. The change from Access Basic to VBA was minor by comparison, I'd think. Something like the compatibility checker in A2010 for web deployment would seem to me to be the best way to address that (run the compatibility checker, fix up your code to meet the necessary requirements, then convert. Dunno how possible that would be, but I'm speculating about something that's likely a couple of Access versions in the future. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 22 Jun 2010 19:02 Banana <Banana(a)Republic.com> wrote in news:4C200C8B.7050307(a)Republic.com: > 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. Is this perhaps the same problem as with BigInt in SQL Server? That is, the server-side data type doesn't exist in Jet/ACE, so you have to work around it? If I recally correctly, BigInt autoincrement PKs are added correctly, but you have to cast them as string in order to display them. Maybe something like that is necessary with this data type in PostgreSQL. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Banana on 22 Jun 2010 22:43 On 6/22/10 4:02 PM, David W. Fenton wrote: > Is this perhaps the same problem as with BigInt in SQL Server? That > is, the server-side data type doesn't exist in Jet/ACE, so you have > to work around it? If I recally correctly, BigInt autoincrement PKs > are added correctly, but you have to cast them as string in order to > display them. Maybe something like that is necessary with this data > type in PostgreSQL. I checked and verified that the data type is a four bytes integer. But to satisfy my own curiosity - I went and made a modified view that would convert the column from integer to text and tried it out. I verified that Access correctly perceived the converted column as a text rather than its original integer. Got the same behavior of #Deleted nonetheless. Also, if it was represented as eight bytes integer (e.g. bigint) we wouldn't be able to do anything; all rows would show up as #Deleted as soon as it's opened. Tangent: PostgreSQL ODBC driver has an option to coerce bigint into other data type so even if it was using bigint, I'd be getting text anyway. In this case, we can update, delete, and even insert into this view without any problem. It's only when someone types something in a field that's not required then later clear that field prior to actually saving the record back to the source that we get the #Deleted behavior. This occurs Jet/ACE sends back "WHERE ... a_field = NULL ..." in its second attempt to find the newly inserted record, just as AVG reported in the OP. Obviously this is an impossible criteria and thus Access never find that newly inserted record. Returning back to the original view that shows the column as an integer, I tried to insert value by hand and it functions just fine, which lines up with what I observed with the other view using natural keys. I then found out that my earlier idea of using nextval() does in fact work - it failed first time because in my rule, I had new id autogenerated in the insert, thus tossing out whatever would be in the id. When I changed that rule to insert the value as it is, nextval() now works correctly and thus can be used to protect from the #Deleted and is more robust than my other kludge of undoing then redoing. Unfortunately, that may be problematic to implement against a SQL Server or MySQL backend as either doesn't support the concept of sequences and thus there's no multiuser-safe nextval() to call. One could kludge by creating a little one row, one column table to hold the highest id and use a serializable transaction to obtain a new value. I'm doubtful that is a better solution than the undoing/redoing kludge I mentioned in other branch.
From: Banana on 22 Jun 2010 22:55 On 6/22/10 3:59 PM, David W. Fenton wrote: > I don't think those people understand the issues that managed code > addresses. I know VB.NET is very different from VBA, but if they > make a version specific to Office/Access, then I think I can likely > adapt. It will be interesting to see if they can convert old code, > though. As a matter of fact, they already have something... not to convert the code but to enable interoperability between VBA and VSTO code. I can't find the site I originally saw but hope this should cover everything adequately: http://msdn.microsoft.com/en-us/library/bb931201(office.12).aspx http://channel9.msdn.com/shows/In+the+Office/Extend-your-VBA-with-NET/ That means the application that replies on code can be migrated from VBA to VSTO in staged migration, which may be more easier to sell than converting everything at once then dealing with bugs that crops up from conversion.
From: Tony Toews on 23 Jun 2010 01:19
On Sun, 13 Jun 2010 16:41:53 -0400, "AVG" <NOSPAMagiamb(a)newsgroup.nospam> wrote: >I am posting this at the suggestion of Tony Toews in response to another of >my posts. Wow. has my suggestion ever spawned an interesting thread. I'll take complete credit. <smile> Tony |