Link to home
Start Free TrialLog in
Avatar of Brendan Maloney
Brendan MaloneyFlag for United States of America

asked on

Access migrating to SQL Backend - How to force a blank return record to insert new record, and why are my ado recordsets uneditable in forms.

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.
Avatar of ste5an
ste5an
Flag of Germany image

1) Sounds like it the security model using application roles. Indicator: it works being admin..

2) Views in SQL Server are updateable, when you edit only one base table at a time. Thus this basically works.
BUT: How should the user know that? That's were you can use INSTEAD OF triggers. You route the different edited values to there tables.

Well, the question is: What kind of editing should the user do? This could be the case for a local temp table.
Avatar of Brendan Maloney

ASKER

Well...to clarify it works as admin when I'm using linked tables and the query is in Access.  Without admin using application roles i have to use recordsets and the query can't be written that way in SQL (recordset is uneditable like that).  This would be where the INSTEAD OF trigger you mentioned might work.  I'll try to read up on that and see what I can figure out.  Is there a recommended resource you might have on how this works?

A Local temp table would like to be avoided, but maybe i can make one 'on the fly' as an ado recordset that doesn't actually exist since it will technically only contain one record at a time it can just be in RAM vs making the database bigger.  My other option would be to add the extra fields to the main data table even if they'll ultimately stay blank or just contain the last inserted information, but otherwise be ignored and then use VBA to route the data to the destination table after update.  At least that way all of the data transferring would stay on the SQL side which is much faster (preferrable).
How to force a blank return record to insert new record
Inserting blank records is likely the wrong solution.
What "admin" are you talking about?  In SS, every user needs permissions to update data only but not to manipulate or create objects.  They would never have admin rights.

I've used many views over the years and never had a problem with updating them.  Typically records from one side only are updated and I'm pretty sure that you can't insert records into both tables (except in Access) but I wouldn't swear to that.  I have never, ever had to use instead of triggers in over 25 years of creating Access apps with SQL Server and other RDBMS BE's.  However, all my Access apps use bound forms and linked tables.  Occasionally I need a stored procedure for a complicated report or non-updateable search form.  I also sometimes use pass-through queries for bulk deletes.  I NEVER use forms bound to ADO recordsets that I have to manipulate to update the database.  It's not wrong, Access allows it but it is not "natural" and you lose the RAD tools of bound forms.  If I had an application that I couldn't implement with bound forms, I would never use Access as a FE.  It has too much overhead and baggage.  If I am not going to use it as a RAD tool, I don't use it.

Just FYI, native Access uses DAO recordsets for bound forms and there are instances where DAO and ADO work differently.  This could be one of them.

I would reexamine the reason you are making this huge application change.  Please start by explaining the admin reference.
@Pat

EDIT: OK this post got a bit out of control so i'm going to break it into two posts as i think both may end up being TL:DR xD.

Let me try to simplify as much as I can mostly because I don't want 'other issues' to confuse the problem.  (Forgive me if i get too wordy i have that problem, but i want to be clear as I AM getting frustrated xD).  I don't typically run into many problems i can't figure out on my own.  As far as my reference to 'admin' ...yes the account i was testing on IS an admin account, but frankly I was simply referring to any account that had inherent read/write/execute permissions to the Azure SQL backend.  When I initially rebuilt this client for SQL I used linked tables/views.  I have a fully working version that pulls everything in mostly the same format we had previously only much much faster with backend SQL processing (even just having the Azure server at s7 we tested with about 30 simultaneous users pulling 300k+ records and submitting data and it was pretty smooth...we may bump to s9 when we get our full 200 users on board for this pilot.  Ultimately this will hopefully become a more enterprise solution that will be deployed across multiple projects/jobsites throughout the company.  In the long run we will be leveraging a better FE client than Access, but I did this whole project in a relatively short amount of time and I had to convert an existing access database with access backends to one with azure sql backends and assist our corporate team with getting us the data we normally pull from existing SQL databases into our access databases via nightly loaders as access macros/VBA in windows task manager.  

