Solved Dynamics365 connector for Access is broken (Dataverse as well)

[monheimx9]

Hello everybody
I've been experiencing a quite disastrous situation today

To give a little bit context:
My company use Dynamics365 CRM to manage accounts and contacts, we don't really use it a lot except for that purpose
Everytime we add or modify customer accounts, I run small queries (from the linked tables) on a small Access database just to update our SQL backend so we can use this data in our main production Access Frontend, basically it's just to update the customer table

Until now it has worked "almost" with no issues (if we put aside that one time Microsoft decided to translate all the Dynamics365 database fields names from English to French, my native speech without any notice)
It has worked like... 3 years if I recall correctly

Now... it's totally broken
I can't even open the linked tables, and if I try to delete them and re-link them, they don't even appear in the object explorer anymore

Even worse, If I try to link only the "Accounts" table, sometimes Access crash, and the other times only the table "Contact" is linked.
But I can't even open it, I get this error : "No active records"

(sorry it's in French)

So have anybody else experienced that issue? Any hints that I could explore?

I've tested that database on multiple different systems, even different versions of Access, all the same
Even fired up a VM with a fresh install, nothing can do I can't even link tables from Dataverse or Dynamics365 to a fresh Access database

To all the readers, thank you for your time

Update:
It appears that not all the tables are broken (but ofc the ones I need are :^)
And if someone is wondering, yes I've looked directly into the Dataverse/Powerapps website thingy to see if everything works fine, and yes, all my records are still there and I can see online what are in those tables

 

[GPGeorge]

Not very many Access people have ventured into the realm of Dataverse, for at least a couple of reasons. One is that they are only available to people with the appropriate version of MS365.

You can't link to your Dataverse or Dynamics365 tables from a fresh Access database. To me that might imply a permissions problem (but that depends, in part, on what specific error occurs when you try to link them.) Sign out of MS 365 in your Access installation, and then sign back in under the correct account to see if resetting that might help.

 

[monheimx9]

Thank you for the hint George, sadly it did not move an inch
I've forgot to mention but there's also a weird behavior if I try to open the linked table from this morning backup
I double click on the table, Access freeze for 5 seconds, and then nothing happen (visualy)
But after that if I open a local table (any local table) and try to close it, I get this message

"Can't execute code in break mode"

Click OK, message reapear instantly, until the task is killed via the task manager

As much as I love Access, sometimes it can drives me nuts

My best bets for now are to wait until tomorrow to see if it's on Microsoft side and suddenly it would resolve by it self, or I can contact the IT company that sells us the Dynamics and Office licences, maybe they'll be able to help

The other solution I could use is directly pull those data through the webAPI, but I can't stress this enough: I've found next to nothing on how to authenticate with Microsoft Dynamics365 service through VBA and webAPI oAuth/tokens
(And I would love to see an AUG conference on that matter one day if there's an expert that knows this specific subject ;^) )

I'll keep this thread updated once I've figure it out

 

[GPGeorge]

Spoiler: etc

Thank you for the hint George, sadly it did not move an inch
I've forgot to mention but there's also a weird behavior if I try to open the linked table from this morning backup
I double click on the table, Access freeze for 5 seconds, and then nothing happen (visualy)
But after that if I open a local table (any local table) and try to close it, I get this message

"Can't execute code in break mode"

Click OK, message reapear instantly, until the task is killed via the task manager

As much as I love Access, sometimes it can drives me nuts

My best bets for now are to wait until tomorrow to see if it's on Microsoft side and suddenly it would resolve by it self, or I can contact the IT company that sells us the Dynamics and Office licences, maybe they'll be able to help

The other solution I could use is directly pull those data through the webAPI, but I can't stress this enough: I've found next to nothing on how to authenticate with Microsoft Dynamics365 service through VBA and webAPI oAuth/tokens

(And I would love to see an AUG conference on that matter one day if there's an expert that knows this specific subject ;^) )

I'll keep this thread updated once I've figure it out

And I'll hold you to giving an AUG presentation when you figure out how this works.

 

[isladogs]

And I'll hold you to giving an AUG presentation when you figure out how this works.

Oi - stop stealing all the best ideas before any of the rest of us get a look in!

 

[monheimx9]

And I'll hold you to giving an AUG presentation when you figure out how this works.

Hehe not on that matter, but I've developed an interesting workflow around SQL backends with speed optimization in mind, and that might be worth a presentation :^)

Oi - stop stealing all the best ideas before any of the rest of us get a look in!

Now, update on the subject of the thread
My first bet was right, that issue did indeed resolve by itself, like magic
I opened the tables without any issue this morning, and linking new tables was working again like it never happened

I guess I can mark this thread as solved
Thank you for passing by

Cheers

 

[isladogs]

Well if George doesn't object, I'd be interested in following up on that topic for Access Europe.
It could make a good follow up to two of our previous sessions:

AEU-7: Optimising Queries and the JET ShowPlan

Colin Riddington demonstrated ways of optimising queries and the use of speed tests to compare performance. In addition, two tools were demonstrated: JET ShowPlan was used to show query execution plans and ISAMStats to measure disk reads

www.isladogs.co.uk

AEU-13: Optimizing Queries in SQL Server

This month’s session was led by Maria Barnes (MVP). Maria discussed how to optimize queries using the various tools provided in SQL Server Management Studio.

www.isladogs.co.uk

If interested, could you contact me via a PM or email using the link below

P.S. I hope George realised I was joking in post #5

 

[GPGeorge]

No problem. Humor is hard in this environment because the smirk on one's face is not visible.

Seriously, I'd love another presentation sometime on Dataverse and Access, albeit for a different reason.

I'm currently looking at Dataverse and PowerApps because MS recently enhanced the ability to work offline for Dataverse in the PA Canvas apps as well as Model Driven apps. I doubt we'll see that for Dataverse tables linked in Access, but then there are probably few scenarios in which that would be as relevant.

 

[GPGeorge]

Hehe not on that matter, but I've developed an interesting workflow around SQL backends with speed optimization in mind, and that might be worth a presentation :^)


View attachment 108587


Now, update on the subject of the thread
My first bet was right, that issue did indeed resolve by itself, like magic
I opened the tables without any issue this morning, and linking new tables was working again like it never happened

I guess I can mark this thread as solved
Thank you for passing by

Cheers

It's after the fact, but there is a way to check for problems in MS 365. And here.

 

[monheimx9]

It's after the fact, but there is a way to check for problems in MS 365. And here.

Thank you for that, I've found two issues that might have been correlated with my problem, but the descriptions are somewhat
vague, so I can't confirm for sure

https://portal.office.com/adminportal/home#/servicehealth/history/:/alerts/MO603795

https://portal.office.com/adminportal/home#/servicehealth/history/:/alerts/MO597504

If interested, could you contact me via a PM or email using the link below

I'm not sure if you were talking to me or George

 

[GPGeorge]

Thank you for that, I've found two issues that might have been correlated with my problem, but the descriptions are somewhat
vague, so I can't confirm for sure

https://portal.office.com/adminportal/home#/servicehealth/history/:/alerts/MO603795

https://portal.office.com/adminportal/home#/servicehealth/history/:/alerts/MO597504

I'm not sure if you were talking to me or George

We each have our own AUG chapters, so I believe that was addressed to you.

 

[monheimx9]

Ok then
isladogs, I'll be in touch in the next few days
I can organize a small demo of what I've done in recent years with SQL as a backend (currently Azure SQL for production, and SQL Express for development)
And if I'm not full of disillusion, you might find it interesting
(In case you wonder, I'm a young developer and might not have the expertise required, even though I work with computers from a very young age, you have more things to teach me than I have to teach you :^) )

 

[isladogs]

Hi Jeremy - yes it was addressed to you
George leads the Access Pacific chapter - I run Access Europe.

Just to be clear, we're really not in competition with each other
Both chapters meet on the first week of each month (next week) - AEU on Wed , AP on Thur

Anyway. Happy to have a chat about this
Suggest you look at the videos from the two sessions first in case we've already covered your idea