?
Solved

Run SQL Query on Sage 50 Database

Posted on 2014-08-29
12
Medium Priority
?
5,125 Views
1 Endorsement
Last Modified: 2014-09-05
Hi all,

I am trying to find out how to run sql queries on the Sage 50 database (wherever it is stored).

I have a Windows 7, with Sage 50 installed and I need to get to the database of it and run some sql queries.

How can I do this?
1
Comment
Question by:error77
  • 6
  • 6
12 Comments
 
LVL 10

Expert Comment

by:Bruce Denney
ID: 40292325
Which Sage 50 is this, UK/CAN/US ?
Version number would also be useful
0
 
LVL 10

Expert Comment

by:Bruce Denney
ID: 40292337
For UK versions you just use ODBC

The connection sting is


ConnectionString = "ODBC;DSN=" & DN & ";UID=" & UR & ";PWD=" & PW & ";DIR=" & DR & ";"

Where

This uses the real Version number not the year version number tacked onto  SageLine50v
Example for V2013
'DN = "SageLine50v19"

UserName
'UR = "manager"

Password
'PW = ""

The path to the Data files this has \ACCDATA on the end of it

'DR = "S:\ACCDATA"
0
 

Author Comment

by:error77
ID: 40293510
What software should I use to connect to it or does it create a connection that I can connect to via wamp web if I wanted to?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:error77
ID: 40293513
It's the UK version btw
0
 

Author Comment

by:error77
ID: 40294703
Anyone please?
0
 
LVL 10

Expert Comment

by:Bruce Denney
ID: 40295368
Perhaps you should explain what you are trying to do.

Sage 50 can have a massive database and reading it over the internet is not really viable.
0
 

Author Comment

by:error77
ID: 40295462
Hi,

Well, basically I want to try to perform manual SQL queries from Sqge 50.

I don't want to have to build a report just write and run the sql query.

Hope this help

Thanks
0
 
LVL 10

Expert Comment

by:Bruce Denney
ID: 40295546
You want to get data out of Sage 50 into what? to do what?

For example you can read data from Excel using SQL statements (via MS Query)

Similarly you can read data using ODBC from programming languages eg C, Visual Basic, php,Java, asp, Delphi..

They all work the same way, you open an ODBC connection and then use SQL to get data returned from the other database.
0
 

Author Comment

by:error77
ID: 40295549
What I'm I connecting to... I mean where do I find the data file from Sage 50 ?  Or do I need to export to csv and then connect odbc to that?
0
 
LVL 10

Accepted Solution

by:
Bruce Denney earned 2000 total points
ID: 40296314
ODBC allows you to issue SQL queries against the Sage50 Database, as I detailed in my earlier response, you need to include the path to the sage 50 data directory.

Are you asking for the path to your Sage 50 Database files, so that you can put it in the ODBC connect string?

The instructions for finding the path to your Sage 50 Data directory are dependant on the version number.
If you look around in Help>About you should be able to find it somewhere.  If not give me the version number.
0
 

Author Comment

by:error77
ID: 40296388
Thanks again for your response.

The version number is: 17.0.15.208

Thanks again
0
 
LVL 10

Expert Comment

by:Bruce Denney
ID: 40296840
Go to help>about>System information

Look on the left side for Directories click on it
on the right you will see Data Directory and the path, right click and open folder to see the whole path.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question