Now...for security purposes we don't want anyone accessing the data without permission.  Okay...it requires active directory integrated authentication...part 1 done.  Now the client loads a DSN on the local computer that is used to connect to the server.  It doesn't have any passwords in it, but it does have the server name and etc so conceivably someone could use that dsn to make their own access database and if they are already an authorized user the could map to the tables...see the raw data and make their own queries which we do NOT want.  The solution to this is application roles.  Users are places only in the public group on the server which pretty much only gives them rights to successfully connect to the server and run a handful of specific stored procedures.  They can't even run a basic select statement at this point, nor can they get a list of the table names.  This resolves the issue.  Of course they need to be able to run select statements from the actual program so the solution is application roles.

Embedded in the compiled VBA code of the project (we only release .accde versions with the ribbon and task bar turned off, shift bypass disabled, and special keys disabled so they're pretty well locked down to only running via the forms that pop up and the buttons at their disposal) is a variable that stores an elevation procedure call 'EXEC sp_setapprole <app role name>, <app role password>'.  The users can't see this, nor do they even know it's happening, but now the users will effectively be running all procedures with the permissions of the app role rather than their own permissions.  The self permissions are just what allows them to connect to the db and to get new connections from the connection pool that I then each have to make sure are flipped to the app role.

Phew...ok i think i over-archingly answered the admin explanation.  Basically in the version without the app role security i can use the bound forms and linked views/tables and it works fine, but since i have to add the security the only way to guarantee that i'm using a connection with the approle assigned to it is via ADODB connection and then linking everything else to that connection.    UNless of course i'm missing a better way to do this for which I'm all ears.
Now to the inserting blank record piece let me try to explain that better.   For one...my title might suck because I don't know really how to explain it better.

In the bound form/linked table version here is what i had done.  I had a form that showed data from a view and had blank fields that were part of a query on a single table joined on key fields from that main view.  The idea is that the form would show data from the view (non of those fields could be modified) to help identify what record they were on and then there were blank bound fields effectively attached to an "insert" on a table..as soon as you'd start typing in one of the blank fields it would begin appending a record to that table.

Now the way this was achieved is by creating a query on the destination table that always returned false (Basically it was like SELECT * from tbl_Progress WHERE 1=2.  It always returns false but it's still bound to the table so any entry effectively gets appended as a new record.  Creating a left join from the source view to this table allowed me to show the data from the view and then insert new rows into the table.  Here's the caveat...apparently this doesn't work in SQL directly.  The view was built in SQL, but i had to build the 1=2 query and the join query in access.  It's based on a SQL table, but there was one query that was the select query...then another query that joined the SQL view to the new blank query for the destination table.

The problem now is that i can't use any access queries or linked queries like think anymore because there's no way for me to guarantee that the connection it will pull from the pool on the SQL server will already be elevated.  Even if i try to run a pass-through query that calls the sp_setapprole command first i may still end up pulling an un-elevated connection on my next connection call...the only way i can guarantee it is by using the ADODB connection that i directly elevate before using it.    I'm hoping someone can help me logic a way to complete this.

I'm still going to look into step5an's suggestions of maybe making an on the fly fake table to temporarily store the data in ram and then send it, or looking into the triggers on the SQL server and see if i can get them to allow me to tell the view that in that state the function to perform is INSERT INTO.
1. Security - SQL has come a long way since I first starting using it as a BE to my Access apps and I don't think the roles feature was available at that time.  However, since you can't seem to implement the roles without rewriting the FE, you might consider implementing something like what I came up with 25 years ago to solve this exact problem.  My app was being used by a bunch of engineers who were technically savvy and were using Access for themselves so clearly  having them know the userID and password to connect to the application database was a recipe for disaster so the DBA and I came up with a method of generating a passward for each user.  The Access app maintained logon security using the network ID and a password specific to the Access app.  The tables were linked when the app opened using the neteorkID and the generated password.  We wrote the code in VBA and the DBA had a tool that generated a password for him each time he had to add a new user.  The seed for the code used the networkID.  I can't share the code.  There are other methods you might employ though.  Keep in mind that there is no problem if user1 and user2 come up with the same password string.  The generated password doesn't have to be unique.  There just has to be a sufficient range of them so that no one could easily guess what theirs was.  Since user names are unique in themselves, that gives you a good seed.

2. Using a left join should provide the "empty" fields and still allow the insert to work.  This definitely works with Access but might require a trigger in SQL Server.  It is EXTREMELY suspicious that you would want to insert a "lookup" row when you add/update a main table row so I really do not understand this requirement.  Access does provide a NotInList event which might be what you should be using instead of the method you chose.

3. I thought you said that the linked tables method worked fine until you started trying to use the roles.  If it did, see if you can use my suggestion #1 to find a way to come up with a password that the users don't know.  That will prevent them from linking to the tables in your BE from an Access app that they create themselves.
1&3 - I think i get what you're saying and I had though of something like this so let me see if my explanation/understanding is correct.  To clairfy first, yes, my bound form non app role version works perfectly.  Rather than having users authenticate through active directory as they are now possibly i have them authenticate through say SQL username/password.  When the account is created it uses something like a hash algorithm on the username to generate the password.  In this manner the password is always the same, but it is unknown to the user.  The algorithm can be coded into the Access application and when the username is pulled the password generated and thus passed to the SQL server for login.  As a basic example lets just say the algorithm was to increment each ascii value by 1 and reverse it then the password for johnb would always be coipk (that's super basic, but just to examplify).  I like the idea...it's a little crude but it could work.  If i can't get this fixed by Friday I may consider it as I don't think it would be TOO hard to implement depending on the corporate office involvement because of course they manage the SQL server.
i have them authenticate through say SQL username/password.
The point is THEY do NOT authenticate because they NEVER know their SS password for your database.  They do log into your app unless you can let Active directory manage that also.  YOUR assessment of how this would work is correct.  The Access app generates the same password that the code the DBA used does.

Getting buy in from the PTB will be tricky.  They probably don't like Access to begin with so they will not like having to actually manage the users themselves rather than relying on roles and groups.  I'm not exactly sure of the procedure they currently use to add a new user but this may be slightly more work.  You can control the credential generation from your side and just give the UID and password to the DBA but I don't like that.  It adds a person into the loop who knows the password who shouldn't and it also requires that the generation procedure be defined outside of the app so your replacement or the system admin replacement can be taught which also adds risk.

There's very little about Access that I don't like but it is over 25 years old  and that is ancient as far as software goes.  The fact that it is still alive and well in very close to the original vision is mind blowing and a real testament to the prescience of the original development team.  I bow down before them but pieces of it have gotten old. and I think you are running into something that the developers simply could not envision and so made no provision for.

I know everyone wants something "better" than Access but I've been doing development since 1968 and pretty much been there, done that and nothing has been easier to use to quickly develop infinitely scalable (as long as you never share FE's and always use an RDBMS as the BE) data centric apps than Access.  Clearly Access cannot do everything, but what it does do, it does very well.  The place where Access begins to fail is when the app itself gets too big to be managed by a single developer.  If you need a team to produce an app, Access will be clunky at best even if you use a source code management tool.
Just my view about security:

