Free Lessons
Fast Tips
Topic Index
Home   Courses   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > Forums > Access
Access Forum

Back to Access Forum

Dataverse Upload Images   Link  
Richard Rost 
7 months ago
A lot of you have asked me when I'll be doing lessons on Dataverse. It's going to be a while. I don't like jumping on new products / features as soon as they're released. I prefer to let someone ELSE figure out what all of the bugs / problems are. That's one of the reasons I'm so late in getting a Windows 11 class put together. It's relatively new.

Here's a perfect example of why I'm waiting on Dataverse. It's just not ready: Access - My First Venture Into Dataverse
Kevin Yip
7 months ago
I recently trialed Power Apps, and it was relatively painless to link my online SQL Server tables to Dataverse (see picture below).  Everything was done on the Power Apps web portal and the process was smooth, from all the credential checking to actually seeing my tables on the screen.  Maybe linking to Access tables (as mentioned in the Devhut blog) is different and more cumbersome.  I couldn't try linking to Access because my trial Power Apps account didn't have licenses for the Office apps.

Regarding annoying glitches and hiccups, yes I saw them too, but mostly browser-based, since everything was done online.

Frankly I wouldn't involve Access in a cloud-centric environment like the Power Apps.  I would upload all my Access tables online to something like SQL Server before thinking about Dataverse and Power Platform.  If I want a private Windows app, I use Access.

Richard, I see your site as mainly about local desktop usages -- for people who probably want their data private instead of being shared online.  If your students want the latter, they should understand it's a completely different beast.  Developing a cloud/web database app is just too different from developing a local app based on Access.  You have often to develop two separate apps for that, like I have done.
Kevin Yip
7 months ago

Jon Capps
7 months ago
I have move almost all my tables to Dataverse, its working, very slowly. I still need to make pass through queries still, however reading and writing to Dataverse tables in that environment is working for my 20 tables to far, with three users.

Again its really slow, I have about 25K in records, in just one table and about 5K in about 5 other ones, and much smaller tables.

We have a E3 license and using the premium version of Dataverse. I am almost out of my depth in my skills, however I am trying to push forward. The reason is the virtual cloud is way too $ and our process for using, excel, to pull data from Access, word, outlook, and 15 web sites we must use daily is a huge resource hog with the virtual enviroment. We have three pc's connecting to the virtual enviroment we are experiencing more and more crashes daily. I miss my local server; however we constantly have to worry about a RAID failing or updating the server every few years. I may end up trying to push though the SQL server route, however for now Dataverse is where my tables are, for now.
Kevin Yip
7 months ago
Hi Richard, I've just built a simple Power Apps for a movie collection which you can see in this video:

Contrary to what Devhut says, using the Power platform is not the most terrible thing in the world.  The biggest downside that I've seen have been server lags at Microsoft's end (you can see it in my video at about 1:00).  Other than that, I had no trouble importing and linking data and creating an app with all the features I wanted (albeit a simple app).  

My app consists of two "continuous form" type of controls that Microsoft calls "galleries."  One is a list of movies, linked from a Winhost SQL Server table that has 13k records.  The second shows cast and crew of the selected movie.  This table has 100k records total, and Power Apps does a good job filtering out only the cast and crew for the selected movie.  Overall, I'm impressed with the performance (server lags notwithstanding).  I didn't have to code differently for having efficient online data retrievals.  All the functions and logic in Power Apps are apparently designed and optimized for online data.  I can simple tell Power Apps to grab 13k rows worth of data and it will take care of the performance side for me.  

All the controls have "data sources" and "control sources" just like the Access counterparts, albeit with totally different nomenclature.  For instance, the control source of a text box is called "Text", confusingly.  The record source of a continuous form (called "gallery") is called "Items". Expressions can be entered in the property sheets of the controls, just like in Access.  But you can enter several expressions in one box, separated by semi-colons, essentially creating a mini-procedure.  There are functions that serve as queries, such as the Filter(table, criteria) function, which returns a record source, just like an SQL would do.  There are no SQL statements used in Power Apps as far as I've seen.

I also made a text box that lets you search-as-you-type, and it filters the movie list to show only titles with the search word.  And the code for that is simply:

     If(Len(searchtitle.Text)=0, MYCOLLECTION, Filter(MYCOLLECTION, searchtitle.Text in Title))
