Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

SAGE 50 Accounts and QlikView

Has anyone managed to link SAGE 50 accounts to qlikview via SQL 2005? The tables in SAGE are DTA files, I am struggling to bring SAGE through via ODBC link.  Any help would be appreciated, Mandy

1 Solution

Accepted Solutions
Not applicable
Author

Mandy,

You're a miracle, thank you so much for your patience and support.  I have now managed to load the Sage data via my laptop into Qlikview and can start working on the Qlikview code that you kindly shared with me.

In case anybody else subsequently reads this thread, my problem was with the ODBC driver.  As Mandy correctly points out, this only works on the physical computer onto which the full Sage software is loaded.  Simply running the installation routine to load it onto your PC or laptop does not run a complete installation. Again in the name of completeness, the error messages I got included references to data mismatches and a message saying ODBC driver not found when I tried to remove the Sage driver from my laptop.  I got around this by loading the whole Sage 50 Accounts 2013 application onto my laptop then running the ODBC installation from the ODBC32 subdirectory.  You can then totally ignore the Sage installation and access the Sage data on the shared drive on the server.  The files can be found, as Mandy says, in the driveletter:\Accounts\ACCDATA in a series of DTA files.  Save these as QVDs as described in Mandy's post.

I'm not sure anybody will ever read that but it could save them the three weeks it took me to work it out if they do.

Mike

View solution in original post

12 Replies
Not applicable
Author

Hi Mandy,

I am struggling with the same thing.  Did you ever find an answer to your question?  There seems to be a thundering silence about anything to do with Sage on these forums.

Regards

Mike

Not applicable
Author

Hi Mike - yes I managed to crack it with a lot of patience and trial and error, and using your DAT back up files of SAGE rather than trying to link directly.

The key is linking the ACCDATA folder, which contains the DAT files via ODBC as follows:

Go to administrative tools from the control panel - select data sources (ODBC)

Go to system dsn and add the latest Sage Line 50 version (should automatically appear on the list)

click finish - give your data source a name you will remember, and add the data path - if you go to my computer and look to where your ACCDATA files are stored - use the properties of this as the data path.

If you are asked for login codes use the administrator ones rather than individual user codes.

If you can get this bit to work you then need to create qvd files before you can begin to create your dashboards.

Let me know how you can get on - I can let you know the key data tables to use for your QVD's - we have a great dashboad that we use to help us analyse our sales trends.

Mandy

Not applicable
Author

Hi Mandy,

Thanks for your help.  I still can't get this to work.  The whole ODBC setup and administration seems to not work rather than your suggestion of how to do it so I would like to thank you personally for trying.  It seems you have  a unique skill in getting Sage to connect to Qlikview!  Good luck.

mike

Not applicable
Author

That's dissappointing - dont give up.  If you want to email me directly I can provide screen shots to try and help you?  Are you working on the server computer? ie the computer that the back up files are stored within?

I note from another post that you made that you are wanting to look at invoice data - thats one of the pages within the dashboard that I've set up - the company are now finding that they use QlikView when dealing with customers and suppliers rather than SAGE because it is so easy to bring data from different reports through to the same screen.  It would be a shame if you didnt finish it - believe me if I can do it any one can!

Code to create the invoice QVD's is below, the passwords and user id's are pulled through from the ODBC set up - use the admin codes.  Remember to build it on a computer that has access to the server files, ie where the backups are stored.  Good Luck, Mandy

code.jpg

sample dashboard.jpg

Not applicable
Author

Mandy,

You're a miracle, thank you so much for your patience and support.  I have now managed to load the Sage data via my laptop into Qlikview and can start working on the Qlikview code that you kindly shared with me.

In case anybody else subsequently reads this thread, my problem was with the ODBC driver.  As Mandy correctly points out, this only works on the physical computer onto which the full Sage software is loaded.  Simply running the installation routine to load it onto your PC or laptop does not run a complete installation. Again in the name of completeness, the error messages I got included references to data mismatches and a message saying ODBC driver not found when I tried to remove the Sage driver from my laptop.  I got around this by loading the whole Sage 50 Accounts 2013 application onto my laptop then running the ODBC installation from the ODBC32 subdirectory.  You can then totally ignore the Sage installation and access the Sage data on the shared drive on the server.  The files can be found, as Mandy says, in the driveletter:\Accounts\ACCDATA in a series of DTA files.  Save these as QVDs as described in Mandy's post.

I'm not sure anybody will ever read that but it could save them the three weeks it took me to work it out if they do.

Mike

Not applicable
Author

Hi Mandy,

I'm also working with Sage50 and I'm running into a problem in finding how Sage50 calculates the Cost Of Good Sold.

They are using GetPeachRowAmountv2() function that calculate the Cost of Sale on the fly/

Do you know anything about it?

Neta

Not applicable
Author

Hi Mike,

Thanks for adding your experience and specifics, very useful. Yes it got read!

Also, it would be a good to confirm on the post that your question was answered.

Kind Regards,

Dave

Not applicable
Author

Hi Dave,

Thank you for your feedback.  I'm glad my post proved top be useful.  I'm afraid I am new to this forum and can't find out how to let people know that Mandy's answer was correct.  I can put videos, smileys and all sorts of stuff up here but not mark the answer a correct. Can you point me in the right direction?

Mike

Not applicable
Author

Hi sorry I dont really know much about the function side of Sage - just the back up data tables.  Mandy