Linking Sage 2008 to access 2007

[suepowell]

HI All.

I am having trouble with filtering and linkoing data from SAGE to my access database where I want to produce some clever reports.

I have posted on the sage forum, but though I might get more help here, as I have in the past.

Here is my problem.

I am trying to create a report in Acces 2007 using the sales_order table.

If I try to read orders where despatch_status = "part" it's fine, but if I try to read orders where despatch_status <>"complete" I get the error message odbc call failed syntax error: Invalid filter in WHERE clause (#0).

If I try to use the criteria = "part" or = "complete" I only get those records that fit the second criteria.

I am also having trouble linking tables where. If I use an inner join it's fine, but if I try to use a left join (which I need to to not miss any records) It fails with invalid outer join specification (#0).

Anybody had this problem with SAGE? Any ideas would be greatfully received.

Thanks.

Sue

 

[gemma-the-husky]

despatch_status <>"complete"

possibly just needs a space between <> and "complete"

did you have a space with = "part"

 

[suepowell]

HI Gemma,

Thanks for the reply,

I'm sure it's not that, I used the QBE grid to create the SQL, just a nice simple select criteria, so I'm sure it's something to do with how SAGE reads the query rather than how access writes it.

Thanks anyway.

Sue

 

[Rabbie]

HI Gemma,

Thanks for the reply,

I'm sure it's not that, I used the QBE grid to create the SQL, just a nice simple select criteria, so I'm sure it's something to do with how SAGE reads the query rather than how access writes it.

Thanks anyway.

Sue

Sue, In that case I think you need to check the Sage documentation to see what its rules are.

 

[Elicono Elliconnon]

"Sage Rules", Access 2007 & The Expression Builder

Hi all,

I am experiencing exactly the same problems that sue had back in the day.
When I import the Data Tables from Sage by actually linking to it, the filter would not acually work.
I tried to edit the tables in the design view and enter the criteria but access gives me all sorts of errors including the one mentioned.
it was suggested that the documentation needs to be viewed in order to find out what the rules are...

Please tell me what documentation has to be inspected and where can i get it???????
I am sure that we all are on the right way, however,
I looked at all and every bit of documents to find out any IT stuff on Sage, but all that I could have found was the reference to the Expression Builder In Sage Report Designer that is no help at all to be fairness.

I rang Sage but they do not know what is Windows Vista have certainly never seen Microsoft Access Database 2007 before in their lives at all.

Guys, if anyone has any sort of information that may lead me understanding as to why the common expressions would not work and/or especially what can I do about that???????

Thanks Very Much!
EE

 

[gemma-the-husky]

are you connecting with ODBC - if you dont pick a Pk in the linked table, you wont be able to update any records, and you get problems with queries

perhaps they want you to pay to become a sage developer

 

[suepowell]

Hi,

I have had a response from SAGE which wasn't very helpful.

Here it is.

The ODBC driver for Sage Line 50/Instant Accounts is intended for use with the Sage Report Designer, and therefore support is only available up to the point of connection to the Sage company data set. The Specific SQL required to query data from the Accounts Database is not currently not supported.

Not quite the claims they have on their web site!

This is a link to their documentation showing all the operators and how they work, but you will have to have a SAGE account to read it.

Link: http://ask.sage.co.uk/Scripts/ask.c...up_adp.php?p_faqid=12651&p_created=1153293481

Basically I was using the correct operators in the correct way according to their documentation so I just tyhink they are not really interested in supporting Access, the latest access support articles on their website are for access XP!

In answer to Gemma's point, I have tried with and without a primary key, (SAGE suggest not to use one) but it makes no difference. I am not trying to update anyway, the ODBC link is read only, I just want to produce reports specific to my customers needs.

Thanks for your thoughts and comments. Good luck.

Sue

 

[gemma-the-husky]

