AL: Access Connector for Dataverse and Power Platform, by Maria Barnes
HTML-код
- Опубликовано: 11 июл 2024
- Maria Barnes demonstrates the Access connector for Dataverse and the Power Platform. She discusses the Dataverse environment within normal Power Apps (premium connector) and within Teams.
Before starting an export to Datavserse from Access, plan which tables you want and close open objects. You can specify that Dataverse tables will be automatically linked after exporting.
Dataverse uses a GUID (Globally Unique Identifier) for the Primary Key. The Primary Name field is how to pick records.
Presentation slides:
accessusergroups.org/lunch/wp-...
0:00 Dataverse Connector for Access
4:14 Inventory Transactions template
4:29 Prerequisites
7:47 Relationships Diagram
9:08 Export options to Dataverse
12:46 Settings, Power Apps, Admin Center
12:56 Power Platform admin center
14:06 Suppliers table selected in Related Tables dialog box
14:36 [Select Related tables] button future changes
13:04 Environment URL
13:08 Copy link to Dataverse
15:04 Navigation pane - tablbles get exported into Dataverse then renamed
16:00 concept of Primary Name field
16:41 purpose is to display link to a record in the user interface
18:07 Entity in Dataverse
18:19 only short Text fields eligible to be a Primary Key in Dataverse
20:38 Can you change primary name later?
21:13 transaction types
21:48 Primary key is GUID, Globally Unique Identifier
22:53 if you have an AutoNumber, migration has a translation layer
25:08 Export Process
25:20 Karl mentioned, in his opinion, that that you can't change primary name field after its been created
25:42 if you do it wrong, you can delete tables in Dataverse and do again
Migrate docs.microsoft.com/en-us/powe...
Data Types docs.microsoft.com/en-us/powe...
27:26 Attachment is supported but only 1 file
27:36 Dataverse data type called File
27:43 MultiValue
27:45 Help needs updating ...MultiValue in Dataverse is called Choices
Choices columns
docs.microsoft.com/en-us/powe...
Types of columns (contains video)
docs.microsoft.com/en-us/powe...
28:08 'Create column validation error table' if you get errors on Export
28:52 Dataverse column validation errors table
31:56 'Select primary name fields' is remembered for the session
33:02 once passed validation, export happens
34:01 Dataverse tables get '1', '2', ... appended to end when done if same name found.
Original table names in Access appended with "_Local"
34:58 how does Access convert to the 3 text types?
text = 4000 char
text area = 4000 char + crlf and
multiline txt with 1 M chars
36:59 Long Integer
37:48 data types not supported doesn't mention Long Integer
38:13 Done. Successfully Exported tables
38:56 Access Insiders - get Beta version
39:30 Post Export
41:05 prefix on Name is Publisher ID
42:06 notion of Solutions
42:54 Fields
43:12 Custom
43:22 Original field name is what Display name is like
43:40 extra fields
43:55 compare design view in Access
44:50 Data
45:00 edit in Excel
45:05 Views
45:17 Relationships
46:03 keys
46:1g Columns - Primary Key, Type = Unique Indentifier
46:48 Order of fields in linked table insn't preserved
48:02 extra fields in Dataverse are Reserved and most about who owns it, modified it -- don't rename or remove
48:32 original ID is a whole number
49:05 Import Sequence Number stores value of AutoNumber to use for synchronization process
50:03 Edit in Dataverse, see in Access and Edit in Access, see in Dataverse
51:16 Demo Results
51:37 Learning PowerApps
51:44 Karl's workshop at DevCon
52:25 Final Notes
54:04 Primary Name use first defined string unless you change it
54:39 PowerApps in browser UI, save everything
55:03 First time built PowerApp, didn't Save As and lost it
55:53 Questions and Answers
56:34 tricks for better performance
58:14 Referential integrity, Cascade Update, Cascade Delete
1:00:35 how do you connect? Get the address of your site.
1:01:22 Files in block storage, upload to Dataverse then in table storage
1:01:51 Restful API ?
1:02:20 Cluster management? regional management?
1:02:40 create different environments such as dev, test, and production
1:06:04 offline?
1:06:50 in PowerApps, you can work offline, save in local cache
1:10:14 reinvent wheel
1:11:29 using capabilities out of the box means dependency so you have to adjust when changes are made
1:13:42 Dataverse vs SharePoint
1:14:54 switch connection info for Dataverse? use Linked Table Manager
1:15:48 local cache
1:19:04 why use Dataverse?
1:23:52 You can save layouts of linked Dataverse tables
AccessUserGroups.org
Lunchtime chapter host Maria Barnes
Last Tuesday @ noon Central time CT -- join us!
AccessUserGroups.org/lunch
SUBSCRIBE for MORE videos! ► bit.ly/AUGRUclips
✓ Join our free online Access User Groups 
Наука
![Eminem - The Death of Slim Shady [Graveyard Album Trailer]](http://i.ytimg.com/vi/vOJD-6vQpXU/mqdefault.jpg)
thank you, Maria! Great job putting together links and giving Access developers the knowledge they need to know. It was good to see the Dataverse connector in action, get information about data type differences, the Primary Name concept, Primary Key as GUID, possible pitfalls, and the PowerApps environment. Thanks for insight from other experts like Karl, Ynte, Colin, George, and Steve. Good questions from participants, and Adrian helping ensure that chat questions you didn't see (amazing you read so many and not skip a beat) got answered. Nice tip from John too. Thanks to all!
And many thanks to you Crystal for doing such a great job putting the video together so we can share with others
GA is here! If you do not see the connector yet, make sure you are updated to 16.0.15128.20248 or later and you should see it under export data.
Correction on the video from 12:18 thru 13:22, I show you how to go to your Dataverse environment to find the URL to copy and paste into a blank environment in the Access Migration window. While this is technically correct, if you do NOT see this environment already from Access then this likely means you are not signed into the same Microsoft Account in Office as the account you are using to access Power Apps/Dataverse or Teams/Dataverse. Check your Account from the File option in Access. Unless you have just created the environment, Access should show any Dataverse subscriptions you have the rights to. If these do not match then your export will fail.
good to know, thanks, Maria!
Great video, thank you. How can I sync a dataverse table to ms access? When I add new data using power apps to a dataverse table. That data will not show up in access until I relink the table. Is there a way I can get this to sync?
Maria! Thank for your sharing. In ms access, I use admin accout user login. I have migrate table to dataverse. I find that basic user login ms access, they cannot use the access file. It means that the basic user accout cannot access the dataverse table. How can solve the problem? Where can I set the dataverse permission. I want the other user (basic user) also can use ms access file.
Hi Maria! Thanks for your video. Well, I am experiencing a problem that I havent seen anyone mentioning yet. All the fields of type "Yes/No" are not been accepted on the migration process. I've already tested many ways, tested on a new table, but if the table has a "yes/no" field, an error appears " the field xxxx is not updateble". Any ideas?
Is this while editing directly in the linked (to Dataverse) table from Access? How about if you edit the data from Dataverse directly? Or is this on a form? I am not encountering this error. The table shows a check box in that field and it can be checked and unchecked. If you are having trouble only on the form, what is the Record Source, is it the table directly or is it a query? Are you having trouble editing any other fields on the form or only the Yes/No field? If only the Y/N field, have you tried deleting the field from the form and then readding it from the "Add Existing Fields" interface?
What version of Ms access, do I need ? I don't see dataverse connector in mine
Marvin this is still in Beta. You need the Insiders Version of Office 365 at this point. We are still waiting on an official release date for GA.
@@mariabarnes6376 Thanks for that minor but massive detail
GA is here! If you do not see the connector yet, make sure you are updated to 16.0.15128.20248 or later and you should see it under export data. also @Thomas Jones
Hi anyone else having problems with their queries after data migration? My queries have become "not-updateable". She briefly went over this in the presentation. Any ideas? Anyone?
I have found that where you need data updates it is much better to have your data source be the linked table directly. Take a closer look at why you used a query. Is it just for filtering? You can use a form filter instead. Are you bringing in values from other tables. You can do that through setting their control source instead to an =DLOOKUP(..) using the join you would have in your query as the ID in the where clause. My point is that for updateability to a Dataverse table, you need to keep the form data source as simple as possible and put the power in your form by using other Access techniques. Hope that helps.
Hi there... I have an access database living outside my dataverse tenant. Is it possible to create a link this way to enable reporting on the new tenant while the access database and work facing forms remain on the old tenant (for the time being until proper migration). Does the access database have to be on sharepoint to start with as it appears that most of the "migration or linking" tutorials show the access database being resident inside the same tenant as the dataverse tables.
I don't think what you are proposing will work because to do the migration as you have noticed, you have to be logged into the same Office account and your Dataverse environment. The same holds for accessing the linked tables in Dataverse once migrated.
@@mariabarnes6376 Thanks. I was hoping there was a "connector" that existed in the collection but I had missed it (not through lack of hunting though)
@@tonykuiper9127 Thinking about this a bit more. You might be able to migrate data to Dataverse in old tenant, then create an Azure Synapse link to the new tenant. I am not sure if that is available for a Dataverse database or not. It is SQL Azure behind the scenes and you can link to a read only copy of it with SSMS but the Synapse is still new and likely has limits
Maria, do you know when the Access Connector for Dataverse will be released?
Officially Microsoft is still listing March 2022 as the General Availability release on the Microsoft 365 roadmap. Obviously, that did not happen as we are in mid-April already. The product is still in Beta which you can get right now by subscribing to the Insiders channel of Office 365. I can tell you that the Access Product manager is scheduled to make a public announcement on a date change at the end of this month at Access Devcon. Not sure if you are attending that, if not, I can let you know once he makes that public announcement.
Thank you very much for letting me know that, @@mariabarnes6376. I won't be attending Access Devcon. Please let us know when it might be released when you find out. Thank you!
@@mariabarnes6376 , any word from Microsoft on when the Access Connector for Dataverse will be released?
@@gioargentati7802 YES! A couple of weeks ago at the AccessDevCon conference, they announced that it would be May, so anytime now. Does not look like the Roadmap has yet been updated but hopefully you should see it very soon!
Awesome! Thank you for the update @@mariabarnes6376 ! :)
Will vba migrate?
Access links to the Dataverse tables so VBA can still be used from the Access front-end.
Dataverse is a data tier component. It fills the same role as any other database back end: ACE (native Access), SQL Server, MySQL, etc. The Access interface itself is not changed, although you often do have to modify the interface to accommodate remotely hosted server-based databases, i.e. data sources "in the cloud".
If you're a moron like me that could'nt find your environment in the first step of linking the tables, you have to create a database in dataverse on the powerapps website first.