Solved Book & Research Recommendations Azure BE > Access FE

[dalski]

Can anyone recommend a good book exposing common pitfalls...? I'm a complete novice in both Access & Azure. Seems that using an ODBC connection in MS Access exposes passwords... in the registry.... Also as I still have not finished my application which I am planning to sell as an .accde & Azure BE I am realising now that:

• Think I've made a mistake using functions in calculated queries does not seem a great idea in Azure; causing the BE in Azure to reconnect with the front-end greatly slowing down the app. Though Philipp Stiefel mentions this is looking better in MSSQL Server 2019. Layering the queries also brings me problems in the application crashing on the third layered query but maybe there's a problem with me referencing a bad incomplete calculated field in a query. It was getting very slow in the query editor on the third layered query so I jumped between the two. It's probably me with a bad reference or something
• Seems I should probably be using ADO recordsets (but rebind after updating due to bug in Access)
• I've already based most forms on queries... reducing the size of the recordset being worked with; though can get realy tricky with queries based on other queries with several tables & acheiving the new query to be editable

Research Material Known About

• Fundamentals Of Azure 2nd Edn - Michael Collier, Robin Shahan - does not touch on common pitfalls of MS Access & is more for hardcore languages/ web development
• Backends For FrontEnds Pattern - Microsoft Learn - does not seem relevant to MS Access
• Extend Microsoft Access Applications To The Cloud - Andrew Couch - Seems largely based on obsolete MS Access Web Apps & scanning contents I can't see any benefit here with guidance on common pitfalls with MS Access
• Access 2010 VBA Programming Inside Out - Andrew Couch - (read it); inital scan Azure was in the beta stages of development.
• Youtube codekabinett.com/eu - SQL Server Authentication - very good & based on @isladogs very helpful Security Challenges exposing weaknesses in MS Access to watch out for

Grateful of any guidance & tips. I'm posting a bit early as I'm trying to implement best practices for an Azure setup & know nothing about everything.

 

[Minty]

I'm not aware of any books covering the potentials and pitfalls of using Azure and Access as FE I'm afraid.
I have however developed a number of apps with exactly that set up, and most of what I know is based on experience and finding out the hard way...

You should move any FE functions into the backend source views, unless they are used on single records/very limited recordsets.
As you have discovered having function calls in Access on Azure linked tables is a non-starter for performance.

Layering queries - do this in SQL Views, there should be no need to do this in Access, SQL Views are much more tolerant of remaining editable with complex joins and sub queries than an Access query. They will also join multiple tables and handle sub queries much more efficiently than doing the same in Access.

You can make and then delete the ODBC connections in code on demand, which would hide the password and not be viewable in an accde.

 

[dalski]

move any FE functions into the backend source views, unless they are used on single records/very limited recordsets.

Layering queries - do this in SQL Views, there should be no need to do this in Access, SQL Views are much more tolerant of remaining editable with complex joins and sub queries than an Access query. They will also join multiple tables and handle sub queries much more efficiently than doing the same in Access.

Thanks @Minty, good news that SQL Queries are more powerful as I'm working on a bit of a pig at the moment. I'm not sure if I should try to upload to Azure & see how i go from there. I imagine this will slow me down massively as I'm so new to it & learning Access was hard enough (still know very little), or continue in Access where I'm more comfortable & trying to implement as much good practice with Azure as possible on my limited understanding. Then when it's all working then go to Azure? I think I'm going to fall to pieces in Azure with such limited info out there & being such a novice.

 

[isladogs]

I'm also not aware of any books specifically related to your question and don't consider myself an expert in Azure
You should be able to find some AccessUserGroups videos that would help your research

Just to correct any possible misunderstanding from a comment in post #1.
In the final link provided, with my agreement, Philipp Stiefel did an excellent demonstration of Access weaknesses based on an early version of my security challenge:

Encrypted Split No Strings

This example app shows how an Access database can be made reasonably secure against hackers whilst still allowing full functionality to authorised users.

www.isladogs.co.uk

Following that very helpful deconstruction, I completely rewrote the app to secure all the areas of weakness that Philipp exposed so successfully in that session

 

[dalski]

Just to correct any possible misunderstanding from a comment in post #1...
Following that very helpful deconstruction, I completely rewrote the app to secure all the areas of weakness that Philipp exposed so successfully in that session

Thanks Colin, was in no way meant to be a dig; but a token of gratitude for the valuable lessons on your site, & as stated in tutorials they were all designed to be solvable. Very grateful for the info.

 

[tvanstiphout]

> using an ODBC connection in MS Access exposes passwords
Not if you use this tip:

Power Tip: Improve the security of database connections | Microsoft 365 Blog

When linking to external tables using Open Database Connectivity (ODBC), Access offers to set up a Data Source Name (DSN). The DSN is quite convenient, giving us a graphic method to quickly build and specify an ODBC connection string without having to remember all of the syntax and parameters...

www.microsoft.com

