June 25, 2021 at 2:38 am
We have a MS Access database that we have separated the tables and now have Azure SQL linked as our backend tables. But, I'm wondering if I can also convert the Access queries to SQL views and maybe stored procedures. It would be great to just have the Access front end be the forms only with the tables and queries in SQL. My questions are:
Thanks,
JP
June 25, 2021 at 2:57 am
Put all your queries and stored procedures as close to the data (back end) as you can. Especially if it's in Azure.
I think I can put multiple Access append queries into one SQL view with multiple select statements, correct?
But why? Explain what you're trying to accomplish. Access's queries are absolutely awful. You could create a common table expression (like a view) and then query that... But without more specific details, it's hard to suggest an approach.
But, I'm wondering if I can also convert the Access queries to SQL views and maybe stored procedures.
Views in SQL Server can't take parameters, and can't have an ORDER BY clause (well, unless you do that TOP 100 PERCENT trick). If you need to pass parameters into your query, use a stored procedure.
And if you use VBA in your queries, rewrite them in SQL Server.
June 25, 2021 at 3:23 pm
It’s been a while since I’ve worked with Access upsizing. Hope this outline helps.
Do your queries need to return complete tables? Usually, for best performance, apply joins and WHERE clauses within SQL Server. Return the minimum data set to Access. This often involves passing query parameters from Access to SQL Server.
Andy Couch, an Access MVP, suggested a method that worked for me. It involves creating a SQL Server table with one row per user. Use a column that defaults to SQL Server function SUSER_SNAME() as the primary key. Call column something like ‘this_user_name’. Add additional columns representing superset of all parameters you need. Easiest to use ‘natural’ column names such as county_name, last_name rather than parameter1, parameter2 etc. Give these SQL Server columns suitable data types and default values, avoiding NULL. This simplifies joins, adding new columns and adding new users. Check constraints would also be good. For example, to prevent input of single quotes and semi colons in character strings.
Create a SQL Server view that pulls back the user’s one row by checking that ‘this_user_name’ equals SUSER_SNAME(). Include clause WITH CHECK OPTION and do not return 'this_user_name'. The user view is then bound directly to Access parameter input form(s), making it easy to pass parameter values to SQL Server.
Represent Access select queries by SQL Server views which source user specific query parameters from this new user table. Technique can also be applied to SQL Server stored procedures which represent Access update or delete queries.
A different issue. If the Access database uses composite foreign keys then, in SQL Server, you should create a check constraint such that every column in the foreign key is NULL or every column in the foreign key is NOT NULL. This is because Access and SQL Server handle partial match of composite keys differently. I try to avoid composite foreign keys…
All the best with your project.
Bredon
June 25, 2021 at 5:46 pm
Thank you Bredon!
Regards,
JP
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply