cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
elz Member
Member

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
Highlighted
Super User
Super User

Re: Can I use an sql query to import from access database

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
v-ljerr-msft Super Contributor
Super Contributor

Re: Can I use an sql query to import from access database

@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