Microsoft Access Migration to the Power Platform with Nathan Helgren

[mark]: 1:48
today's guest is from redmond in the united states he works at microsoft as a senior program manager in the show notes will have links to his bio and and linked in an things like that you want to find out more information but with that welcome to the show Nathan

[nathan_helgren]: 2:05
thanks mark i appreciate you having me

[mark]: 2:08
it's good to have you on the

[nathan_helgren]: 2:09
okay

[mark]: 2:09
show always like to get to know the guests from a what do they do when they're not working perspective and so with that in mind what you know tell me about family friends fun what do you do when you're not working for micro soft

[nathan_helgren]: 2:27
got it well i've got twin eleven year old boys so

[mark]: 2:30
wow

[nathan_helgren]: 2:30
they keep me pretty busy my house is a lot of activity so it's mostly you know just trying to keep up with them were

[mark]: 2:38
okay

[nathan_helgren]: 2:38
really do you know board games and playing outside video games

[mark]: 2:42
nice

[nathan_helgren]: 2:42
music that kind of stuff i like to cook a lot we do some family book clubs lots of reading so yeah that kind

[mark]: 2:49
very cool

[nathan_helgren]: 2:49
of good stuff

[mark]: 2:51
very cool very cool and have you always been in seattle

[nathan_helgren]: 2:56
mostly for my professional career i was in salt lake city before that grew up there and my first few years were at a p c manufacturer that's no longer

[mark]: 3:06
wow

[nathan_helgren]: 3:06
around in the u s and then moved over to microsoft about twenty three odd years ago

[mark]: 3:12
wow

[nathan_helgren]: 3:12
and i've been there ever since

[mark]: 3:14
wow you've seen some changes

[nathan_helgren]: 3:16
yes yes i have came in right around windows x p and ye so i've seen through some stuff

[mark]: 3:24
so so was steve was steve the c o then or did he come as were you there prehimbenco

[nathan_helgren]: 3:31
right it was it was steve yeah so steve was then and as a matter of fact there were a few launch parties around windows x p and office x p that i saw both bill and steve there like you know right up

[mark]: 3:43
well

[nathan_helgren]: 3:43
front congratulating folks and signing copies of stuff like that so is pretty cool you know it was it was still a huge company by that point but it's still you know it was years ago and back when the originators were the kind of around

[mark]: 3:57
that's very cool that's very cool so how did you get involved with microsophbazeps

[nathan_helgren]: 4:03
well really it was a series of kind of moves within the company you know there's a lot of times when you'll be working on a product and that product will need people to work on it quite as much any more there's kind of reorganizations and i kind of ended up in busapsas part of a reorganization but i'm really happy i did because it's it's a really cool area there's a lot going on a lot of interesting technological problems solve a lot of great opportunities and so you know i've been here for about four years in the bus app space all of it within the power platform and data verse and you know i think it's a event astic place to be there's there's a lot of great great velocity going on right now with with the different technologies and the different things we're trying to do it

[mark]: 4:47
so true you know the show we wanted to discuss access microsopht access and i find it's funny because you know most people at some time and in it for any time you would have done something in access you build access data bases and things like that what surprises me is the number of customers still use access as a mission critical application right and it's kind of like low code one right a subject matter expert in the business said hey i can work out the relationship these tables and put them in i can create those early and and for some people that was the introduction to data bases and so it's been around a while what has motivated microsopht and particularly inside the data verse team to go you know what we need a migration path from a something that has you know it might never intended originally to become a mission critical application but through time it ended up there right and so now of course there's massive risk with an access data base in that one there's the internal security of risk and in other words the ability to have data leakage from withinside an organization it was never designed in the cloud centric world that we live in now and and then there's the version you know is the version that i'm using the current version has that been maintained who's doing it is it under the control of it or is it still sittin and shadow and of course a lot of what we get in the power platform addresses that what was microsoph's motivation lens et cetera to decide you know what we need an easy migration path from access to data verse

