Access ADP Strategy needed.

Hi Experts,

I am used to do the following in an Access MDB.
when needed to change multiple subforms record source based on what is filtered in main form.

Have all the subforms be based on a query and change the queries SQL property in code when user selects all the filter options and clicks the filter button.

Now in ADP I am wondering how can this be accomplished?

Just to give an example what I am looking to accomplish.

I have a form that includes 2 sub forms regarding totals of employees records, one is grouped by City and the other by Month hired, and I would like the main form should have the options to filter what kind of employees they are looking for, and the filter should reflect both subforms.  

P.S. Perhaps there is a way to change a view's sql by code?
However I thought that could affect other users trying to filter at the same time.
(Waiting to hear from users if its intended to be used by more then one person at the same time..)
So first lets hear from you guys what's the right strategy here..
LVL 6
bfuchsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Just curious - are you not able to use the Master/Child link fields of the Subform Control to manage the filtering? In general, if the two datasets are related, you can simply set those fields and let Access take care of the rest.

If you can't then - are these records intended to be edited? If not, you could always adopt the temporary table approach. Create a Stored Proc that will fill a table, based on the parameters you pass in, and then base your subform on those temp tables. Clunky, but it'll work.
PatHartmanCommented:
Everyone should have their own personal copy of the FE.  Therefore, everything that happens in the FE is localized to a specific user.

BTW, ADP's have been deprecated.  It might be time to think about a conversion path.
BitsqueezerCommented:
Hi,

of course you can do the master/child linking which Scott described above also in ADPs, no problem. It's more a question on which way you used to fill the forms with data.
To successfully use linking all of your forms need to have the RecordSource property filled, if you do that with VBA instead Access cannot do it alone. The subforms should simply use a view which returns all records, then Access takes care of adding the needed WHERE.

Changing a view with code is indeed a bad idea at least for the reason you said on your own: You do not know who is working with it when you change it. A view should be as static as a table until you develop a new version.
That doesn't meant that SQL Server cannot use dynamic SQL strings, for that purpose it has a special stored procedure named "sp_executesql". You can assemble a SQL string and use that to execute that. But that also opens your database for SQL injection in many cases so it's better to avoid it if you do not have a good strategy against that. And in many cases it is also not needed to use that method.

If I did understand right what you said above you have grouped queries in your subforms which maybe doesn't include a field which you can link with your main form. But there is of course a method to trigger that, in the simplest case use the Form_Current event of the main form (if that's a continous form and you want to update the subforms on record change) or a button click code if you prefer that. If you cannot refresh the subforms with "Me.subformContainer.Form.Requery" here, then you can instead create a Public Sub in the subforms (I use "SetRecordSource" as standard name for that purpose) which you can call from the main form with "Me.subformContainer.Form.SetRecordSource" (and optionally any parameter if you need). The subform can then assemble the needed SQL and use that as RecordSource.

In my current project which is a very big ERP system and also an ADP (doesn't interest me what Microsoft recommends about ADPs as Access is getting more and more a toy instead of a professional development tool, beginning with A2010, ADPs really rocks!) I use a more "independent" solution. Here I have an event message class which sends event messages to all loaded forms and they decide what to do then on their own. Means: If I have a main and subform the main form sends an event message "I have changed me, whoever hears that, do something with this information" and all other loaded forms get that and decide, if they need to requery themselves, maybe with data from the event message like an ID, or do anything else with the message. So in fact the subforms do not "know" that they are not standalone forms, they always get informed over events so that I could also use the same subform as popup form instead without any change in code. Means: The modules are independent from each other and don't ever access each other directly (like with the method described above to directly access a Public Sub of the other form). Works, but also means more discipline in programming as it is tempting to direct access other modules without the event system for a quicker result.
A basic version of that method is described here in my article: How to synchronize forms using own events

Cheers,

Christian
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bfuchsAuthor Commented:
Hi Experts,

@Scott,
The story here is as follows, I am only planning to use this main form as a container for
1- all the subforms.
2- place all the filter options there, including the filter button.
Now whatever is being filtered on main form should reflect all sub forms.

Being that the case, I don't see why I should use Master/Child linking here, as this will require I include all those filter options as fields in the subforms recordsets, and for the examples above, I dont think they can even be included (lets take for example a total of employees braked down by cities, nothing besides of count(EmployeeID) and City belongs there).

Re your option to use a temp table, I was thinking about that, however wondering if that would not cause a slow in performance, as data would need to be deleted and reloaded for every filter?

@Pat,
Not sure how the fact that everyone has their own copy of FE can help with this case in ADP projects, as there are no local tables or views?

Re ADP being deprecated, see my next comment..

@Bit,
I am glad to see there are other users sharing the same experience I do, that nothing beats ADP (at least in terms of performance, which is the most important here in our company).

I know that solution with events sounds like something that would be suitable in this case, however since I am not so familiar with classes, perhaps this might be challenging for me to step into..

Just wonder if the reason I mentioned above is not relevant, meaning user claims that no more than one person will be using that screen at a time, would you think its okay for me to change the view's sql every time a user filters something (the way I do in Access MDB's for queries/pass-through queries), or from what I understand from your writing, they may be more reasons why that its not advisable?

