Alpha Video Training
Results 1 to 21 of 21

Thread: ODBC and Sage Accounts

  1. #1
    Member Bob Whitaker's Avatar
    Real Name
    Bob Whitaker
    Join Date
    Jun 2000
    Location
    Sutton-Coldfield, UK
    Posts
    694

    Default ODBC and Sage Accounts

    When trying to create a passive link table to connect to Sage Line 50 V9 everything goes great and when I test the connection thats shows that it has succeeded but as soon as I click the "next" button to select the tables to link to Alpha instantly crashes back to the desktop.
    Bob Whitaker

  2. #2
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: ODBC and Sage Accounts

    Bob,
    I have done this successfully. What I did was configure a new data source using the mas90 4.0 osbc driver, and in the logon potab of the configuraton dialog, I put in a specific company and a user code with a password.Until I did this ,I was unsuccessful
    John

  3. #3
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: ODBC and Sage Accounts

    of course I meant odbc not osbc and potab is another typo

  4. #4
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default Re: ODBC and Sage Accounts

    Just wondering, does MAS 90 use pervasive sql like Peachtree? I spoke to Peachtree about linking to their data and they said it was a proprietary version of Pervasive and there was no API, no driver, no way to link anything. If it can be linked that would be great.

    Russ

  5. #5
    Member Bob Whitaker's Avatar
    Real Name
    Bob Whitaker
    Join Date
    Jun 2000
    Location
    Sutton-Coldfield, UK
    Posts
    694

    Default Re: ODBC and Sage Accounts

    I was using the 32 bit ODBC driver that comes with Sage, I succesfully connected but it was when I clicked next that Alpha crashed.
    What is the driver John refers to and where did he get it ?

    Bob

  6. #6
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: ODBC and Sage Accounts

    Bob,
    what version of Mas90 are you using.I was using 4.05, and this driver is most likely added when installing levels 4 and above
    Russ,

  7. #7
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: ODBC and Sage Accounts

    Russ,
    I was under the impression that there was something that one could purchasew that allowed crystal to talk to Peachtree. So they probably sell an odbc driver with a dictionary for their data.
    John

  8. #8
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: ODBC and Sage Accounts

    Russ,I did aearch on crystal and peachtree. It looks like if you have 2005 premium edition and higher, you have a crystal report option. If that is true, there must be an odbc driver forthis level of Peachtree. There may very well be no option for your level of Peachtree other than upgrading.

  9. #9
    "Certified" Alphaholic
    Real Name
    Russ Boehle
    Join Date
    Mar 2000
    Location
    Connecticut
    Posts
    2,767

    Default Re: ODBC and Sage Accounts

    Yeah, I asked them about that as I already have Crystal, although I'd have to dust it off. The version of Crystal that comes from peachtree is custom for Peachtree so they say that's all that can be used with it. It's not that big a deal as they allow import and export via csv. For what I want to do that should work although a bit clunky.

    Russ

  10. #10
    Member Jenny Earnshaw's Avatar
    Real Name
    Jenny Earnshaw
    Join Date
    Feb 2001
    Location
    Hertfordshire, UK
    Posts
    77

    Default Re: ODBC and Sage Accounts

    Quote Originally Posted by Bob Whitaker View Post
    I was using the 32 bit ODBC driver that comes with Sage, I succesfully connected but it was when I clicked next that Alpha crashed.
    What is the driver John refers to and where did he get it ?

    Bob
    I have managed to create a Passive-Link connection to Sage Line 50 Version 12 using the Sage ODBC driver.

    Like you, I succesfully connected but when I clicked "Next" it didn't crash. However, I couldn't see any of the Sage tables so I decided to use the option "Portable" SQL Statement and entered the following:

    SELECT ACCOUNT_REF, NAME, ADDRESS_1, ADDRESS_2, ADDRESS_3, ADDRESS_4, ADDRESS_5, C_ADDRESS_1, C_ADDRESS_2, C_ADDRESS_3, C_ADDRESS_4, C_ADDRESS_5, CONTACT_NAME, TELEPHONE, TELEPHONE_2, FAX, E_MAIL, WEB_ADDRESS, TRADE_CONTACT, DEL_NAME, DEL_ADDRESS_1, DEL_ADDRESS_2, DEL_ADDRESS_3, DEL_ADDRESS_4, DEL_ADDRESS_5, DEL_CONTACT_NAME, DEL_TELEPHONE, DEL_FAX, ANALYSIS_1, ANALYSIS_2, ANALYSIS_3, DEPT_NUMBER, DEPT_NAME, STATUS_NUMBER, STATUS_TEXT, DEF_TAX_CODE, DEF_NOM_CODE, VAT_REG_NUMBER, CURRENCY, COUNTRY_CODE, DISCOUNT_TYPE, DISCOUNT_RATE, PRICING_REF, SETTLEMENT_DISC_RATE, SETTLEMENT_DUE_DAYS, PAYMENT_DUE_DAYS, CREDIT_LIMIT, ACCOUNT_ON_HOLD, TERMS, RESTRICT_MAIL, TERMS_AGREED, DATE_ACCOUNT_OPENED, DATE_NEXT_CREDIT, DATE_LAST_CREDIT, CREDIT_POSITION, CREDIT_POS_CODE, CAN_CHARGE, BUREAU_CODE, CREDIT_REF, DATE_CREDIT_APPLIED, DATE_CREDIT_RECEIVED, OVERRIDE_TAX_CODE, LAST_PAYMENT_DATE, FIRST_INV_DATE, LAST_INV_DATE, BALANCE, TURNOVER_MTD, TURNOVER_YTD, PRIOR_YEAR, DEFAULT_FUND_ID, GIFT_AID, INVOICE_BF, INVOICE_MTH1, INVOICE_MTH2, INVOICE_MTH3, INVOICE_MTH4, INVOICE_MTH5, INVOICE_MTH6, INVOICE_MTH7, INVOICE_MTH8, INVOICE_MTH9, INVOICE_MTH10, INVOICE_MTH11, INVOICE_MTH12, INVOICE_CF, CREDIT_BF, CREDIT_MTH1, CREDIT_MTH2, CREDIT_MTH3, CREDIT_MTH4, CREDIT_MTH5, CREDIT_MTH6, CREDIT_MTH7, CREDIT_MTH8, CREDIT_MTH9, CREDIT_MTH10, CREDIT_MTH11, CREDIT_MTH12, CREDIT_CF, PAYMENT_BF, PAYMENT_MTH1, PAYMENT_MTH2, PAYMENT_MTH3, PAYMENT_MTH4, PAYMENT_MTH5, PAYMENT_MTH6, PAYMENT_MTH7, PAYMENT_MTH8, PAYMENT_MTH9, PAYMENT_MTH10, PAYMENT_MTH11, PAYMENT_MTH12, PAYMENT_CF FROM SALES_LEDGER

    I've now got a Passive_link table called Sage_Cust as the Sales Ledger is the Customer table. I managed to create a form and to refresh the data.
    Jenny Earnshaw
    BusinessPlus (Letchworth) Ltd

  11. #11
    Member Bob Whitaker's Avatar
    Real Name
    Bob Whitaker
    Join Date
    Jun 2000
    Location
    Sutton-Coldfield, UK
    Posts
    694

    Default Re: ODBC and Sage Accounts

    Thanks Jenny and all

    It seems a lot of work to do something that should just work. Hopefully Alpha will look at this and come up with a solution.

    Regards
    Bob

  12. #12
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: ODBC and Sage Accounts

    Russ,
    Russ,
    My guess is that the only thing custom about it is that it comes with an odbc driver for Peachtree and some ddf files(data dictionary) for peachtree.do you haveversion 2005
    Peachtree may not be in a hurry to sho their file srtructures to folks ub=nless they make a purchase from them.
    John

  13. #13
    Member
    Real Name
    Martin Blackwell
    Join Date
    Sep 2007
    Posts
    9

    Default Re: ODBC and Sage Accounts

    Bob,

    I was looking for an answer to this as well. Crystal Reports opens the tables via SDO without a problem. The SQL suggestion from Jenny could have been:- "select * from sales_ledger". That works ok but these 'passive' links do not auto-refresh (I think). The real problem with Sage is that the actual files are an ancient flat-file design and that design is not sql friendly - you only have to see the new Report Designer to see where they have added horrible functions to provide data in a useable form. I would not do anything serious win this area until they migrate to a real database (not holding breath...).

    Cheers

  14. #14
    "Certified" Alphaholic
    Real Name
    JohnZaleski
    Join Date
    Oct 2000
    Posts
    1,736

    Default Re: ODBC and Sage Accounts

    As i mentioned quite a while ago in this thread, if you are using a version of mas90 4.0 and above mas90 has a new odbcdriver MAS 90 4.0 ODBC Driver and everything works just fine.However if you plan to use it quite a bit you're probably better off creating a special dsn for this use and putting a company, user code and password in the ODBC configuration setup otherwise you will be prompted for these time and time again
    Ther is no problem with the types of files Sage uses, the ODBC driver takes care of that.
    It displays all the mas90 tables properly and everything else works as it's supposed to.

  15. #15
    Member
    Real Name
    Martin Blackwell
    Join Date
    Sep 2007
    Posts
    9

    Default Re: ODBC and Sage Accounts

    John,

    Thanks, I did indeed see your post.

    Bob was looking specifically for Sage Line 50 - a UK product which has a small entry price (sub $700). The MAS software you refer to is from a long line of companies hoovered up by Sage in its bid for global dominance. I'm certain it's an excellent product but you wouldn't typically buy both - you might upgrade from Line 50 to something like MAS but not in the UK where it does not appear for sale. As to if you could just buy the driver...

    Regards

    Martin

  16. #16
    Member
    Real Name
    Paul Watkins
    Join Date
    Dec 2007
    Location
    Wales
    Posts
    2

    Default Re: ODBC and Sage Accounts

    Hi, I tried to start a thread on this on 10th Dec. So far, 83 reads and no replies! My original need was simply to add some more seats to our original Alpha 5 v4.5 build 266, we don't have a british importer anymore so I downloaded v8 Pro and a 3user runtime. Any of which gives me my extra seats and runs all the code fine with one exception, we have some tables for supplier info and sales info and open transactions which are batch refreshed from a SageLine 100 v6.2 accounting system using the ODBC driver which works fine. I would like to update completely to v8 but can't until I can crack this connection. I tried with v8Pro, failed, read the small print, got v8 Enterprise, still failed, put a message on the board, since then I've tried every vendor option, got a table of what dumps me, what gives me an sql syntax error and sql state code. I've now got MySql and a really thick book to practice connecting to, since I obviously don't know enough about SQL. I'll get there oneday, but it is especially galling that v4.5 did it so easily and new improved v8 doesn't, I keep reminding myself I'm an engineer not a programmer. I could use some help, if anybody takes pity on me, I don't mind a reply on this thread, but it would be lovely to get my reply count up above zero on my 1st and only post.

  17. #17
    Member Bob Whitaker's Avatar
    Real Name
    Bob Whitaker
    Join Date
    Jun 2000
    Location
    Sutton-Coldfield, UK
    Posts
    694

    Default Re: ODBC and Sage Accounts

    Hi Paul,
    This at least gets your reply count up even if it doesnt solve yours (and my) problem. I wholly agree this is a matter that should be resolved given the dominance of Sage in the market place. I would be happy to pay for an odbc driver that works. This is what we have done with Quick Books.

    Regards
    Bob Whitaker

  18. #18
    Member
    Real Name
    Martin Blackwell
    Join Date
    Sep 2007
    Posts
    9

    Default Re: ODBC and Sage Accounts

    Mr Watkins,

    As you can see from this thread, Sage does not provide a stable or well defined ODBC interface that works consistently across multiple development platforms. You may well find that AV8 or 9 or 10 fails later with other versions of Line 100. I would try using an intermediate database to get at the Line 100 data and refresh the tables on a regular basis. To be honest, if you want just customers and suppliers then keep it simple - extract CSV using any report writer that can be batched and then upload to the database of choice. You then have complete control of the process.

    And Happy New Year...

    Martin

  19. #19
    "Certified" Alphaholic
    Real Name
    Charlie Crimmel
    Join Date
    Apr 2000
    Location
    West Virginia
    Posts
    1,695

    Default Re: ODBC and Sage Accounts

    Horizon CRM 1.70.401 download by Visionbase Software Ltd
    Powerful CRM with links to Sage & Quickbooks with the following features :-Contact Management, Marketing Campaigns, Opportunities, Quotations, Sales Orders, Invoicing, Purchase Orders, Links to Sage Line 50 and Quickbooks, Diary, Email Centre plus .. Horizon is a powerful CRM with links to Sage & Quickbooks Features include Contact Management, Marketing Campaigns, Opportunities, Quotations, Sales Orders, Invoicing, Purchase Orders, Links to Sage Line 50 and Quickbooks, Diary, Email Centre
    Released: 10/01/2006; Filesize: 33.4 MB; Price: USD $0.00;
    Platforms: Windows NT, Windows, Windows 2000, Windows XP, Windows 2003
    Tags: Campaign, Contact Management, Defect, Helpdesk, Invoicing, Knowledge Base, Opportunity, Project, Quotations

  20. #20
    Member
    Real Name
    Martin Blackwell
    Join Date
    Sep 2007
    Posts
    9

    Default Re: ODBC and Sage Accounts

    Charlie, I think you posted this reply to the wrong thread (its not the purchase order one you were replying to)

  21. #21
    "Certified" Alphaholic
    Real Name
    Charlie Crimmel
    Join Date
    Apr 2000
    Location
    West Virginia
    Posts
    1,695

    Default Re: ODBC and Sage Accounts

    No: I was refering to

    Horizon CRM 1.70.401 download by Visionbase Software Ltd
    Links to Sage Line 50 and Quickbooks

    They might have a connection to Sage that alpha could also use.

    It is a free download
    http://www.download32.com/horizon-crm-i5693.html

    Charlie Crimmel

Similar Threads

  1. Export to Sage
    By Michael Humby in forum UK Users
    Replies: 10
    Last Post: 11-07-2006, 06:47 AM
  2. Linking to Sage Accounts
    By tarlachmoorhouse in forum Alpha Five Version 5
    Replies: 1
    Last Post: 01-27-2004, 12:33 AM
  3. Export to Sage
    By Michael Humby in forum Alpha Five Version 4
    Replies: 1
    Last Post: 06-26-2003, 09:30 AM
  4. Alpha 5 v 4.5 & Sage ODBC Driver
    By Robert Stewart in forum Alpha Five Version 5
    Replies: 5
    Last Post: 06-08-2003, 02:19 PM
  5. Alpha 5 v 4.5 & Sage ODBC Driver
    By Robert Stewart in forum Alpha Five Version 4
    Replies: 0
    Last Post: 06-05-2003, 12:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •