Avatar of Thomas Zucker-Scharff
Thomas Zucker-Scharff
Flag for United States of America asked on

How do I connect an MS Access database to a PowerBI gateway?

I am new to PowerBI and am trying to set up a gateway.  When it asks for the Data source type in data source settings MS Access is not listed, what do I choose?

Microsoft AccessDatabasesPower BI

Avatar of undefined
Last Comment
Thomas Zucker-Scharff

8/22/2022 - Mon
John Tsioumpris

Have you checked the documentation from MS : https://docs.microsoft.com/en-us/power-query/connectors/accessdatabase
Thomas Zucker-Scharff

ASKER
John thanks for the link. I should have looked there first - will do now
Thomas Zucker-Scharff

ASKER
John,  

Although this worked somewhat - it didn't solve my problem.  Maybe if I explain better, it might help.  I have the following already setup:
  • PowerBI was installed on my system in the 32 bit form
  • Due to constraints on the time we had to develop the dashboard, I went ahead and developed the PBI dashboard
  • I finally got them to install the 64 bit version (having the 32 bit version was a non starter when using the 64 bit version of MS Access).
  • Now I want to change the current data source from excel spreadsheets, which I was forced to use during development, to my MS Access DB.
  • I watched several videos online on how to do this and they all say the same thing, change it o the back end in the advanced editor
  • I tried the Advanced editor route, but I always get the same result, it breaks half of my stuff
  • I decided to try importing the MS Access Database into PowerBI and rebuilding the whole thing (not my preferred remedy, but I though it might be worthwhile)
  • I finally got everything imported (one large table was giving me trouble but it finally worked)
  • When I go to rebuild the application, it does work at all the same.  When I select lastname, firstname, department from the table I was having problems with, the relationship is not at all correct (the database does a lookup by ID to the department table and returns the name, in the DB).  In PowerBI the department lookup simply returns all 1's
  • If I select the department from the department table it list all the departments before listing all the lastnames and firstnames - no correlation
  • This is probably the most important table in the database
  • I tried editing the relationships in the PowerBI relationship editor to no avail (or I just don't understand it well enough)
  • I would really rather just change data sources, but I have been unable to figure out how to do that without breaking everything.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Thomas Zucker-Scharff

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.