Re SQL injection, if this is an in-house program, not accessible outside our network, do you think I have to be concerned about that? and why is that worse of what I am currently doing, changing local MDE's queries SQL property in code?

Thanks,
Ben
BitsqueezerCommented:
Hi Ben,

the new information here is that you want to have only one main form where you want to change anything depending on what should be displayed. I would say, that's the main reason why you create yourself more work than needed.

I would say, you have two choices: Either you want this way, then you would need a lot more programming to make that work, you cannot (or not often) use Access automatisms which make your work easier (like the link fields). Or you create different main forms which are hard linked to their subforms where Access do the work for you, which creates more form objects so in case of changes you would need to go through more objects to change it everywhere - in that case you can more often use Access automatisms and less programming. Each purpose would create it's own view.

"however since I am not so familiar with classes" - I don't think so. Many people who works with Access coding since years think that classes are something magical and they don't know how to work with them. Indeed you are working daily with classes and also with events, you only don't thought that. The reason is, that form's and report's code modules are nothing else than class modules which you instantiate when you say "DoCmd.OpenForm..." (OpenReport). You have a lot of predefined events in these special classes and I would say, it's even more learning to understand how these two types of class module objects work (as they contain a lot of functionality which you cannot influence) as to create your own class modules with your own events. Simply try it, you would be surprised how easy it is and if you have learned the basics about them you have a really powerful tool to work with. You will see that most often you will want to use class modules instead of standard modules as they can do so much work for you.

"user claims that no more than one person will be using that screen at a time" - yes, I hear that also sometimes from my users or managers but a responsible developer would always ignore that. In a database where more than one frontend exists you can never be sure that this is really the case. If you would try to create a frontend which makes that sure you would need to develop a safe method to lock each other user from doing the same at the same time, which always creates additional issues like: What if the user who starts that action goes away without ending the action? All other users are blocked until an admin kills the process. Don't do that, it only creates headaches. In case you even change a view there must be an additional process to reset the view to it's original state and so on, it's really bad practice.
Other reasons why you should never change a view on runtimes are that you are losing the meaning of a view: A view should give you the best performance access to your data by saving not only your SQL code but also an optimized execution plan which can be optimized more using statistics of how it is been used later. Most of these things happens in the background. If you ever change a view's SQL the process starts again, statistics are thrown away and so on. Also SQL Server takes care of how often the view is used, it caches the data you got through the view for many users so whenever the view is called again it don't need to calculate anything again, it simply reloads the data from the cache. That is destroyed whenever you change the SQL code, especially if you change that every time - there is no advantage to use a view anymore, you can simply use dynamic SQL (which in fact also will be cached as long as the exact same SQL code will run again). Views should also make sure that the user cannot access any data he wants, so normally you would protect the tables to not be accessible by the user and only offer views where you also use additional security on them to not allow anything. And something you normally switch off first is the option to view the definition or change the object itself, that's something only a developer should be allowed (and of course all above, owner, admins and so on) - but never be allowed by a user. And allowing the frontend to do that also automatically means that the user is allowed to do that. He would be able to change at least one view at any time and that means he can install SQL Server Management Studio on his computer and change the view to load data from the database he normally would not ever see.

Dynamic SQL: Yes, you can of course do all the dynamic SQL on the frontend by assembling the SQL code here and executing it. But that would not prohibit SQL injection (and as a developer you cannot ever simply trust any user, not if they are your best friends - that can change at any time, maybe some user lost his job and is angry about that and wants to make some damage, you can never be sure. Or he leaves his workplace and don't lock his computer and a visitor sits down and use his account to try to find out some data he shouldn't know, i.e. some salary or product data.)
If you assemble a SQL string like "SELECT A,B,C FROM MyTable WHERE A=" & MyVariable, you open the database for SQL injection. A simple ";" is all what the hacker needs at the input field where you enter MyVariable, then any other SQL command can follow. This is only one simple example, and there are strategies against that, but always keep that in mind when you develop your solution.

