0

I am in the process of converting an ADP (Access Data Project) to an ACCDB. So far, it has gone pretty well, except that the views no longer seem to be updatable now that they are actual links into the SQL Server database.

Specifically, the following VBA works fine for tables, but not for views:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append

The 3rd line throws the exception "PasteAppend isn't available now" -- and it is absolutely correct (the paste menu is greyed out). But, to reiterate, this problem did not occur for ADP views using the same SQL server database.

Unfortunately, Microsoft has discontinued support for ADPs, so we are forced to migrate.

Any suggestions how to get around this?

UhClem
  • 129
  • 3
  • 10
  • You describe the real problem as more about ensuring the queries are updatable, less about problems with the macro code. Instead, share details of the SQL queries, the linked tables, etc. – C Perkins Aug 08 '17 at 01:55
  • This is actually form VBA. What we have is a bunch of forms with record sources that are either linked tables or linked views. In the case of table source, the user can duplicate (paste append) a record or create a new record just fine. But if the source is a view, it no longer works. To C Perkins's point, I could choose one of the linked views and provide specific details, but initially I wanted to keep the question at a high level. As in, "Has anybody encountered this?". Again, the views were updatable when they were ADP, but not so now that they are linked. – UhClem Aug 08 '17 at 17:17
  • I can sympathize with you on trying to gathering info before jumping to conclusions. Perhaps you only want help from people who've experienced this exact issue. But you asked for suggestions "how to get around this?" Stack Overflow (SO) is not appropriate for very high-level "Have you seen this?" yes/no questions. A proper SO question is an answerable question with detail. Even you said in your question "it is absolutely correct (the paste menu is greyed out)"... indicating that the problem is with the view not the Macro. So yes, I suggest that you post view/query details. – C Perkins Aug 08 '17 at 18:11
  • C Perkins, normally I am accused of providing too much detail! :) But I really could use some guidance on this issue, and will be happy to provide whatever info will get me to a solution. In this case, I am unsure exactly what type of details would be helpful. Are we talking about the actual SQL Server view? It has not changed. It was (and still is) updatable via ADP. Could there be something in the Access link properties? I'm not sure what there is to look at, other then the ODBC connection string. – UhClem Aug 08 '17 at 19:41
  • Well I'll admit that I'm not familiar with ADP. My initial recommendation was only a general suggestion so that your question would not be marked down, flagged or ignored by others. I'm not certain of how much detail you need to share right now, only that any details of the ODBC connection and linked view may be useful in the long run, more so than the macro code you shared. Perhaps reworking the question title might also get more responses. – C Perkins Aug 08 '17 at 20:01
  • I did a web search for `access cannot update sql server view` and found https://stackoverflow.com/questions/19554800/cannot-delete-and-update-records-on-access-linked-table. See the last answer also (zero votes). The key to solving this may be specifying unique/primary keys on the table or view. I'm aware that ADP used OleDB rather than ODBC and maybe it was easier/better at automatically determining unique columns to update a record(?). There were also other hits on that search and other variations returned numerous links that might be promising. – C Perkins Aug 08 '17 at 20:07
  • C Perkins, you have steered me in the right direction once again, (The "last answer" referenced above now has one vote.) The problem was indeed the lack of a unique key. This is not as straightforward as it might sound. I have learned a lot since posting my previous comment. I will attempt to write up a thorough answer tomorrow. – UhClem Aug 09 '17 at 07:02
  • Glad I could help, especially since I wasn't familiar with ADP. I just do my best to give good feedback before others start to downvote and abandon the question. I had the feeling this was resolvable, just had to get to the heart of the issue. – C Perkins Aug 09 '17 at 07:23

0 Answers0