ODBC - Manual Linking to Sage

[mph1976]

Hello All,

Am dipping my toes into the wonders of ODBC linking to Sage via Excel and have come up against a little bit of a brick wall.

I am essentially trying to recreate the Customer Profit Report inside Excel (via an ODBC link) with all the variables that exist inside the Sage report. The problem I am getting is that Excel (?) cannot automatically import all the variables I have listed - I am guessing this is because they are from different tables that are clashing (just a wild guess to be honest!). The pop-up message that I get refers to the fact that I need to MANUALLY LINK the variables...and this is my problem...

Is there a quick and easy fix to this (prays) or do I need to think again?

The main issue seems to be when I am trying to add 'Sales_Ledger' and 'Stock.' fields to those already added ('Invoice.invoice_number' etc).

Any ideas as always greatly appreciated

NB I am running Excel 2007, version 16.0.17.0158 (Sage Line 50 Accounts Professional 2010) on Windows XP.

Thanks, Martin

[brucedenney]

You need to link the tables in MS Query

There are some issues with MS Query "automatically refreshing the data" when you first open it.

If you put unlinked tables into a query then MS Query load all the lines in the unlinked table for each line in the original table, so... 1000 invoice lines with 100 invoices with 100 customers with 100 products results in a query returning 1,000,000,000 lines, not surprisingly this results in MS Query getting very busy and most people think it has hung. Most installations have a lot more records than this.

The way to work around this is to switch it off first or to just put one table in to start with. At the end of the wizard choose to edit the query in MS Query rather than return the results to Excel. At this point you can switch off auto refresh and add in the tables without creating a huge amount of traffic.

Then create the joins between the tables (as you would in the report designer), refresh manually to check you have your joins correct, then return the data to Excel.