[nathan_helgren]: 6:31
you know that was a really interesting story because it really wasn't data verse that approach access actually came to us so

[mark]: 6:38
wow

[nathan_helgren]: 6:39
access had been hearing from its customers for a while now that they had needs some of them were saying we want to take our data mobile and there was an attempt to kind of go cloud based for a while there on access a few versions back and things just didn't work out the way they had hoped and then there are a lot of pushes for people to take their data also into a more secure space especially nowadays you know there's a lot of talk about compliance and governance and so because of kind of those limitations of access you know where you do have sort of an open model security that you able to lock down as well as you'd like to there was a lot of requests of you know how can we get more secure how can i access this mobile how can i put this in the cloud where other people can get to it and they came to us over in the power platform set over the data or specifically and said hey is this something you guys would be interested in doing um and really we were yeah when when i first heard about it i thought that's really interesting i like you i was not aware how how widely access was still used but it's actually still a very um you know a very strongly used application um and as as you were sayin you now kind of started out earlier as like a like an entry level data basely wasn't quite like sequel or something really powerful but a lot companies and businesses and enterprise level businesses run their business on access at least maybe not all of the components of it but access is very commonly used up cation and there's a lot of strong development that goes on with that as well they've got a big development you audience and some nvps that are very passionate and they've got a lot of this good great capabilities of things that they put out but they're still unable to kind of meet a few of those goals because of the the structure and the architecture of access so we kind of put our heads together and and came up with the best of two worlds thought of different ways to make sure that you could get that data into data verse so we could use that a d level security we can have roll based security

[mark]: 8:39
nice

[nathan_helgren]: 8:39
really kind of provide that that tighter governance as people want to have but also not keep people from utilizing the functionality they're used to one of the key rules that we set for ourselves where that access forms needed to continue to work so people who move their data out of access into data verse they can still use their forms they can still use their queries so really to them it could happen in the background from an i t manager and nothing would change for their data work everything would work as it always has and the reason for that is there's a lot of investment in people's access clients us and also it takes time to learn how to build an ab maybe you want to figure out how the best way to handle this do i want canvas or model do i need to have automation that you know the access functionality continues to operate without any business interruption while the data there and then at the same time you can be utilizing that time to kind of ramp up and build your other infrastructure on the power platform and continue on and expand your business into different directions while that access front end keeps doing its job

[mark]: 9:42
okay so you bought something up there that i didn't know about how this works so you're saying ah an individual sorry an organization having a data base an access they could migrate two data verse and they could carry on and build apse whether data model or sorry whether model riven or canvas faced ultimately get all the benefits of power automat power virtual agents and power pages as part of that or and this is just correct me if i've heard you rightly here you could migrate your data into data verse but you could still use access your front end to that data sitting in data verse is that what you're

[nathan_helgren]: 10:23
absolutely

[mark]: 10:23
saying

[nathan_helgren]: 10:24
absolutely

[mark]: 10:24
wow i did not know that

[nathan_helgren]: 10:26
and yeah that was that was again like it was really key to us because whenever you have a big shift in technology you know there's always that disconnect period of people to learn and catch up and if we were to take that and just put that into data versa until everybody okay now just convert all your apse and you're ready to go you know it's not going to have the kind of crawl walk run pat and we sometimes need to have in technology we need people to be able to get their data into a secure place and meet those requirements you know maybe they're at a bank or maybe there are some other institution that has some financial or maybe even some governmental secure data that they want to have in some place that's a little bit tighter access restricted area and so they put that in there but at that time they still need time to get their apps out so they move their data over to meet that compliance regulation and they could keep running i mean they could honestly they could run on the access client the entire time and not use any power as if they decided

[mark]: 11:21
wow

