Powerpivot and Sage 50

Powerpivot and Sage 50

Didn't find your answer?

Hi

I have tried to import data using Powerpivot from Sage 50 v18 (2012) but for some reason it just won't connect to the data using the ODBC datasource connection.

Can anyone help at all. The error message is User ID or Password invalid ": ERROR [28000] User ID or Password invalid

ERROR [01000] The driver returned invalid (or failed to return) SQL_DRIVER_ODBC_VER: 18.1
ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
ERROR [01000] [Microsoft][ODBC Driver Manager] The driver doesn't support the version of ODBC behavior that the application requested (see SQLSetEnvAttr)."

I can import via ODBC and MS Query normally using Excel data connection no problem but Powerpivot just doesn't work even though the ID and password used are exactly the same as importing via Excel. I have reinstalled the Sage ODBC driver to no avail.

Any help much appreciated. Thanks 

Replies (7)

Please login or register to join the discussion.

avatar
By paulwakefield1
05th Dec 2012 12:57

Try this

From Get External data on the Home tab of the PowerPivot window, select From Other Sources and select Others (OLEDB/ODBC).

Underneath Connection String,choose "Build"

In the Data Link Properties window, Connection tab, under 1.Specify the source of data

Choose "Use Connection String" NOT "Use data source name"

Choose "Build" and select the Machine Data  Source tab and choose your Sage source from there.

Thanks (0)
avatar
By paulwakefield1
05th Dec 2012 13:42

Except I meant to add......

This should solve the precise problem you are having but may not enable you to get to the data tables. :-)

I thought I cracked this at some point but either my emeory is faulty (probable) or I have lost the file.

Thanks (0)
avatar
By paulwakefield1
05th Dec 2012 17:21

This may help

I remember now that I was unable to create a connection that would recognise Tables from within the PowerPivot environment.

I needed to create a simple connection in the workbook (Just DSN, UID and password). Then, using "Existing Connections" in the PowerPivot Design tab, I was able to pull in the tables. The connection in the workbook could then be deleted.

I only played around with it originally to see if it was possible* but have never used it in anger.

*To which the answer is "Yes" but it's not intuitive!

Thanks (0)
avatar
By jamieyoung
06th Dec 2012 11:05

Thanks Paul

I gave it a go but like you couldn't get to the tables. In your last answer you said:-

"I needed to create a simple connection in the workbook (Just DSN, UID and password). Then, using "Existing Connections" in the PowerPivot Design tab, I was able to pull in the tables. The connection in the workbook could then be deleted."

How do I create this simple connection in the workbook so that it appears in Existing connections in Powerpivot Design Tab? Tried but struggled with this.

Any help much appreciated

Thanks (0)
avatar
By paulwakefield1
06th Dec 2012 11:56

There may be a better way and

you may be able to shortcut some of the follwing but I hope this will work for you:

In the Workbook:

Data tab

From Other Sources - From Data Connection Wizard - ODBC DSN

Choose your Sage ODBC data source and enter login details

Select the Table (and change any other details you wish)

Finish and return the data to the Workbook

In PowerPivot:

Open the PowerPivot window

Design tab - Existing connections

From Workbook Connections" section and NOT the Local Connections section, select the connection just created. Make sure the name does not have any illegal characters such as brackets around "Default" - but don't worry too much, it will soon tell you and it is simple just to edit the name at that point.

Validate if wished or just go straight to "Finish". This should import the data into PowerPivot. You should then be able to delete the Workbook imported data (I have only briefly tested this last point but it seemed to work OK).

Thanks (0)
avatar
By barrysnashall
06th Dec 2012 12:06

Sage and Excel

Hi

 

What data are you looking to connect to?

 

The Analyser Sage and Excel addin is a great way to connect the two applications and then from Excel you can drill down back to line by line detail by any date, department and nominal codes. All codes can be single, group like the COA or grouped outside the normal range i.e. in(5000,6900,5100).

 

If you need to connect directly to the tables, would always be pleased to help with a remote connection to your PC (for free) Sage and Excel are two of my loves and the challenge of things not working makes this even better!

 

[email protected]

Regards

 

Barry

Thanks (0)
avatar
By jamieyoung
06th Dec 2012 12:18

Paul - That's great and did the job perfectly. Thanks for your help.

Barry - thanks for your kind offer but all sorted now

Thanks again everyone

Thanks (0)