With an Azure BE, you cannot program as-if you are on a LAN with an OnPrem SQL Server. The connection is likely 3 orders of magnitude slower.
It is important to cache all static tables, and even tables with regular adds and updates can be cached to some extent, as long as the cache expires each time the underlying table is updated.
It is important to do as much processing on the server as possible. That means server-side views (indexed if possible), functions, and stored procedures. Of course we need a high-quality database design.
Stacked queries like you wrote about can be acceptable, *if* in the BE.
You'll need to understand the tooling SQL Server has available for monitoring and optimizing performance.

All of the above requires expert programming. It may be too much to ask for a beginner.

 

[GPGeorge]

Although I also know of no good books on Access/Azure SQL, there are a lot of parallels between hosted instances of SQL Server and Azure SQL. You should find a lot more relevant resources by including SQL Server in your research.

The main design consideration, IMO, is that Access FEs with any remotely hosted databases need to be architected as Client-Server applications. The client -- Access -- should be as light-weight as possible, handling primarily interface tasks. All of the data management needs to be done in the Server database, or at least as much of it as possible.

That suggests that VBA functions that are data focused should probably be rewritten as Stored Procedures in the Server database. Views on the server should replace local queries in large part, although sometimes parameterized local queries can be reasonably performant. Think of it in terms of one general rule:

Move the least possible amount of data back and forth across the connection.

If you are populating a form for editing, load ONE record at a time in that form. If you are aggregating data for a report, do as much of that as possible in views, stored procedures or pass thru queries.

 

[isladogs]

Thanks Colin, was in no way meant to be a dig; but a token of gratitude for the valuable lessons on your site, & as stated in tutorials they were all designed to be solvable. Very grateful for the info.

It didn’t come across as a dig.
I just wanted to clarify how and why that video was done
I was very grateful to Philipp for exposing the weaknesses in that app as it prompted me into making significant improvements in subsequent versions.

 

[dalski]

Thanks guys, I get the gist to do as much as possible in the BE. Do we think carry on in Access, then think about Azure once I get it all working, or move to Azure now, whilst continuing to develop & test? I imagine I'd need to convert datatypes on incompatible types... & many things would that are working now, would no longer work until adressed in Azure. To which I see myself struggling at the first hurdle, not to mention the remaining hurdles.

I take no offence at all to my limited capabilities, few familiar faces here so we have a rough idea on my limited capabilities at this moment in time.

 

[Minty]

@dalski If you can get used to writing the queries in SQL Server, the syntax is virtually identical bar a few things (Text delimiters, Date Formats and string concatenation are a couple that spring immediately to mind) Get used to using YYYY-MM-DD.

You will then avoid some of the gotcha's that come when upsizing/converting, like referring to calculated fields within the same query they are created (You can't do that in SQL Server), and Access's awful nested way of displaying table joins.

There are also several really useful functions in SQL Server that can save you a bunch of time and effort.
Look at String_Agg(), Row_Number(), Concat_WS() as some neat ones for starters.

They take a little getting used to but (particularly the windowing functions) will outperform anything you can do in Access.

 

[GPGeorge]

Thanks guys, I get the gist to do as much as possible in the BE. Do we think carry on in Access, then think about Azure once I get it all working, or move to Azure now, whilst continuing to develop & test? I imagine I'd need to convert datatypes on incompatible types... & many things would that are working now, would no longer work until adressed in Azure. To which I see myself struggling at the first hurdle, not to mention the remaining hurdles.

I take no offence at all to my limited capabilities, few familiar faces here so we have a rough idea on my limited capabilities at this moment in time.

I would treat it as a normal software development project.

• Leave the current production application alone, except for required maintenance or bug fixes.
• Treat the new version as a development application, regularly updating data if necessary for testing as you go.
• Deploy a test version of the new architecture when it is ready, running it alongside the Access production application during that test phase.

 

[BlueSpruce]

Can anyone recommend a good book exposing common pitfalls...? I'm a complete novice in both Access & Azure.

Attached is a draft document I dug up on Access/Azure performance that I think will be useful to you and others:

 

[tvanstiphout]

Who is the author of this Access,Azure,Performance.pdf document?

 

[dalski]

Thanks for all your input guys. This video from Sean MacKenzie Data Engineering was very helpful along with your help also.

One or two relationships have not imported but that's fine I'll sort them out. Interestingly I hooked up Azure & am using MSSQL Server 2022 also. Presumably Sean did this as it's a better management system.
Also Sean did not use wizards to import the tables, but used VBA DoCmd.TranferDatabase to import the tables, I think to avoid local a 'Local Machine Data Source'; which I think in future deployment would force prompting users to link all these on other computers I think when setting-up db. I'm probably using the wrong term here. ODBC connection ver 18 did not work, but 17 did. Posting to help others in future.

 

[GPGeorge]

Thanks for all your input guys. This video from Sean MacKenzie Data Engineering was very helpful along with your help also.

