February 1, 2011 at 8:34 am
I've got a large DB composed by: [1 Access front-end with VBA] + [1 SQL backend].
I need to update a view in the SQL back-end, using an APPEND query in the front-end. This should be easy and work fine, but it's not. The reason for this is most likely that the SQL view is generated using a SQL table-value function, with code that returns a table.
I know the view must be updatable because some Access forms that use VBA/recordsets/etc DO update the data on the same view. However, my APPEND query (that doesn't use VBA) cannot copy or add records to the view, so there must be reason for that, and not the query itself.
Questions are:
1. Any ideas on how to update views from Access when the table-valued function don't work, or why it is not working?
2. Related with this: how you make a query in SQL server updatable or not? Is there a checkbox or something that makes it updatable or not?
Thanks in advance, a.
February 1, 2011 at 9:22 am
It's been a few years since I worked with Access-on-SQL (sounds like that should be the name of a village in England), but if I remember correctly, the key thing is that the Access code can only update/insert/delete to one table at a time. I remember there being a setting in the Access forms that picked which was the "target table" (I don't remember what it was called, but it's something like that).
Take a look at the data properties in the form you're using, see if it has something to indicate that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2011 at 9:58 am
Thanks GSquared but I think there's a lot more going on. To summarise it, these are some of the usual scenarios and related questions on the UPDATE ISSUE I've posted:
1) Just Access file: watch form Properties, or the query that populates a form. Same for updates, append queries, etc/
2) Access + SQL server: same as 1, plus the SQL server query that creates a view and populates the form. (Q: when are these queries updatable?)
3) Access+ VBA + SQL : same as 1-2, but the VBA code (usually recordsets) can (apparently, I've seen this happen) update queries that one could not update on the SQL backend (Q: why this happens?)
4) Access+ VBA + SQL + functions in SQL: same as 1-2-3, but the SQL functions may change when a table/view are updatable. (Q: when and why?)
Hope this helps to make it clearer, al
February 2, 2011 at 6:29 am
This article has data on when a SQL Server view is updatable (scroll down a bit to find the section on it): http://msdn.microsoft.com/en-us/library/ms187956.aspx
Edit: Given the right code, any view can be updated by updating the underlying table(s) directly. Most likely, the VBA updates are on the underlying tables, bypassing a non-updatable view. I've done that trick in VBA in Access. You can also update an non-updatable view by having "instead of update" triggers on the view. This again bypasses the view itself, going to the underlying tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply