cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ontario234 Regular Visitor
Regular Visitor

Import Data from Access that uses ODBC connection

Hi,

 

I have MS access database that extracts data from SQL server using ODBC connection. I need to import this database in Power BI desktop. How can i achieve this? I DO NOT want to directly query or import data from SQL server since my access database is well established and contains all the queries and sub queries that I need. Help Please

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Import Data from Access that uses ODBC connection

Hi @ontario234,

 

In Power BI desktop, to get data from Access database, you can use Access Database data source below:

 

q5.PNG

 

As this connector use Access Database Engine, you need to install Access Database Engine version, either 32-bit or 64-bit, that matches your Power BI Desktop version. See: Resolve issues importing Access and .XLS files in Power BI Desktop.

 

By the way, use SQL Server database in Power BI desktop, we can also specify the T-SQL query.

 

q6.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Moderator v-qiuyu-msft
Moderator

Re: Import Data from Access that uses ODBC connection

Hi @ontario234,

 

In Power BI desktop, to get data from Access database, you can use Access Database data source below:

 

q5.PNG

 

As this connector use Access Database Engine, you need to install Access Database Engine version, either 32-bit or 64-bit, that matches your Power BI Desktop version. See: Resolve issues importing Access and .XLS files in Power BI Desktop.

 

By the way, use SQL Server database in Power BI desktop, we can also specify the T-SQL query.

 

q6.PNG

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
JWBennett Frequent Visitor
Frequent Visitor

Re: Import Data from Access that uses ODBC connection

MS Access data sources do not currently have a supported data gateway allowing scheduled refresh.  However, ODBC does.  If there is a back door to using the ODBC data source with MS Access I would like to know about it. 

Highlighted
breadearner Frequent Visitor
Frequent Visitor

Re: Import Data from Access that uses ODBC connection

I currently have this issue as well. When trying to run a query as I would in Access, I get an error and the query returns no results. I have the connectors installed.

 (I inherited a large Access DB that uses ODBC that I have minimal knowledge of. I'm trying to set up a dashboard).

PowerBi Query Failure.jpg

How would I go about using the queries? For example, the SQL query for this is:
SELECT [LNAME] & ", " & [FNAME] & " " & [MI] AS CLIENT, dbo_Daycareschedule.STARTDTE, dbo_Daycareschedule.ENDDTE, dbo_Daycareschedule.SERVDESC, dbo_Grant.GAGENCY, dbo_Daycareschedule.MON, dbo_Daycareschedule.TUE, dbo_Daycareschedule.WED, dbo_Daycareschedule.THU, dbo_Daycareschedule.FRI
FROM (dbo_Climas INNER JOIN dbo_Daycareschedule ON dbo_Climas.CLIENTID = dbo_Daycareschedule.CLIENTID) INNER JOIN dbo_Grant ON dbo_Daycareschedule.GRANT = dbo_Grant.GID
GROUP BY [LNAME] & ", " & [FNAME] & " " & [MI], dbo_Daycareschedule.STARTDTE, dbo_Daycareschedule.ENDDTE, dbo_Daycareschedule.SERVDESC, dbo_Grant.GAGENCY, dbo_Daycareschedule.MON, dbo_Daycareschedule.TUE, dbo_Daycareschedule.WED, dbo_Daycareschedule.THU, dbo_Daycareschedule.FRI, dbo_Climas.CLIENTID
HAVING (((dbo_Daycareschedule.STARTDTE) Is Not Null) AND ((dbo_Daycareschedule.ENDDTE) Is Null) AND ((dbo_Daycareschedule.SERVDESC)="Adult Daycare" Or (dbo_Daycareschedule.SERVDESC)="Adult Day Health Care" Or (dbo_Daycareschedule.SERVDESC)="Extended"))
ORDER BY [LNAME] & ", " & [FNAME] & " " & [MI];