Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
elz
Advocate II
Advocate II

Can I use an sql query to import from access database

Hello,

 

I would like to use sql query to select the fields from an access database table to import into power bi desktop.
I currently have the sql queries in a text file.

 

Is this possible?

If so, which option on the Get Data button should I use?
Will this import work for people that do not have ms access installed on their computer?

 

I tried using the ODBC import option.

I selected MS Access Database as the DSN and pasted the sql query into the last box.

 

The connection string field is optional, so I left it blank, but realized that my sql statement doesn't point to the database.


That is where my problem is. Where/how do I point to the database for the sql statement to point to?

 

I vaguely remember doing this in powerpivot/excel, but don't remember having to write code to do it.

 

Any help would be greatly appreciated.

 


Thank you
Tracy

2 REPLIES 2
v-ljerr-msft
Employee
Employee

@elz


I would like to use sql query to select the fields from an access database table to import into power bi desktop.
I currently have the sql queries in a text file.


You should be able to use sql query to import access database into Power BI desktop by using the ODBC import option.


I tried using the ODBC import option.

I selected MS Access Database as the DSN and pasted the sql query into the last box.

The connection string field is optional, so I left it blank, but realized that my sql statement doesn't point to the database.
That is where my problem is. Where/how do I point to the database for the sql statement to point to?


In order to do this, you need to create a DSN for ODBC first. Please refer to How to: Set Up an ODBC Data Source for the detail steps to create a DSN.

AccessDB02.PNG

 

After creating the DSN, then you can go to Power BI desktop, select ODBC import option, choose the DSN you created and put your sql query into SQL Statement, and click OK.

a.PNG

Then click Connect.

AccessDB03.PNG

Last, click load to finish the data import.

AccessDB04.PNG

 

Regards

access SQL is not as advanced as TSQL. It will work as long as it is with in the capabilities of Access

you need to select import from Access under get data

yes it will work without Access installed 

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.