Community
Showing results for 
Search instead for 
Do you mean 
Reply

OLE DB to Office 365/Power Bi

New Member
Posts: 4
Country: United_Kingdom

OLE DB to Office 365/Power Bi

 

Hi,

 

We have recently upgraded to Office 365, when I try and connect Act to OLE DB using external data settings my 'Act! OLE DB Provider for Reporting 2.0' has disappeared.

 

Any ideas how I now connect? If I select OLE DB from data sources it asks for a connection string (non-credential properties) with the advanced option of an SQL statement.

 

Ideally I want to connect to Power Bi but I think both products will connect the same way.

 

Many Thanks in advance.

 

CC Liv

 

Act 20.1.120.0, Update 4

Office 365

Power Bi

Employee
Posts: 333
Country: USA

Re: OLE DB to Office 365/Power Bi

Hello,

I am not familiar with the OLE DB functions, but found this article

25186 How to Create an OLE DB 2.0 Connection in Act!

http://kb.act.com/app/answers/detail/a_id/25186/kw/OLE%20DB

 

Thank you,

Car Mag
Act!  Support

Highlighted
New Member
Posts: 4
Country: United_Kingdom

Re: OLE DB to Office 365/Power Bi

Brill, thanks, I'll have a read now :-)

New Member
Posts: 2
Country: United_Kingdom

Re: OLE DB to Office 365/Power Bi

Hi. Have you had any success. I had the same issue. I've managed to establish a direct link with Excel, but still searching for how to do this from Power BI. For Excel, I had to go to the Excel Help search box and search for the legacy "Data Connection Wizard".  This brings up a dialog box - click Other/Advanced - then you'll see an option for "ACT! OLE DB Provider for Reporting 2.0". The just enter the database file name with full path, and your ACT! login details.

Let me know if this helps, and I would welcome any assistance with Power BI as I'm unable to find an equivalent method of access so far.

 

Thanks, Anthony

New Member
Posts: 4
Country: United_Kingdom

Re: OLE DB to Office 365/Power Bi

Hi,

 

Sorry for late reply!

 

I have manged to connect to PowerBi, here are the steps I followed.

 

  •  Contact Act for your SQL SA account password, this is something they can only give to you and without it you wont be able to connect.
  •  Set up a new ODBC connection in windows
  •  Within USER DSN create a new SQL Server connection, give the connection a name, you then need to add the database server name I found the correct path for this on database information within Act.  Click Next
  •  Select with SQL server authentication. connect to SQL server to obtain default settings box checked and then add SA for user account and the SA password off Act.
  •  Click next, then leave as default, it should say test connection and hopefully it will be successful.
  • Then within PowerBi add a new ODBC data source, you should see Act on the dropdown, select it and then use the SA account details again.  This should then work and you'll be able to see the tables.

Hope this helps!

 

New Member
Posts: 2
Country: United_Kingdom

Re: OLE DB to Office 365/Power Bi

That's brilliant - thank you for your help!  Will have a go