The If() function above can return an entire table or data source.  To find a substring inside a title, you simply use the "in" keyboard above.  Functions in Power Apps feel very streamlined and slimmed-down compared to the VBA counterparts.  Not surprising, since this is made for people who don't (want to) do a lot of coding.

Richard, I really think Devhut is close to misrepresenting this product.  I'm sure the Power platform still has tech issues, but if he can't even do a simple thing as importing a table (which I have zero problem with), maybe he isn't qualified to give an opinion just yet.  And he is being shrill and over-the-top in his criticisms of Microsoft, which sounds very unprofessional, not to mention unhelpful to anyone looking for useful info about the Power platform.
Richard Rost
7 months ago
That's very interesting. I definitely need to spend more time with Power Apps and that's one of the reasons why I haven't chimed in and given my two cents on it or made any videos about it because I really haven't spent any significant amount of time with it. And yes I do think he is sometimes very critical of Microsoft.
Kevin Yip
7 months ago
Having said all of that, I will not pay for the Power platform once my trial period ends on June 10th because the Power platform is really made for a group of users, not individual users (whom Access is made for).  But if I still had my old job, I would strongly consider getting this because it suited my particular working environment: 3-5 users who didn't always use desktop PCs and had to travel and/or be at remote locations.  Since you pay by the user, this is actually ideal for really small businesses with 3-5 employees.  The streamlined, slimmed-down coding is also a plus from what I've seen.  But the user still needs to know certain advanced coding concepts, such as variables, scope, data types (numeric, text, boolean, etc.), the object-oriented structure, etc.


Just want to mention another example of cloud-friendly coding in Power Apps.  In my app, one of the form fields uses a Lookup function as a control source.  This is akin to a continuous form in Access that has a field that uses DLookup().  This is usually frowned upon in Access because it may degrade performance, especially if the form has lots of records and the DLookup domain also has lots of records.  Yet my app displays the result almost instantly with no slow-down whatsoever, even though the form has hundreds of rows, the field's Lookup function actually looks up a 700k-row table on every row in the form.
Kevin Yip
7 months ago
I've also learned that you cannot design public websites with the Power platform.  Even though it's an online platform, online doesn't necessarily mean public.  All users need licenses in order to use Power Apps, Dataverse, Power Automate, etc.  So the Power platform is very much a "private" application.  To make public websites or applications, you need something like Visual Studios.
Kevin Yip
7 months ago
Local variables in Power Apps are called "context variables", and their scope is the screen they are used in.  Global variables have the scope of the entire app.  Properly naming the variables is a must, because code can appear in many places.  You can globally search and replace variable names, and get a nice result of where each variable is located: which control, which property, etc. (see picture below).  If you name all the context variables with "c_", then you can find all of them by just searching "c_".
Kevin Yip
7 months ago

Richard Rost
7 months ago
Thank you for sharing all of that, Kevin. Personally, I like the combination of Access and SQL Server. If you need an on-prem database, start with Access. If you need more security or lots of users, add SQL Server as a backend. If you need to get online, use SQL Server online. I get why MS is pushing Power Platform, but I still like SQL Server.
Kevin Yip
7 months ago
I finally found a real problem, which involves aggregate functions like Sum() and Count() (called CountRows in Power Apps).  In Power Apps settings (first picture below), it says that Power Apps can only count up to 2000 rows on SQL Server tables when "delegation is not supported."  When Power Apps "delegates" a task to SQL Server, it essentially means it "tells" SQL Server what to do.  Some tasks are "delegatable", while some are "non-delegatable."  And sadly, a simple task such as counting how many rows an SQL Server table has is not delegatable.  It can still be done, but Power Apps can only count up to 2000 rows.

To get around that, you need some silly workaround: create a column of 1s in an SQL Server table, the sum that column to get a row count, which surprisingly *is delegatable* for Power Apps.  

