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
Last Comment
Thomas Zucker-Scharff
Have you checked the documentation from MS : https://docs.microsoft.com/en-us/power-query/connectors/accessdatabase
ASKER
John thanks for the link. I should have looked there first - will do now
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:
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.
ASKER CERTIFIED SOLUTION
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.