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
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.
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.
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.
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!
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).
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!
Regards
Barry