You have to create a "view" for an SQL Server table (a "view" is SQL Server's version of a query).  This view will have a dummy column of 1s, which will look like:

ID   FirstName   LastName   DummyNum
1    Kevin       Y          1
2    Richard     R          1
3    Kevin       R          1
4    Jane        D          1
5    Juan        E          1
6    John        F          1

Then in Power Apps, you will be able to get a row count by using the Sum() function.  In Power Apps syntax, it is Sum(MyView, DummyNum).  This was how I was able to get a row count of 15K, well over the 2000 limit, in my second picture below.  That's all because Sum() happens to be a "delegatable" operation in this instance.

But in another instance, as shown in my second picture, I hit the 2000 limit anyway, because in that instance, Sum() is non-delegatable.  It is non-delegatable because Sum() does some extra processing of its own.  The syntax I use is Sum(Filter(MyView, ID=something), DummyNum).  The extra use of the Filter() function happens to be non-delegatable (i.e. cannot be passed through) to SQL Server.  Hence, the operation can only return 2000, and no more.

So this is some unique hassle of the Power platform.  I suppose the 2000-row limit is to help with performance.  This creates a real problem of not being able to get an accurate aggregate value in some instances.  I've searched for online info for this, and the workarounds for this create even more hassle.

Kevin Yip
7 months ago

Kevin Yip
7 months ago

Richard Rost
7 months ago
That's crazy. That right there would drive me crazy. Thanks for sharing.
Kevin Yip
7 months ago
I finally have some success in dealing the aforementioned "delegation" problem in a pretty hassle-free fashion.  New users of Power Apps should take major note of this, because you don't want your row count limited to 2000, for obvious reasons -- an app that can't even count correctly is just no good.

This Microsoft documentation has this important info on delegation:

Delegation affects all types of data storages: Dataverse, Sharepoint, SQL Server on web host.  They all have functions that are delegable and non-delegable.  

Here is the info on delegation for SQL Server:

Note that Sum() is on the list and is delegable.  But the If() function (similar to IIf() in VBA) is not even on the list, thus non-delegable.  Therefore, if I use this expression to sum a column conditionally:

    Sum(If(A = B, Table1, Table2), Column1)

I will only get 2000 rows at the max.  That's because the sum operation contains an If(), which can't be delegated.

However, if I rewrite the expression just slightly:

    If(A = B, Sum(Table1, Column1), Sum(Table2, Column1))

then both Sum functions don't have any non-delegable elements inside, like the If() in the previous example.  As a result, this expression will get me a TRUE, correct row count!

It took me a while to even understand the concept of it because it's such a foreign concept coming from Access.  As I said, this seems an important topic to know about for Access people like us.  

Richard Rost
6 months ago
Kevin Yip
6 months ago
There are two other mind-blowing difference I've seen so far.  One is input masks.  It's a trivial thing in Access to set up an input mask for a textbox.  But there is no such built-in feature in Power Apps.  To create an input mask for, say, a phone number textbox "(000) 000-0000", you need to write some heart-stoppingly complicated code in Power Apps.

The other notable difference is that in Power Apps, all the form controls only have at most *two events*: OnSelect and OnChange.  There are no OnEnter, OnLostFocus, OnClose, OnDblClick, OnGotFocus, etc. none of those.  I don't know if this would cause major problems, because I've only made two simple apps.  I've already seen some minor problems, such as when the user exits a textbox without changing the textbox's content -- in which case you can't do a thing, because there is no OnLostFocus for textboxes.

Other than that, there are just differences in coding syntax and various nomenclature.  For instance, a continuous form is called a "gallery" in Power Apps.  I made a simple customer form app in Access and in Power Apps (picture below).  

Power Apps advertises itself as a "low-code, no-code" tool, which is not necessarily true from my experience so far.  The coding syntax may be more streamlined, but the thought, logic, and design the user has to put into the code are still largely the same.  For instance, to create a data source for a form, you still have to link to the table, choose the fields, sort the fields, filter the records, etc., just like you have to do in Access.  So that's why I think Access knowledge is still important for your students to have, even if they consider Power Apps in the future.

Power Apps' primarily attraction is the *cost*, I feel.  If you have a low number of users in your company, say 5, you only pay $25 per month per app.  (An app can have multiple screens, so you can essentially have multiple apps within an app.).  SQL Server is free.  Winhost for SQL Server costs $5 per month.  Internet domain fee is about $20 a year, or $2 a month.  So your total operating cost for running a Power Apps app is about $25 + 5 + 2 = $32/month, or $384/year.  And you have the added bonus of being able to use your app on all platforms: PC browser, PC desktop app (free at Microsoft Store), Mac browser, iOS browser, iOS app, Android browser, Android app.

Here is Power Apps' pricing:

The $5-per-month plan includes Dataverse storage space of 50MB, which is woefully inadequate for a database.  So you really need to pay the extra $5/month for the Winhost plan to get the 500MB database storage space (which is also not that much space, but enough for most people).

In comparison, in an Access-only environment, to have Internet connectivity, Access Database Cloud will charge you $248 per month for 5 users = $2976 per year.  And this is largely a virtual desktop kind of thing, not exactly an Internet-connected service.  And you still have to pay $100/year to get Access from Office 365.  

That is over $2000 more expensive than switching to Power Apps.  Your students should spend that $2k *on your courses* instead, Richard, because...

As I mentioned, to design apps in Power Apps, the user still needs considerable database skills, the kind you get by learning Access and SQL Server.  So Richard, I wouldn't mind if you covered Power Apps on your site, because I think Access knowledge is still required for Power Apps -- "required" in the sense that it would make designing Power Apps apps much more easily and smoothly if you already had Access knowledge.  So if you included Power Apps on your site, it might add to the worth of your Access courses as well.  All this is just my opinion of course, as I understand you need to like Power Apps yourself before endorsing it and teaching it.
Kevin Yip
6 months ago

Kevin Yip
6 months ago
Here is a glimpse of the differences between coding in Access and in Power Apps.  In my app above, I use string concatenation to construct the full address shown on the continuous form, with each part of the address separated by a comma.  And I want to ignore empty fields, so it won't show extra commas.   E.g. If the second line of the street address is blank,

instead of showing:     100 Main St, , New York, NY
I need to show only:   100 Main St, New York, NY

In Access, I write a custom function called ContenenateAddress() as shown below, and use it as the control source of the text box.

In Power Apps, I go to the "Text" property of the textbox (the equivalent of its control source), and write the function as shown below, using the built-in functions Concat() and Filter().

"ThisItem" is an object representing the currently selected record.  If I write "ThisItem.CustomerName", it is similar to writing "Forms.MainForm.SubForm.Form.CustomerName" in Access.  This is another example of the streamlining of coding in Power Apps compared to VBA.

The Filter() function does not have an equivalent in VBA.  But if you are well-versed in VBA, you should understand what it does.  It applies a filter on a "collection" of items, which can be table of records or an array of values, with a specified criteria.

So if I write:   Filter(MyTable, "Fashion" in CustomerName)

it will return all the records in the table whose CustomerName field contains the substring "Fashion".

If I write:  Filter([field1, field2, field3, ...], condition)   as I do in the picture above

it will return only the items in the array (indicated by the square brackets [ ]) whose condition is true.

And finally, the Concat() function concatenate the items returned by Filter(), with commas as delimiters.

As you see, the amount of code is similar in both cases -- you do basically the same amount of typing, maybe a little bit more in Access.
Kevin Yip
6 months ago

Richard Rost
6 months ago
Very insightful, Kevin. Thank you. It just seems to me like they need to "mature" PowerApps a little more before I start diving into it. It still very much feels like "1.0 software" to me.
Kevin Yip
6 months ago
It's definitely a different way of life compared to Access.  For instance, Power Apps has no built-in report design capabilities.  The user has to rely on external services such as SQL Server Reporting Services (free).  You use a web interface to run reports, export to PDF, etc. (see pictures below).  So any user with a web browser can run reports anywhere.  But to design reports, you use a desktop-only app called Microsoft Report Builder (also free).  So only desktop users can can create reports, which may be good for security reasons.  It's just a totally different way of life compared to Access.
Kevin Yip
6 months ago

Kevin Yip
6 months ago
Here are my final findings of Power Apps.  My trial period has ended.  The video below shows what a typical CRUD form app can do, and how it is designed in Power Apps.  My conclusion in a nutshell is that (a) it's best for those who need online connectivity and multi-platform front-end access, and (b) it has both better and worse features compared to Access.
Richard Rost
6 months ago
Looks pretty good... but yeah, it's like learning a whole new ecosystem.
Kevin Yip
6 months ago
It turns out my trial has NOT ended.  Microsoft sent me an email (see below picture) saying I have another 30 days of access.  After that, I'll have another 90 days of access -- really?  My account had no previous license whatsoever.  So this 30-day trial is actually a FIVE-MONTH trial, lol.  To those who consider trialing, take note.
Kevin Yip
6 months ago

Richard Rost
6 months ago
Interesting. LOL

This thread is now closed. If you wish to comment, start a NEW discussion, below.

Back to Access Forum Comments

Start a NEW Conversation
Only students may post on this page. Click here for more information on how you can set up an account. If you are a student, please Log On first. Non-students may only post in the Visitor Forum.
Subscribe to Access Forum
Get notifications when this page is updated

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2023 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 12/6/2023 12:39:35 AM.