Columns not appearing in ODBC Connector

[Kaps]

I have also posted this question in another board - as it is starting to my head in ...

http://www.experts-exchange.com/Apple/S ... 11328.html

I am trying to set up an ODBC connection from SAGE to Filemaker. The ODBC connection is now working. However in the query builder I can see only the table names - but I cant see the column names within the tables.I am using Filemaker 12 on a Win 7 machine. I have tried both SAGE 2008 and SAGE 2013 and got exactly the same problem.

Curiously if I connect to Access or Excel, I get the column names. I dont know how to use Access and Excel will be too cumbersome. thanks

Kaps

[brucedenney]

Have you set up your own DSN to connect to?

[Kaps]

have got a system DSN connection.

[brucedenney]

I think it may be the method you are using to access the data.

I am no expert in Filemaker, however, I have come across this sort of thing before.

There are 2 methods for accessing data via ODBC one of them works for SQL databases e.g. MSSQL, Oracle ... The other works for other ODBC sources.

http://www.filemaker.co.uk/support/tech ... /odbc.html


The problem with using Sage ODBC as an SQL source is, I think, because Sage (foolishly) set the delimiter in ODBC to be a comma, as a result, the SQL statements that are created are ambiguous.

Some software e.g. MS Excel doesn't check for validity of SQL statements and allows the ambiguity, many others eg libre/open office data pilot, checks for ambiguity and rejects the connection. With libre/open office you can copy and past the SQL from MS Query into libre/open office and it works even though the data pilot will not accept it.

You could be facing the same thing here, if you try using the browsing tool (an equivalent of MS Query or DataPilot) things might not work, but feed it an SQL statement and it will.