This is not urgent help. You may reply if you get time. I am trying to learn different terms of MS Access.
I am reading about stored procedures. From what I have read, have understood that these are faster, takes less memory, can be triggered.
This a bunch of SQL statements, called as per the requirement. However if any expert here can tell me in details what exactly are these.
Or what actual purpose it serves, or any good links that will help me for reading, it would be of great help.
If you are using an Access FE, you may or may not ever need a stored procedure. That would be my last option with my SQL Server et al applications. Using a sp wouldn't even cross my mind unless I had a slowness problem that I couldn't solve by optimizing my query or using a view.
A stored procedure is simply procedural code stored in the SQL database. The language used is T-SQL and it is very limited in capabilities compared to VBA. Yes, for some things, you will see dramatic performance improvements. But, for 99% of what you are doing, you will never need them. If the stored procedure returns a recordset, you can bind it to a form or report. It will not be updateable of course. I have used them in two situations.
1. for very complex reports that need to join many tables
2. for very complex search forms that need to join many tables.
I've been using Access with SQL Server, DB2, Oracle, and a half dozen lesser known RDBMS. I use bound forms that use queries as their recordsource to limit the rows and columns returned. Since my forms are bound to recordSources with criteria, I never use form filters except possibly to further reduce already filtered recordsets on the way to opening a single record edit form.
Hi @Pat Hartman sir, thanks a lot for the detailed answer. I am going through your post. Please allow me some time to revert.
My work will be more of a back-end i.e. using Access through VBA hence am trying to learn from that perspective as well.
PS - Starting from the basic things however was reading about SPs too hence the post.
Access is a RAD (Rapid Application Development) tool. Its purpose is to create interactive interfaces using bound forms and reports. My applications contain various types of code but mostly related to validation and batch workflows such as emailing monthly statements to all sales people. If you are thinking that you need to use unbound forms/reports. Think again. you have all the control you need once you understand how the form event model works.
If you are not using bound forms and reports, you'd be much better off with a tool that has a lot less overhead such as vb.net.
I have used stored procedures in combination with an app as it allowed us to bypass some limitations with regards to triggers on tables we used to fire messages off to a queue that could then perform actions based on the total data available across many different tables.
The input came in from a power app which flat out refused to work with tables that had triggers.
The power app worked fine with stored procedures.
Later we thought it was a nifty thing as we could simplify the app and minimize the data being sent as the stored procedure could do some of the lifting for translations of a´various input items.
Pat. Please stop disparaging TSQL and trying to discourage Access developers from using it. You clearly are not well versed in its utilisation.
TSQL has CLR integration and can call assemblies which use .NET code. CLR integration can do anything that can be done in VBA, only much better. As .NET is compiled, it is vastly more efficient than VBA which is only interpreted.
As Stored Procedures support multiple TSQL statements, they can natively perform many of the processes that can only be done by VBA in Access.
TSQL can do many things that VBA is required for in Access. It intrinsically supports typed variables and can concatenate strings straight from multiple records using a query without need a loop. Even a dynamically created table can be a variable and then used in a query. Parameterised functions can return table variables.
Stored Procedures can call sp_executeSQL which is the equivalent of DoCmd.RunSQL in Access. The important difference is that the Execution Plan for sp_executeSQL can be cached so are more efficient.
TSQL can natively and efficiently concatenate the records from related table without requiring a call to any code as is required in Access.
TSQLs FILESTREAM can read files. FILETABLES present the contents of files in table columns without any code at all. The contents of dozens of filetypes can be indexed and TSQL can perform contextual searches using those indexes. For example, finding two words or their grammatical variants (plurals, and other cases of verbs) within a specified distance.
TSQL can create a cursor which is the equivalent of opening a recordset in VBA then iterate through the records.
Hi @Pat Hartman sir, thanks a lot for the detailed answer. I am going through your post. Please allow me some time to revert.
My work will be more of a back-end i.e. using Access through VBA hence am trying to learn from that perspective as well.
PS - Starting from the basic things however was reading about SPs too hence the post.
@Galaxiom I'm sorry if my statement offended you. I was referring to the ability of the sp to interact with the Access FE. We are after all talking about developing using Access the RAD tool not some .net environment. There is a difference between using Access as a RAD tool and writing everything your self. If you are going to write code at that level, you should be using a tool that doesn't come with the heavy baggage a RAD tool like Access comes with. I haven't written a sp in a few years. Do they now have debugging capability? Can I step through the code?
@Galaxiom I'm sorry if my statement offended you. I was referring to the ability of the sp to interact with the Access FE. We are after all talking about developing using Access the RAD tool not some .net environment. There is a difference between using Access as a RAD tool and writing everything your self. If you are going to write code at that level, you should be using a tool that doesn't come with the heavy baggage a RAD tool like Access comes with. I haven't written a sp in a few years. Do they now have debugging capability? Can I step through the code?
I didn't take offence. I simply pointed out what you said was wrong. AWF is supposed to be a place to share knowledge and it needs to be pointed out when wrong information is posted.
It is not the first time you have disparaged TSQL. You have been around a long time in this industry and nobody doubts you abilities in Access. Clearly you don't particularly want to learn something new at this stage of your career but that is no reason to be discouraging other developers from getting into TSQL.
I made it quite clear by quoting the incorrect statement I was referring to. You went well beyond "referring to the ability of the sp to interact with the Access FE" by claiming TSQL had "very limited capabilities compared to VBA". This is just plain nonsense.
TSQL has all the fundamental requirements of any programming language to control the flow of processing using conditional branches and loops. In fact most of what can be done in VBA can also be done directly in TSQL without having to resort to .NET integration, which is only required for advanced processes such as calls to COM objects where higher performance is needed.
The Intellisense and error highlighting in SQL Server Studio is vastly superior to the VBA editor. It means there are far less mistakesto debug when writing TSQL than writing VBA. Sections of code can easily be executed in isolation by simply highlighting them and pressing Execute. And yes, stepping though the code is supported in the studio.
Learn how to use the Transact-SQL debugger to control which Transact-SQL statements are run in a Database Engine Query Editor window.
docs.microsoft.com
There are several things that Access SQL is terrible at doing and developers must resort to cumbersome queries or VBA to achieve what SQL Server handles right inside the engine with a simple construct. ROW_NUMBER() is probably one of the best examples of functionally desperately missing from the Access engine.
All the questions about Allen Brow's ConcatRelated() function are rendered obsolete. SQL Server just does it.How much code has been written for splitting up strings? TSQL has STRING_SPLIT() that returns a table from the string.
I've seen people struggling with elaborate code to read text documents into VBA and produce indexes. SQL Server just does it at an advanced level without any code at all.
TSQL queries can directly read Access databases and Excel documents without having to link or import them.
Writing roll your own security in Access is at best a Clayton's outcome unless great lengths to hide the code are taken. Loads of Access developers try to read the computer login information and build security around it. Meanwhile SQL Server has robust fine grained, Windows authentication aware security built in. If you need security, forget Access and put the data in SQL Server.
For that matter, SQL Server can link directly to Active Directory and present it as a View. I remember years ago writing VBA to get that information into tables.
If developers are content doing simple stuff then Access is a great tool. But way too many developers give themselves headaches trying to do complex things in Access that are trivial in TSQL. Far too many Access developers are wasting time that would be better spent learning SQL Server.
BTW Although it isn't essential to be able to do it at all, learning a bit of .NET to write your own high performance compiled functions is trivial compared to a full blown .NET graphical interface, so saying the developer might as well abandon Access if they use any .NET cannot be justified. The real strength of Access is its rapid interface development. Underneath that is a very limited database engine and programming language. It isn't particularly difficult to hand out some of that work to TSQL.
Creating dlls written in VB.NET using Visual Studio is not a particularly steep learning curve for an experienced VBA developer. Ninety percent of typical VBA code will import straight into VB.NET project in Visual Studio. It will adapt some by itself and then literally suggest what needs to be done to the rest of it. You can do fancier techniques in .NET especially for connections but it still understands the foundational VB dialect quite well. Once you know a couple of things about constructing the project framework, it is really not difficult.
The main thing to grasp is that .NET is object oriented so many things are procedural in VBA are done with a Method.
For example you don't use:
Code:
strWhatever = CStr(variablename)
but
Code:
strWhatever = variablename.ToString
Since all .NET variables are objects there is no SET key word required. Visual Studio will automatically drop SET from pasted VBA code.
Otherwise at the simplest level, VB.NET isn't that different from VBA.
That is pretty harsh. Are you sure you are not being defensive? I don't do that. The point I make is that TSQL and passthrough queries should not be the FIRST choice when developing with Access and SQL Server. Not that you should never use them. I've never said they were inherently bad or even inferior. I also say code shouldn't be your first choice when working with a RAD tool. That doesn't mean I think VBA is bad or that you should never write any code. You need to write whatever code you need to write to do the things that Access can't do for you. I happen to agree with you that Access SQL is inferior. It hasn't changed since the 90's! But, there is a great deal of bad information out there regarding Access when used with SQL Server and consequently people get the impression that if they use SQL Server, they MUST use unbound forms and stored procedures and pass through queries. That is simply not true.
so saying the developer might as well abandon Access if they use any .NET cannot be justified.
We should always use the best tool for any particular job. If VBA and querydefs can't do the job, then by all means use whatever SQL Server has to offer. But, if you are going to start out ignoring the best features of Access, then you shouldn't be using Access at all. Access has an extremely heavy footprint because it is a RAD tool. If you are not going to use the RAD features, why take the resources hit?
Hi experts,
Apologies for delay in response. I am reading each post from learning perspective.
Thanks a lot for your valuable time and help to my question.
Have a nice weekend.