AD user authentication is sufficient.

Cause it is not relevant how users connect to the database. They can only access views, procedures and functions depending on the AD security group they belong to. The AD groups are logins/users in your database and they have roles assigned which grant the minimum permissions.

Why does this not lead to havoc, let the RDBMS do the heavy lifting:

1) Normalization (NF0-NF5, BCNF, DKNF).
2) Constraints, constraints, constraints. E.g. each candidate key gets its UNIQUE constraint, each column its default (when possible). And a lot of CHECK constraints.
3) Use read-only views for listings and overview for simple aggregation.
4) Use procedures and functions for more complex data aggregations. They can do user based filtering on data transparently on user or group level.
5) Restrict edit views (insert/update/delete) to a single unit of work using a filter in the view.
6) Simple and medium complex logic can often be seamlessly implemented using INSTEAD OF triggers on that views.
7) Complex logic for DML is put into procedures. Again here you can filter whether it can be executed like are deletions allowed.
8) Use DateTime/User created and changed columns in each table and history tables (plain row copies) to allow simple audit. Or use temporal tables and built-in audit.
9) Especially for Access - now I'm getting inconsequent, cause here I'm using Hungarian notation: Use separate views for each form and report. I have schema.frm_FormName or schema.rpt_ReportName for designated consuming objects in  Access. This makes change management easier. General lookups are schema.cat_EntityOrTableName.
10) Use Linked Tables as well as passthrough queries using those SQL Server objects.
We are using AD Integrated authentication right now, but here is the problem.  The users here can and have gotten in 'backdoor' access to this and other non access databases in the past if they weren't properly secured.  Connecting to the database requires there to be a DSN.  Currently the database creates this, but then the users just have to create a new access database and use the DSN and bam...backdoor access to all tables and they can make their own queries and even edit the data without data validation.

