UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Odbc Connection To Sage 50, Access 2016    
 
   
Rose_gardener
post May 27 2020, 09:23 PM
Post#1



Posts: 124
Joined: 12-May 06



Hello All:

Has anyone here ever linked Access to Sage 50? I am trying to following instructions in the following 2 links and running into issues. I've run into multiple brick walls and outlining them all here would be cumbersome. If anyone out here has linked Access to Sage 50, it would be helpful to get some feedback. I've used the following documents to get some idea of how to set up the connection. Mainly, what I am running into is a differing abilities depending on whether I am on my client's server, or whether I am on my client's desktop.

How do I connect to the program using ODBC/OLEDB?
Connecting to your Sage 50 data using ODBC
How do I set up an ODBC Pervasive DSN for Purchasing and Inventory data?


If you've connected an Access DB to Sage 50 in the last year or so, I would like to pick your brain a bit.

Thanks Much - Rose
This post has been edited by Rose_gardener: May 27 2020, 09:24 PM
Go to the top of the page
 
GroverParkGeorge
post May 28 2020, 09:29 AM
Post#2


UA Admin
Posts: 37,447
Joined: 20-June 02
From: Newcastle, WA


Do you know what database Sage 50 uses? I think that's an accounting package, so knowing the database behind it would be more useful, I would think.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
MadPiet
post May 28 2020, 09:31 AM
Post#3



Posts: 3,776
Joined: 27-February 09



Looks like (if my quick search is correct), it uses MySQL as a backend.
Go to the top of the page
 
FrankRuperto
post May 28 2020, 09:53 AM
Post#4



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


Albert Kallal has extensive experience with Access/Sage integration.

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
GroverParkGeorge
post May 28 2020, 09:54 AM
Post#5


UA Admin
Posts: 37,447
Joined: 20-June 02
From: Newcastle, WA


There should either be a proprietary Sage 50 ODBC connector, then, or you should be able to use a MySQL ODBC connector.

Since we don't know what has been tried so far, and what errors occurred in those trials, we can't really offer a lot of suggestions. Maybe someone who uses MySQL can offer some insights, though.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
FrankRuperto
post May 28 2020, 10:02 AM
Post#6



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE (Rose)
I am running into is a differing abilities depending on whether I am on my client's server, or whether I am on my client's desktop.

If the database you are trying to connect to lives on a server, you might not be able to establish the connection even with the proprietary ODBC because the IT admin needs to grant you permissions.
This post has been edited by FrankRuperto: May 28 2020, 10:03 AM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
AlbertKallal
post May 28 2020, 04:33 PM
Post#7


UtterAccess VIP
Posts: 3,101
Joined: 12-April 07
From: Edmonton, Alberta Canada


Ok, how you approach this is going to depend on what you goal is.

First up, the database used is MySQL. I seem to recall they had a version for SQL server, but it don’t really matter.

So, you CAN connect to the database with ODBC. And if I recall correctly the sage logons are MySQL ones.

However the problem/issue/challenge?

Well, it a bit of a jungle of related tables, and the time to figure out what table and what belongs to what table can be a challenge.

Now, I think for just pulling some data, you can use ODBC.

However, the “general” accepted approach is to use the SDK and the interface.

So with the SDK, you can:

Execute a query against tables – often the table have “extra” data and some fields that would require lookups to other tables are done for you.

You can pull an invoice and the child data.

Better yet, you can push out an invoice say from Access to the Sage.

So, my interface (from Access) allows one to push out an invoice.

But, to push out an invoice?

Well, you first have to make sure the customer exists (if not, then add it).

And, if you setup sales reps? Then once again, you have to ensure that the sales rep is added.

So, only after the above two things (customer + sales rep) can you add the invoice.

And of course for the details lines, what product id and if the item is “taxable” has to be setup.

So, it really depends on your end goal.

To just pull some data?

Ok, then ODBC certainly is a possible road.

If you looking to send data from Access? No, ODBC is not really practical.

So, the “interface” thus takes care of all those “tiny” details such as adding records to the “many” underling tables, and does all that detail work for you.

So, such an approach prevents you adding records in a way that would mess up the accounting system.

So, is the goal to just pull data into Access, or is the goal to eventually push data from Access to sage?

I found it easier to pull data using the SDK, but then my overall goal was to add customers, sales reps, and also add invoices directly into sage.

So, is this a pull only, or are you looking to push data?

I created an interface for both sage 50, sage 300, and one for QuickBooks. Most interesting is the Access code is the SAME for all 3, but that’s another VERY long post and issue!

So to be fair? I do pull data from sage 50, but I used the SDK in place of ODBC to do this. The SDK is NOT for the faint of heart, and they don't have a SDK for Access (COM based). The SDK is for .net. So, I wrote the code in .net, and built a consumable object for Access in .net.


Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada


Go to the top of the page
 
Rose_gardener
post Jun 5 2020, 01:53 PM
Post#8



Posts: 124
Joined: 12-May 06



Hello Everyone:

Thank you for all your feedback. Sorry it's taken so long to get back to this thread. I was juggling a lot of work this week. I've read through some of your thoughts and will do some more testing tonight, and see if I can figure things out.

The main Sage documentation I've been using is this Knowledge Base article: How do I connect to the program using ODBC/OLEDB?

My client went into Sage and Followed Directions in Section I
QUOTE
Section I: Allow Data Access from your Sage 50 Company
Open Sage 50 and your company.
Select Maintain, Users, Set up security.
Select the Crystal Reports/Data Access tab on the User Security window.
Under Access from Outside Sage 50, select With the following login information, and then press Change.
Enter a password and confirm it. Note: The password must be exactly 7 characters in length and contain at least one letter and one number.
Click Close to close the window, and you will be ready to use and ODBC or OLE DB connection for read-only access to your company data.


I am using the Pervasive ODBC Engine Interface as directed in the KB article.

I've tried connecting multiple ways, but am running into the problems described in my OP. Some of the suggestions in this thread may help me resolve the situation. This is so frustrating, because I create DSN files all the time for clients using SQL server. I've tried getting assistance from Sage support, but they do not assist with ODBC connectivity issues (sigh).

I did note that one of you mentioned Albert Kallal as a possible resource. I may look him up. Thank you.

Thank you all for your help.

Go to the top of the page
 
cheekybuddha
post Jun 5 2020, 06:38 PM
Post#9


UtterAccess Moderator
Posts: 12,997
Joined: 6-December 03
From: Telegraph Hill


>> I did note that one of you mentioned Albert Kallal as a possible resource. <<

Did you note the author of the response before your last? wink.gif

--------------------


Regards,

David Marten
Go to the top of the page
 
FrankRuperto
post Jun 5 2020, 07:56 PM
Post#10



Posts: 1,099
Joined: 21-September 14
From: Tampa, Florida USA


QUOTE (AlbertKallal)
I created an interface for both sage 50, sage 300, and one for QuickBooks. Most interesting is the Access code is the SAME for all 3

That's an interesting statement, being that Sage and Intuit/QB are competitors. I imagine you're using DotNet interop and a public function for all 3?

Hi Rose,

According to this link, it now seems API's only exist for Sage Online products, so you might have to use ODBC, or an OLE DB connector, to link Access with Legacy Desktop Sage: https://developer.sage.com/api/accounting/guides/overview/
This post has been edited by FrankRuperto: Jun 5 2020, 08:10 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    4th July 2020 - 02:11 PM