One or two relationships have not imported but that's fine I'll sort them out. Interestingly I hooked up Azure & am using MSSQL Server 2022 also. Presumably Sean did this as it's a better management system.
Also Sean did not use wizards to import the tables, but used VBA DoCmd.TranferDatabase to import the tables, I think to avoid local a 'Local Machine Data Source'; which I think in future deployment would force prompting users to link all these on other computers I think when setting-up db. I'm probably using the wrong term here. ODBC connection ver 18 did not work, but 17 did. Posting to help others in future.

The link you posted doesn't appear to work. Can you try again?

I'm not sure what you mean by "...it's a better management system." Please clarify.

Also, could you respond to Tom's question in Post #13? Thank you.

 

[dalski]

Thanks George,

1 - Sorry about that, edited link to YouTube video of Sean Mackenzie's guide on hooking up Azure with MSSQL & Access, should work now.

2 - Post 13 - I did not post the link/ file so I have no idea who the author is. I posted a link to Sean Mackenzie's video of setting up Azure > MSSQL > Access is all. I think you've misdirected this question to me; no probs.

3 - Using Azure > MSSQL > Access 'Better System'; what I mean is presumably Azure's interface (being an online app through a browser) is not as nice/ customizable as MSSQL. So presumably the reason Sean used another layer of application (MSSQL) underneath Azure before it is transferred to MS Access is because it has increased functionality. From a logical point of view I would think as the data is transferred through another application (MSSQL), it is another unnecessary HTTP request... & has an overhead/ processing cost associated with it. But the transfer from MSSQL to Access must be much less-performance degrading because it is local. Noetheless another processing cost I would've thought; if that's clear. My amateur first perception is that this is because of the awesomeness that MSSQL brings with it.

 

[BlueSpruce]

2 - Post 13 - I did not post the link/ file so I have no idea who the author is. I posted a link to Sean Mackenzie's video of setting up Azure > MSSQL > Access is all. I think you've misdirected this question to me; no probs

I posted the document. It's a draft paper I contributed to 10 years ago but was never published. It does contain valuable insight on optimising Access with remote db's.

 

[GPGeorge]

Thanks George,

1 - Sorry about that, edited link to YouTube video of Sean Mackenzie's guide on hooking up Azure with MSSQL & Access, should work now.

2 - Post 13 - I did not post the link/ file so I have no idea who the author is. I posted a link to Sean Mackenzie's video of setting up Azure > MSSQL > Access is all. I think you've misdirected this question to me; no probs.

3 - Using Azure > MSSQL > Access 'Better System'; what I mean is presumably Azure's interface (being an online app through a browser) is not as nice/ customizable as MSSQL. So presumably the reason Sean used another layer of application (MSSQL) underneath Azure before it is transferred to MS Access is because it has increased functionality. From a logical point of view I would think as the data is transferred through another application (MSSQL), it is another unnecessary HTTP request... & has an overhead/ processing cost associated with it. But the transfer from MSSQL to Access must be much less-performance degrading because it is local. Noetheless another processing cost I would've thought; if that's clear. My amateur first perception is that this is because of the awesomeness that MSSQL brings with it.

My mistake. It was not posted by you.

 

[GPGeorge]

Thanks George,

1 - Sorry about that, edited link to YouTube video of Sean Mackenzie's guide on hooking up Azure with MSSQL & Access, should work now.

2 - Post 13 - I did not post the link/ file so I have no idea who the author is. I posted a link to Sean Mackenzie's video of setting up Azure > MSSQL > Access is all. I think you've misdirected this question to me; no probs.

3 - Using Azure > MSSQL > Access 'Better System'; what I mean is presumably Azure's interface (being an online app through a browser) is not as nice/ customizable as MSSQL. So presumably the reason Sean used another layer of application (MSSQL) underneath Azure before it is transferred to MS Access is because it has increased functionality. From a logical point of view I would think as the data is transferred through another application (MSSQL), it is another unnecessary HTTP request... & has an overhead/ processing cost associated with it. But the transfer from MSSQL to Access must be much less-performance degrading because it is local. Noetheless another processing cost I would've thought; if that's clear. My amateur first perception is that this is because of the awesomeness that MSSQL brings with it.

The link still doesn't work for me, but that's okay, we can find his YouTube channel and maybe track down the video you mention.

Point #3, having worked with both Azure SQL and SQL Server, I can understand the point about user interfaces. SSMS is, at least in my experience, easier to navigate than the Azure portal.

That said, I'm not convinced anyone would implement a dual layer SQL Server --> Azure SQL architecture simply because of the interface. For one thing, SSMS connects equally well to Azure SQL instances as it does to local SQL Server or hosted SQL Server instances. Once the database is set up, there's little further need to use the Azure management tools because it can be managed through SSMS just like other instances of SQL Server.

Rather, I suspect (pending finding the appropriate video on that YouTube channel) that one possible reason for that architecture could be having a local instance of SQL Server that performs faster than a remote instance, complemented by a process that syncs the local SQL Server data with the Azure SQL data to make it available to web apps, etc. That's just my speculation of one possible reason I would do that. There may well be others I'm not thinking of at the moment.

 

[dalski]

Thanks George, yes I thought it strange. Strange the link to Youtube didn't work. I've edited it again just in case. Trying again via 'Media' in case that works.