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.

View solution in original post

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.

View solution in original post

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. 

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];

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 80 members 1,376 guests
Please welcome our newest community members: