Power Pivot error when getting data from ACCESS query

Question

The data I was using in EXCEL pivot tables suddenly went beyond the row limit for EXCEL; Went to 1.7 million rows. Now I need to use power Pivot but have the following issue. I have a query (view) in ACCESS that adds a lot of derived fields to transaction data for year, month, day, etc, so the user doesn't have to add those. It's a straight up Select query. I go into Power pivot and chose databases then ACCESS, set the path to the ACCESS database, and the test is successful. When I go to the tables or views I see the query in the list.But after selecting it, when I hit next to load the data I get the following error:

"OLE DB or ODBC error: ODBC--connection to 'LDODBC' failed.; 3151.

An error occurred while processing table '1ACTMasterQuery'.

The current operation was cancelled because another operation in the transaction failed."

I am using linked tables in the ACCESS app using an ODBC pipe named LDODBC, and none of those show in the list of tables or views. Do I need to set a connection string? There is no good explanation I could find on the web. What part don't I understand.

Thanks

RADO · Accepted Answer · 2018-06-08 05:00:05Z

1

I would strongly recommend NOT to connect to a query. Save the query result into an Access table first. Then connect to the saved table from PowerBI - it will be simple, fast and robust.

answered Jun 8, 2018 at 5:00

RADO

8,1873 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jpl458 Over a year ago

Figured that out yesterday. Thanks for the answer. It's all you said.

Collectives™ on Stack Overflow

Power Pivot error when getting data from ACCESS query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related