what i do is (eg) have a Jet Sl_Customers table, link to the sage table with ODBC, and update my customers table for new items and changes. doing it this way means i only need to refresh the data occasionally, and not actually rely on a live link to sage, especially as not every user on the system necessarily HAS a connection to Sage - although they can ALL see the Jet table

can you do it that way?

 

[suepowell]

Thanks for the reply Gemma.

I have now managed to structure my querys so they will work. I can check the value of a code is less than 2 rather than the field doesn't = a text value, or equals 2 text values.

I also found the table link I thought I needed wasn't correct as I was looking at the wrong table. SAGE table names mostly make sense but there is room for confusion if you don't know the SAGE table structure very well.

If I need to get more creative with my queries I think I will have to go the route of importiong the data into local tables and querying those, but for the momwent it is working, and the client will get real benefit from it being completely live.

Thanks again for your help.

Sue

 

[Elicono Elliconnon]

Hi all,
Thanks very much fo all your replies.
The problem that I have is that Sage Structures Data not exacly the way that it may allow SQL Database application like Access to interpret it correctly even though through the help of the Sage - SQL Driver it is possible not only to view the tables but also to create querries & reports.
It appears to me that the problem exists only when I link the Sage data tables to Access and the driver then has to not only structure the data to make it available for the SQL Based applications to work with but also to structure it in a way that is convenient to the reader.
Although it is better for all and especially our clients to work with Sage Data linked directly to access which hands over our client chances to deal with fresh and updated tables.
I see no way therefore that the Sage Driver has to be maintained unless Sage does it themselves.
Has anyone ever had a so called Sage Developer Edition?
I know that the difference between the Sage Dev. Edition and the ordinary Sage is only one dll file, anyone knows what is it?
Would Sage have a development tool for the Sage ODBC Driver?

 

[gemma-the-husky]

i think you have to pay to be a sage devloper, then you get more tools etc, - i think its hard to update files inside sage when you are guessing

i actually generate an audit trail that sage can import, using documented methods

one of my users has had something special done by a sage developer to interface a bit differently

 

[Elicono Elliconnon]

Hi all,
I'd say that the primary objective so far is not to be able to "change" the data within the Sage Tables but to make the Sage Driver to be able to "understand" the formula commands.
Say, when I link to the Sage Data Tables? cretate query, open the it in the design view and try to apply <>0 in the criteria row of Aged Balance it does not work givving me the error message, but if I enter "Not Like 0" it will actually work, although the same command does not work with the text fields.
Anyhow, did somebody ever try to apply different filter code that might work so that the query will not show the deleted transactions, for example?

By the way if i try Like "*Deleted*" it actualy do the trick and the queryshows me only the deleted transactions...
While if I enter Not Like "*Deleted*" it does not work...

 

[gemma-the-husky]

if <> 0 fails, but
not like 0 works then perhaps it is treating every field as text, even if its clearly numeric

------
as i say, ive never tried to query data directly, other than to copy certain columns from a sage table into a different table in my database, and the types come across correctly (date/number etc)

 

[Elicono Elliconnon]

Oh Yeah,
If I import the tables everything works perfectly.

But is there anything that we can change in Access that might actually "Start Treating The Values Right"? somehow "Affect The Driver"

 

[tbayliss]

Sage Developer programme

Glad Sue Powell got her immediate problem solved but I can reply to her and Ennico's questions about the Sage 50 Developer Programme as I am an Access/VB developer registered on this program which covers Accounts & Payroll and costs £1200 + VAT a year.

It does not help you directly with ODBC problems as it bypasses ODBC altogether, directly reading from and writing to the data files using a library of ADO objects. These can be addressed using other tools but it's far easier using VB6/Access VBA or VB.NET as Sage provide example code.

A third party developed the ODBC driver for Sage, which explains Sage's lack of helpfulness when it comes to any problems with it.