[nathan_helgren]: 11:22
to it's totally possit but it does open up that wider variety to them so maybe they have a back office that continues to use their access client apse but then they have a bunch of mobile appsadthere people out in the field used to go do some field work and report and er check induced do you know they scan in at different locations and take pictures of things and type in details of their their investigations and then have that go back in and somebody to back off can actively use that data and start doing other processing with it so it really does kind of open up a wider world of usage for access as well so even if they wanted to stay with the access platform they can get a lot of benefit from from using this

[mark]: 12:02
yeah okay that's awesome tell tell me some questions pop into my mind is there a license cost for this connector or is it part of the connecter suite

[nathan_helgren]: 12:11
so this connector is actually an access side connector and so

[mark]: 12:15
wow

[nathan_helgren]: 12:15
there is so it was built on the access side to connect data verse and it uses our open data apis so because of that there's no extra cost for the connector so if you have a standard data ers license for example even data verse for teams for example you can utilize that and the connector that was built on cass will be able to connect to the systems and migrate the data over now once that data is in data verse you do have to have your data is licensing that's necessary

[mark]: 12:43
of course

[nathan_helgren]: 12:43
if you're using the full version you know you'll have to have licenses you're users but if you move into microsoffh data or teams data verse for teams then you're able to utilize the license you already have there and use the data in that way and migrate it over and connect to it

[mark]: 12:58
yes i didn't even think of that use case right lighting it all up in teams which is you know would be a massive in for a lot of organizations okay so that's that's interesting is this all g a now or is it dog what's the status of it

[nathan_helgren]: 13:12
we went to g a just back at build here in may so we are live now world wide and really you know the date of her side on the power platform an data verse there's really no change the code is already there it's it's part of native data verse so really all you have to do is just make sure that if you're using access you have the most recent version of microsothree sixty five it's right now in the microsoh three sixty five version in the monthly builds or it will also be in the quarterly when the quarterly build comes out but it's not in the package product yet the package products can be a little ways out just because of the timing for how push out those bits but if you've got the monthly the monthly drops as part of the sixty five you can use it right this moment

[mark]: 13:56
wow that's awesome so have you got any without sharing names have you got any use cases of like particular industries organizations et cetera that are already are going you know this some problems for

[nathan_helgren]: 14:09
we've had some trying to think of best way to phrase on these we've had some government

[mark]: 14:15
so just by

[nathan_helgren]: 14:16
agencies

[mark]: 14:16
industry yeah yeah

[nathan_helgren]: 14:17
yeah

[mark]: 14:17
so

[nathan_helgren]: 14:17
we have some we've

[mark]: 14:18
number

[nathan_helgren]: 14:18
had some government

[mark]: 14:18
okay

[nathan_helgren]: 14:19
use we've had some you know hotel and you know restaurant we've also had some oil and gas looking at us

[mark]: 14:28
right

[nathan_helgren]: 14:28
and and then also small businesses small to medium businesses that you want to utilize the functionality some some beverage companies as well so we've we've kind of run the gammit from small industry all the way up through government and for enterprise as well of people who've started utilizing this we had some early testers in our views from many different agencies and groups that you gave us a lot of great feedback and help kind of mold the direction we were going and the mvps were really kind of key there as well gave us a lot of fantastic feedback to make sure that we were ready go but yeah over all we kind of see just a wide range of different folks who find the applications really attractive we find that i it actually particularly the group that's most excited about this either either

[mark]: 15:16
right

[nathan_helgren]: 15:16
it's somebody who is who is really into the power platform and writing appso or it's the i t team that says i can click a few buttons and suddenly it's secure that's fantastic let's do that

[mark]: 15:28
yeah yeah i mean it's like one of the one of the original shadow it applications right access and x l

[nathan_helgren]: 15:35
yes

[mark]: 15:35
you know those types of things that it didn't know about so i can imagine it definite wanting to wrap their arms around this and and secure and you get their data into compliance if people want to go and find out more you know i got show notes and i'd love to put any kind of links et cetera is there any kind of key locations markoff docks what what else to get up to speed and using this

