I'm currently migrating a Microsoft Access client with a Microsoft Access backend to an Azure SQL backend. I have a fully working version of the client, but that was before we implemented security with Application Roles. In order to ensure the users are getting an elevated connection I've been slowly rewriting the client to use ADO recordsets with the SQL in the VBA rather than using access queries tied to linked views/tables and pass-through queries. This has brought up several questions, but let me go ahead and just ask 2 for now and i can put the rest in another thread as they may not be fully related to this question (plus they're the most important).
Simple question first:
1) I am creating ADO recordsets that select views when forms load to tie them to the Form.recordset property. I am using a client side cursor (Location) with adOpenDynamic (Type) and adLockOptimistic (LockType). When I do this the forms are no longer editable. I've confirmed in VBA that the recordset.iseditable property is true for the SQL statement. In most cases the SQL statement is as simple as "SELECT * FROM <view name>". The view is editable in SQL Server Management Studio, and the linked view is editable if I'm using an admin account that I don't need to elevate via application role so I know it's not an issue with joins. Is there some other curor/recordset setting I'm missing to ensure that when I bind an ADO recordset to a form it remains editable?
2) I also have a more complicated query. I can provide more information on this if it's confusing, but I'll try to describe it as best I can. I have a form that needs to display a series of unique records for progress on a project. All of these records correspond to a view I've already built. 2 of the fields also correspond to key fields in a second table that tracks each time a user enters progress for that unique combination of two keys (The second table has additional fields that make each entry unique, but that isn't relevant here). Previously, I left joined the two fields from my unique table to a query on the second table that always returned 'false' (e.g. SELECT * FROM tbl_Progress WHERE 1=2). What this allowed me to do was place fields from the 2nd query/table on the form and have them always be blank. When a user would begin entering data into these fields of the form since no matching record existed it would default to add a new record. The only way I could get this to work was having this query made in Access. I'm not sure if there is a feature of the Access client that allowed this to work properly, but it did work nonetheless. I tried recreating it completely in the Azure SQL backend, but the resulting query would never be editable likely due to the 1=2 constraint always being false. I have been told there is no way to get this to work properly in SQL, but I'm still curious if there may be a way via some trigger or an "INSTEAD OF" constraint. I'm not very familiar working in that area so if that's the path to a solution any help would be great. On the other hand, if there's a way I can handle this with multiple ADO Recordsets or another method my brain has happened to miss I'd love to get some feedback. I can provide screenshots and examples if needed. I would like to note that I'd prefer to avoid using some kind of local table storage and a submit if possible because then any possible collisions wouldn't be handled live and we're not doing batch updates. We're trying to keep any input handled record by record for all of the client side events.