I spoke to main office this morning and they don't want to manage SQL users in addition to AD because then if someone had an AD account removed becuase they left employment, they could still access the DB if their SQL password was still live.  Now that said it's also bound by IP address range, but if someone works from home and they get that address white-listed then there's that too (or at least that's what corporate said).  Though IMO to work from home they'd need to VPN first and use that instead.

The working version of the access database is done solely as you have mentioned.  It's all pass-through's and linked views.  I thought of DSN-less connections, but there are too many instances where variables need to be passed so pass-through queries are needed.  Hmm actually though the connection string is stored in those so maybe a DSN-less connection would work.  Just loop and link the tables/views on startup with the connection string and make the pass-through's have the server name and that might just work.  Then they'd never even know the server name.

I'm playing around with it now.
hmm, I guess that's the problem: "not properly". Using application roles won't solve this, when not done properly which is also hard. Imho the complexity of security management is in both cases the same. The only "benefit" with application roles is simple obfuscation.

Just start by not giving permissions to tables. This is almost half the battle. The rest is normally won by a clean architecture. Cause from the viewpoint of security: The client (application) does not matter at all. Just the measures in the backend do.
ste5an,
Once a user has a valid UID and password and a little knowledge, he can use Access to link directly to SQL Server tables and UPDATE them if his credentials allow. Roles might solve that but I don't know since this is not my area of expertise.  I explained how I solved the problem 25 years ago.  It is a problem that has existed since day 1 of Access but is not talked about with any regularity so MS has never attempted to address the issue.  The good side is that most users are incapable of setting up their own database and linking it to the server.  SS and Windows security has changed a great deal since that time.  If you create an "Access" app from scratch that manages all the data interaction manually, you can get around this.  But the reason I put Access in quotes is that would not be an Access app since it does not take advantage of any of the RAD features of Access.  If you can't use the RAD features of Access or don't want to because you think they are inferior, you ought not to bother using Access.  You are giving up all the good stuff and are stuck with only the bad stuff.

Brenden,
I was pretty sure you were going to run into a wall with the PTB regarding anything remotely resembling "extra" work.  Since your intention is to replace the FE, can you convince them to step up and help for a year or whatever it takes to come up with a permanent solution?  An inferior solution is to not grant the users ANY SS permissions to your database and behind the scenes use a general user account so that when the user logs into the app, you link the databases using a hard coded UID and password that has only user data permissions (never admin permissions).  This is inferior because it masks actual usage and if you ever have to do some analysis to track down a problem, you have no way of isolating Joe's traffic from Suzy's.  But, it will get you over this hurdle.  Hopefully, the DBA will consent to this as a temporary solution.  He may want to set the password to expire in an unusually short time frame to minimize the exposure if the password gets out.  This will be annoying but you can keep on top of it and it is a small price to pay for not having to recode the entire app to produce an interim solution.
I think you need to take a look at this.
@Pat:
Once a user has a valid UID and password and a little knowledge, he can use Access to link directly to SQL Server tables and UPDATE them if his credentials allow.
Sure, when you grant read and write access to users on tables. BUT - as I already wrote - this is what we should not do. And keep in mind, granting those read/write permissions is also an active step.

Use views and procedures which resemble your applications workflow. Then it is irrelevant whether the data is changed by the application or after connecting to the database directly. There is no difference by terms of security. The only difference would arise from a weak relational model. But done properly (constraints, triggers and procedures) then this is no longer a thread. When you need to do this on an existing database the solution is pretty simple: Create those views in separate schemata's or a separate database on the same host.
If a view is updateable, it is updateable when the user creates a blank Access database and links to it so I'm not sure how views help.  Using stored procedures is a completely different development model than the typical Access bound form so that is a bridge too far..
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.