[nathan_helgren]: 16:01
you know i'll put some links for you to use in the show notes on the out on microsophdock so we have some docks out on the access side which are really kind of explaining all the preparation you should do on the access side a little there's a few things to be aware of some data type concerns that you have to be aware of things of that nature and that we have the documents on the date of her side and i've also created a number of videos that kind of show the actual

[mark]: 16:25
fantastic

[nathan_helgren]: 16:26
migration process so people can watch those and they're they're like under five minutes and you can kind of watch the process as it goes through get an idea about what you'll need to do and become familiar with it you know utilize that as kind of your kicking off point and then you know we've got a lot of other great resources out there with the various documentation and blog posts and other things and as well as other users who've done some of the migration i have kind of you now made some post about it saying o hey i've done this and this works great or this is something i'd like to see next time and this is a feature that we are continuing to work on this is the version making it possible

[mark]: 17:02
great

[nathan_helgren]: 17:02
to migrate we definitely have some improvements we want to be making as we continue forward so it is a it is an ongoing type of release

[mark]: 17:12
fantastic fantastic so it's not just going to be dropped as a v one and and and that's it that's you're migrating at four days and continue to iterate

[nathan_helgren]: 17:20
exactly mainly because we do have some as i said some data type restrictions so something is really interesting and don't know do we have time to talk about that

[mark]: 17:29
yeah yeah totally totally

[nathan_helgren]: 17:30
yeah um something that just is kind of interesting is access has you know because it is a fully formed you know data data base and a whole relational functionality that it has some data types that have been there for it a while and some of them have different data limits than data verse does for example one thing i found super interesting is access allows you to have up to two gagabites worth of data in a large text eld but the total

[mark]: 17:56
well

[nathan_helgren]: 17:56
size of an access data base is to gigabites so

[mark]: 17:59
wow

[nathan_helgren]: 18:00
the the likelihood of

[mark]: 18:01
what

[nathan_helgren]: 18:01
somebody putting all of their data and filling up the entire data based with a single field is unlikely so

[mark]: 18:07
yeah

[nathan_helgren]: 18:07
the kind of the tricky part we had was okay well we know they're not using two bites our current maximum is one macabite so the truth is somewhere in the middle and so what we're doing right now is we have a bunch of i tore that it's running in a background and so as people are doing these migrations every time it catches record says this record is too big we can't move it we find out how big was that record how often does this happen and then maybe after we look at this data it tells us we need to bump up to maybe ten megbites or a hundred megabites to meet the needs of access but it's not too gigabites and so we're kind of keeping an eye on that so that's kind of where some of those improvements are when we have some overages for things like that or our decimal range is smaller than what we have in access as well and so again if we hit if people are hitting that limit our limits it's not paul it's over it's positive negative one hundred billion with ten decimal places so it's a big number but we are kind of tracking to see if it goes larger than that and again so we can kind of expand and deal with those all those limitations i talked about our on the online documentation by the way

[mark]: 19:09
nice

[nathan_helgren]: 19:09
and so people can take that into account as they're looking to migrate but so yeah so because of that we do have some growth to do to look at you know are there spaces where we can increase certain values to better met accesses needs and so that's kind of like as we're looking at this in on going way that's one of the things we're taking into account

[mark]: 19:27
was there any field types that just didn't have a logical mapping

[nathan_helgren]: 19:31
yes

[mark]: 19:32
yes

[nathan_helgren]: 19:32
right now ole does not have a logical mapping because ole is used for a lot of different things in access and one thing it's used

[mark]: 19:40
yeah

[nathan_helgren]: 19:40
for is v b script which we on't support so that's one thing that data verse doesn't have is v b a and that

[mark]: 19:46
yeah

