Some MS Access assistance please!

Ironic Namesake · 24 Sep 2011 at 17:13

I'm trying to make a simple MS Access database (newbie at MS Access) which retrieves data from Sage Line 50 (read only) over ODBC link.

Longer description of my process so far, but basically what I need is :

1. A few Linked Tables via ODBC to Sage, these being used only to read data from Sage - I have these set up.
2. An identical copy of the above Linked Tables , only to update and pull updated/new data from Sage as necessary and to run queries against. These queries can't be run against the Linked Tables directly due to restrictions with Sage's ODBC driver. Updating will consist of some records changing, some deleted , and new records.

Reason being is Sage isn't the best at giving me information I need , and doing manual stock exports and sales order exports etc. is a total pain. I've made a semi working spreadsheet previous to this which gives me most the information I need but that's very slow due to constantly needing to re-calculate all the formulas etc.

I initially did an import of the relevant tables from Sage via ODBC , this worked fine - I've figured out Queries to run which are giving me the information I'm looking for , and faster than Excel , which is great.

I assumed the data imported would be refreshed every 1500s (as per refresh interval) - turns out in order to get updated information I need to use "Linked Tables"

So, I've created Linked Tables - but everytime I run a query this pulls data from Sage, which isn't necessary and will probably slow things down for Sage users considerably once more linked tables are added. And, none of the queries work this way as it seems the Sage ODBC driver isn't very good - eg. Criteria such as <>0 or <>"Complete" don't work - from googling around , there seems to be no way around this when using Linked Tables via ODBC to Sage.

I thought I found a solution to this problem by using a "Make Table" Query - which in theory works , but I can't update it this way as it won't allow to run the query again once relationships between tables are in place.

Thanks in advance, hope this isn't too long!

jamief · 27 Sep 2011 at 14:06

Use SQL Server Express rather than Access. You'll thank me millions of times later.

Participant · 27 Sep 2011 at 15:19

Ironic Namesake said:
And, none of the queries work this way as it seems the Sage ODBC driver isn't very good - eg. Criteria such as <>0 or <>"Complete" don't work - from googling around , there seems to be no way around this when using Linked Tables via ODBC to Sage.

I found 'NOT LIKE 0' worked for me with Sage and linked tables. I'll think about the other stuff you've said and get back to you if I can add anything.

Another thing that springs to mind is, when you say: 'but everytime I run a query this pulls data from Sage, which isn't necessary' - surely it is necessary, otherwise how does the linked table guarantee to have live information at the time of the query? And is it really a huge problem anyway, if you design the queries in such a way that irrelevant records are ignored at the first filter?

And another thing...is there a specific reason why you are bothering with Access? Sage 50 Excel integrated reporting is excellent in my experience, you can create custom reports in Report Designer then a macro to refresh the query in Excel every 1500s, then pee around to your heart's content with pivot tables and charts etc.

Ironic Namesake · 3 Oct 2011 at 12:25

jamief said:
Use SQL Server Express rather than Access. You'll thank me millions of times later.

I did look into SQL Express but I don't understand it at all so stuck with Access for now!

Robbie G said:
I found 'NOT LIKE 0' worked for me with Sage and linked tables. I'll think about the other stuff you've said and get back to you if I can add anything.

Another thing that springs to mind is, when you say: 'but everytime I run a query this pulls data from Sage, which isn't necessary' - surely it is necessary, otherwise how does the linked table guarantee to have live information at the time of the query? And is it really a huge problem anyway, if you design the queries in such a way that irrelevant records are ignored at the first filter?

And another thing...is there a specific reason why you are bothering with Access? Sage 50 Excel integrated reporting is excellent in my experience, you can create custom reports in Report Designer then a macro to refresh the query in Excel every 1500s, then pee around to your heart's content with pivot tables and charts etc.

Thanks, I will give that a try - I did come across that before on Google but hadn't tried it yet. What I've actually done now which is working fine : Created Excel spreadsheet which holds all the data from Sage, refreshed manually as and when I need it to be updated.

There's certain things which Sage doesn't do properly and I couldn't get to work properly via Excel eg. accurate Shortfall reports including selling prices of shortfall items as per Sales Order as well as some others !

Ironic Namesake · 4 Feb 2012 at 21:06

Bumping a slightly old thread.

Has anyone here had luck with adding Sage ODBC as a Linked Server on SQL Express 2008?

I'm trying to do this to replace the Access solution but not getting very far, any advice on how to do this? Using Sage 2012, ODBC Driver V18

Participant · 7 Feb 2012 at 13:38

Have you installed Sage Integrated Reporting? It's meant to bypass all this faff. Using this you can create custom reports for linking to Excel and as long as the table exists, you can query it.

Looks like this on mine:

Not sure how well Sage 50 plays with SQL server since it doesn't actually use an SQL database.

Ironic Namesake · 8 Feb 2012 at 02:28

Robbie G said:
Have you installed Sage Integrated Reporting? It's meant to bypass all this faff. Using this you can create custom reports for linking to Excel and as long as the table exists, you can query it.

Looks like this on mine:

Not sure how well Sage 50 plays with SQL server since it doesn't actually use an SQL database.

Yeah I did have this installed but couldn't really get it to do everything I needed - was better achieved in Access which isn't too bad but would like to move on to something which is more flexible and automated - I think SQL will be one of the ways to achieve this (if linkable), what I currently use Access for - I'll make a list a bit later of what Access is doing for me currently. if you can think of a way to get all this to work in Excel I wouldn't mind doing that - far less hassle than SQL probably!