To give you a possible solution for your problem it would be helpful if you would show us the exact way how you created your ACCDB solution you meant above with main form and sub forms, the SQL code, the VBA code, maybe a little demo database so that we can see how you want to do it. Then it's easier to show you an appropriate way how to do the same with ADPs.

Cheers,

Christian
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Re your option to use a temp table, I was thinking about that, however wondering if that would not cause a slow in performance, as data would need to be deleted and reloaded for every filter?
I'm not sure - if you create a Stored Proc, and pass in the necessary parameters and let the server create the table(s), you might find very acceptable performance. All depends on (a) the data involved, (b) the muscle of the server and (c) your database structure.
PatHartmanCommented:
If this is a new app,  Stop right now and switch to creating a standard .mdb/.accdb.  We know the ADP has already been deprecated so new development on a dead platform is unwise.

I never moved to the ADP since they don't support anything but SQL Server and I always seem to have other data sources to link to.  I believe you can use a SQL String as the recordsource in addition to a view or table just like regular Access and I thought that was what we were talking about.  Changing the bound recordsource by replacing it with a different string doesn't affect other users of the application since it is done only in the user's FE.

I'm going to jump in with the others - It is NEVER right to change a view on the fly since that would impact any other user of the application.  Find another way.
BitsqueezerCommented:
Hi Pat,

of course you can also use other databases in an ADP, it's only the connection of the ADP itself which is strictly dependent on SQL Server. You have two possibilities: Creating a linked server on SQL Server lets you link any other kind of database down to an Excel file. Or, if you don't need to mix data in one view from different databases, you can always create an ADO recordset and assign that to a form which is fully updatable.

I personally am not dependant on any Microsoft support so there is no need to stop working with ADPs only because MS don't want any professional way of working with SQL Server other than DOTNET. They want Access to be a toy for Office users without development background, that's clear to see in the Access roadmap: A2007 deprecated ADPs, but has them (hidden) and can work with it. Macros were made easier to edit. A2010 deprecated ADPs, but has them (hidden) also and works with up to SQL Server 2008 (but of course also with newer ones, only not with newer features). Macros were again made easier to edit. Webdatabases were introduced where nothing else than Macros can be used and only Sharepoint as server. Good to sell some extra Sharepoint- and Windows Server-Licenses. Bad for developers as Macros are really totally sh**. VBA is not supported. A2013 really made the cut and doesn't support ADPs anymore but has even a better Macro editor. Rumours says that VBA is the next big cut in this process. So you see the development of this product? So why should I stop working with an older version of Access and SQL Server which have all the features I need? Only to always have the newest version or the "support" MS offers? I get updates from older products some years later also, and when THAT ever stops the product and my frontend/backend has already closed all issues. There are people out there which also develop A97 databases - and why not if that fits their needs? We are really not dependent on MS updates and recommendations. I could also develop a database with A2000 and SQL Server 2000 and it would be absolutely stable. So please - don't always say "stop ADPs as MS has deprecated them" - we all know that but it really doesn't interest. ACCDB/MDB were never that good as ADPs are and as ADPs are gone with A2013 they will probably never reach that professional level. JET/ACE in between Access and SQL Server is a bad joke and as bad as if you would insert a VW beetle motor into a Porsche so that the VW motor should drive the Porsche motor. That's not what I understand under "professional developing". Access is going to be a product for Office users more and more and so I'm afraid working with newer versions will cut down the developing possibilities more and more also. Access once had a replication possibility - it's gone, too professional. Access once had a security model for managing rights - instead of further developing and making it more professional, it was thrown away completely, beginning with A2010. So, what was the point in following MS recommendations? Like a few years ago..."DAO is best" - then "ADO, don't use DAO anymore". And in the next version "ah...DAO is better, don't use ADO anymore". I'm still waiting on the day that the more professional ADO will be deprecated and thrown away. I'm afraid we don't have to wait very long.

And yes, you can use a SQL string as RecordSource in a form/report, that's what I already said above. With nearly the same disadvantages as doing it on the server.

Cheers,

Christian
PatHartmanCommented:
ACCDB/MDB were never that good as ADPs are
We're going to have to disagree on that point.

I agree with you on ULS.  It was awful and most people failed to implement it properly but fixing it would have been better from our perspective.  Sadly, MS has never considered professional Access developers to even be a life form let alone important.  They have finally succeeded in relegating Access to "toy" status with web apps.  I just wish there were an alternative for desktop development.  I would switch in a heartbeat.  Not every app needs access to the web.  In fact, most business apps are inward facing and so client/server techniques are more appropriate and provide a better user experience than something running in a browser.  Citrix is the best solution for supporting roaming users of internal apps.  It is transparent and doesn't alter any of the application parts so any Access app can be made available to remote users with no fuss or application change.
bfuchsAuthor Commented:
@Bit,
Or you create different main forms which are hard linked to their subforms where Access do the work for you
It looks like its missing some explanation on my part of what I am trying to accomplish.