[nathan_helgren]: 19:46
so it's that's kind of a it's a ding in the minds of many of the people who use access who are access developers because they're used to using v b a for doing a lot of things you can do all those same things with other scripting methods in data verse but it's just not a it's not like a move over and plug it in that works so you now lay itself because it holds pictures and it holds scripts and it holds jason at holds other things we still have some time to figure out what we can do with that because there are some gaps that we just don't cover on our side as effectively um also another space is right now we currently are not using float values because to be frank the data verse float values have some work they are insufficient for most float types and they're very very under us and we are in the process of trying to improve that and so once those are improved will allow float types over as well so there's a few types that are still kind of held back and all those are you know fully disclosed on the dock mutation so people can make the right choice for them if you try to move over data that's not supported the migration tool catch that and it will say hey we don't support these columns or we don't support these rows because they're too low and then users can make a choice they can choose that they want to just cancel out and not migrate over which is totally fine or it will allow you to continue and move everything that can move over into data verse and then the rest of it will stay in access and so at that point say if you have pressure to meet your compliance and regulatory needs for our data storage you can move over all of your knowyou're decimal currency and all this other information and store that there and maybe some of your other fields just aren't moved over and you move over as much as you can into data verse and use it that way so that's another option we provided for people because we knew we coul hit everything a hundred per cent on the first try so we we wanted to make sure we got we got above eighty five percent of the data types covered and sizes and so we wanted to make that that was something that we could move forward with and give people a choice and also give them a pathway forward to work on this and then you know maybe move stuff over later after we've got the ability to handle these other types

[mark]: 21:54
yeah makes sense what was the name of that field type that you said that holds the v b a script so so i wonder if there's an opportunity for somebody in the community to create a tool that does a translation to that defence or other scripting that will allow that to run or is that something that you marstoffs thinking a building so it could ultimately get across just away from v b

[nathan_helgren]: 22:20
you know honestly we don't have a

[mark]: 22:22
no

[nathan_helgren]: 22:22
direct road map item to handle the olascripting at the moment so if somebody from the community came up with something like that that would be pretty cool you know i would be super impress and i'm sure a lot of people will be more than happy to use that so yeah i mean that old be a great opportunity for the community to kind of just jump in and solve a really kind of tricky sticky problem that we have um like another thing that we're gon that we're looking to you right now that we don't have full conversion for is calculated fields but as you may be aware we have just private pre viewed the power f x v it's for formulas and so we're

[mark]: 22:57
yes

[nathan_helgren]: 22:57
thinking that we might be able to actually use power effects and do a lot of that translation and be able to with minor code changes maybe with a script or something move everything or from calculated fields to formula field so that's like another area where we're kind of investigating and kind of testing and figuring stuff out and if the if somebody in the community starts playing with it and says hey this is a great idea i've got to i've got solution for that you would be twould be super cool

[mark]: 23:23
i like it i just see that the x m tool box might need another app added to its arsenal

[nathan_helgren]: 23:29
definitely we love that we love all the ingenuity that people put into the x m tool box as a mater of fact i've had a couple of other features ve released that people have immediately i'll release a feature that's a p i own for a while and with the next morning i'll wake up and somebody in australia or new zealand has come up with a tool that helps out and gives people a u i for it in the span

[mark]: 23:49
nice

[nathan_helgren]: 23:49
of like less than twenty four hours like wow it's really cool

[mark]: 23:52
crazy crazy nathan before i let you go is anything else you'd like to add

[nathan_helgren]: 23:57
um you know i would just

[mark]: 23:59
yes

[nathan_helgren]: 23:59
like the people to go out there and give it a try and see if that something might meet their needs you know we wanted to make it the whole reason behind this feature is really just kind of meet customers needs and solve problems and so i'd really like to hear back from people on if this is meeting the need if this is you know up to snuff for what they would like and if there's feedback that they have i'm always very open to feed backs through email or through my linked in you can contact me with any questions about any of the features i work on and i would love to hear that feedback and have discussions with you about it

[mark]: 24:31
nathan thanks for coming on the show

[nathan_helgren]: 24:33
thank you