As for the Developer Toolkit: it works, it is far faster and more flexible than ODBC for reading data and, most importantly of all for developing add-ons to Sage, you can write transactions into Sage. The built-in Sage 50 "Import" facility is OK for one-offs when you start using the system like names and addresses and opening balances but clumsy for users and limited in what you can and cannot import.

I have written many Sage add-ons using Access that users can then tweak if they have the knowledge but the bits of VBA code directly linking to Sage I prefer to hide as misuse of these tools could cause Sage data problems.

Tony Bayliss (West Midlands)

 

[tbayliss]

Sage Developer programme

Glad to see Sue Powell got her immediate problem solved but I can reply to her and Ennico's questions about the Sage 50 Developer Programme as I am an Access/VB developer registered on this program which covers Accounts & Payroll and costs £1200 + VAT a year.

It does not help you directly with ODBC problems as it bypasses ODBC altogether, directly reading from and writing to the data files using a library of ADO objects. These can be addressed using other tools but it's far easier using VB6/Access VBA or VB.NET as Sage provide example code.

A third party developed the ODBC driver for Sage, which explains Sage's lack of helpfulness when it comes to any problems with it.

As for the Developer Toolkit: it is far faster and more flexible than ODBC for reading data and, most importantly of all for developing add-ons to Sage, you can write transactions into Sage. The built-in Sage 50 "Import" facility is OK for one-offs when you start using the system like names and addresses and opening balances but clumsy for users and limited in what you can and cannot import.

I have written many Sage add-ons using Access that users can then tweak if they have the knowledge but the bits of VBA code directly linking to Sage I prefer to hide as misuse of these tools could cause Sage data problems.

Tony Bayliss (West Midlands)

 

[Elicono Elliconnon]

Third Party Driver?

Hi all,
I heard that there are a lot of different developer toolkits for sage out there.
What toolkit are we talking exactly?
I am still working on trying to apply elements VB Coding but it seems to be that i am hitting myself against the wall here.
What is ment by "example code" that sage provide?
I agree that my ignorance knows no boundaries, but i am really sorry to disturb.
EE

 

[tbayliss]

Sage Developer programme

In response to Elicono, there is only one developer toolkit for Sage 50 that I know of and that is the one produced by Sage itself. There may be third party ones but I don't know of them. There are certainly many third party add-ons for Sage 50 produced using this toolkit. By paying my £300 per quarter, I get updates every time a new version of Sage 50 or Payroll 50 comes out and also when new functionality is added to the toolkit.

The kit includes the library file that goes in your windows/system folder and you need to register that in Access/VB. The example code shows you how to do things with your new library of Sage data objects, e.g. post a sales invoice "object" into Sage, using VB and following the rules for that object - valid date, customer, nominal code, VAT code etc. As part of your customised sales invoice add-on, you may read from the customer data so for example you can check the status to see if that customer is On Hold and/or let the user change the values of fields in Sage.

You can also write routines that update lots of records e.g. changing prices of products based on any criteria you specify.

Tony Bayliss

 

[i_Am_Johno]

A simple solution to the problem

I get round the fact that the Sage ODBC driver does not like the "Not Like" or not equal "<>" expressions, by using the IIF statement to turn the text string into a number. e.g. if I want to create a query that excludes all Sales Ledger accounts that have "Suspended" or "Finished" in the field Analysis_1. I enter the following expression in a blank Field in the Access query design grid,
IIf([ANALYSIS_1]="Suspended" Or [ANALYSIS_1]="Finished",1,0)
Then in the Criteria for that column enter '0' (zero)
This will have the same result as putting <> "Suspended AND <> "Finished" in the Criteria of the field ANALYSIS_1

 

[Petroc88]

This thread is running again on the Sageforum site but it still doesn't address the issue of why some people have no problem using the <> and NOT LIKE operators whilst other people do. There are various ways of working round the problem and thank you Johno for your contribution but it still doesn't get me any nearer finding out why we have this abnormal behaviour in the first place!