First I am attaching a screenshot of the screen in question, so that may give you an idea what I'm talking about.

I am using the top section for placing all filtering options, then I will place in the tab control on the bottom all the subforms that are needed.

just to list a few examples

1) totals of employees by Source
Source
Career Builder  5
Monster             7

2) Totals of Employees by Hire Month
Jan-15   10
Feb-15  20
Mar-15  30

3) Totals of employees by Employee status
Applicant  10
Prospect    11

Now for each of those listed above I would need to design a subform that will have 2 columns in datasheet view, one for the category and the other for the total#.

(I assume if they all have the same amount of columns, its possible to have a technique that handles it by code, that only one form should be used and each instance of form should display different set of data, but that would leave for another post..)

All those subforms should be based on Employees table, and only totaling records that matches the filter criteria selected on top, so if for example users selects hire date range 1/1/15 - 1/31/15 status = Applicant, all totals should be from employees of status applicant hired on Jan 2015.

(Perhaps once I finish the design, will upload the form so you can modify that like we'd done it successful in the past).

@Scott,
1-If I would have to upload each time the data, can this have any affect on the database size or impact users in any way?
2-That approach also limits to only one user at a time, correct?

@Pat,
The way you've described would involve I guess more programming as each subform needs to be separately changed their record source sql property after users applies a filter.

@Bit, Pat
I am more than happy to have you elaborate all the pro's and con's of using ADP, as I have only to gain from your level of expertise in this field, please feel free to continue and I guess many people would benefit from it..

Thanks,
Ben
Untitled.png
PatHartmanCommented:
There is no advantage to reusing objects in this manner.  Create separate subforms or separate search forms.
BitsqueezerCommented:
Hi Ben,

thanks for the further explanations, now it's more clear what you want to achieve.

Apart from that the layout of the fields is really awful...:-)

If your result is always "Category" and "Number of Employees" then I would create a SP for that purpose so you can use one form (you would even not need a subform as the filter fields in the header can be unbound fields) to display the result.

That could be something like this (which you must of course change to fit your needs):

CREATE PROCEDURE dbo.procStatistics
(
	@intCategory	AS int,
	@strFilter		AS nvarchar(4000)
)
AS
BEGIN
	DECLARE @strSQL AS nvarchar(MAX) = '';

	IF 		@intCategory = 1	-- Source
		SET @strSQL = ' SELECT [Source] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employees
						WHERE ' + @strFilter +
					  ' GROUP BY [Source]';
	ELSE IF @intCategory = 2	-- Hire Month
		SET @strSQL = ' SELECT [Hire Month] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employees
						WHERE ' + @strFilter +
					  ' GROUP BY [Hire Month]';
	ELSE IF @intCategory = 3	--Employee status
		SET @strSQL = ' SELECT [Employee status] AS Category, COUNT(*) AS [Total #]
						FROM dbo.Employees
						WHERE ' + @strFilter +
					  ' GROUP BY [Employee status]';
						
	EXECUTE sp_executesql @strSQL;				
END

Open in new window


Now your frontend only needs to assemble a valid WHERE-string from the filter fields and use that to fill @strFilter, then execute the SP and assign the returned recordset to the form's Recordset property. As this returns grouped queries it is of course not updatable so you can use SQLNCLI as driver to execute the SP.

Of course this procedure is not safe against SQL injection so you need to make sure that @strFilter doesn't contain any additional SQL commands. That can be done by searching for ";" in the string if that doesn't appear in your data, and also by searching for any SQL command text like SELECT, INSERT, UPDATE and so on which normally is also not contained in your data. It's worth the time to search for that in the Internet on how to avoid SQL injection, so that you can use one standard procedure to check that for each of such filter procedures which you can call at the beginning of the procedure here and then stop the execution if there's anything wrong.

Cheers,

Christian

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
@Bit,

I would definitely like to work with you in completing this project, however I feel its not fair to encapsulate all this into one post, especially that this topic of reusing the same form is not really part of the original question, therefore I am first awarding all participants in this thread and opening a new one focusing more on this topic and perhaps some gaps I'm still having in applying your latest solution to my project.

Thanks again,
Ben
bfuchsAuthor Commented:
Hi Bit,
Actually that works wonderful, see attached.
Great job!
Untitled.png
bfuchsAuthor Commented:
@All Experts,
When you have a chance please take a look at the following, thanks.
http://www.experts-exchange.com/Database/MS_Access/Q_28665967.html
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.