ODBC.. Linked vs Import

[jackmac]

Hi all,

I'm building a database which I want to pull information from an ODBC source (Sage 50 2012). I can either Link Tables or Import - both of which bring the data in. Unfortunately when I try to pull from the created Access file I can only see the Imported table I create. Is there a way to update this automatically without doing an import again?

Linked tables update in seconds obviously but I can't see it when I try to connect from Filemaker as it isn't a 'real' table, it's only reflecting data. Unfortunately I can't take from Sage into Filemaker for a number of reasons.

Hope someone can help!

Jackmac

 

[jdraw]

You want to try to describe exactly what you are wanting to do.

Linked tables are just links from Access to the actual source table. If you update the linked table via Access, the actual source table is updated. If someone updates that source table via another application you will see those updates in your linked Access
tables as soon as you refresh the links.

If you import a table into Access, you have a copy of the data from the source at that point in time. If other updates are made in the source, you will not see those updates - because you took a "picture" of the data when you did the import and you basically have a stand alone copy.

In some applications(database systems) you may not be able to update a linked table. There may be security or controls forcing you to a read only situation.

I have not used Filemaker nor SAGE.

 

[gemma-the-husky]

i generally take a copy of the sage files, and let users refresh it when they need to

two reasons

1. prevents inadvertent uncontrolled update of the sage data
2. makes the data available to users who haven't got Sage installed on their PC.

matter of taste though, i think

 

[jackmac]

Thanks for the replies. I'll try to summarise what I'm doing. I need to extract data from Sage and get it into Filmemaker - Filemaker good... Sage bad! If I use Access as a 'middleman' I can pull the data from Sage then Filemaker pulls from Access:

Sage ---> Access ---> Filemaker

The info comes into Access from Sage using a Linked table easy and updates as you corretly point out. Filemaker won't see the Access table as it assumes it's only a mirror and as such has no real table.

If I use Import into Access, it creates a true table which I can then pull into Filemaker. Unfortunately it's a snapshot and isn't automatically updated. If I can get the Import to recheck every say 120 seconds (I'd guess that would be a re-import)
then Filemaker will be a happy camper.

When you say 'take a copy' Gemma, do you copy those files to a new location then the Access file looks at them? If so, that may be another way. Do you have experience (or should I say 'had an experience') with connecting to Sage?

Hope that helps.

 

[Pat Hartman]

Doesn't Filemaker have the ability to link to ODBC data sources? You would link to Sage from Filemaker just as you link to Sage from Access.

Linked tables cannot be "seen" when you link to a database that contains links. The link must be direct. It can't be second-hand. If you import however, you can see the linked tables. So if you import into Filemaker you could do it via Access. I wouldn't though. I would just make the link to Sage from Filemaker.

 

[jackmac]

Hi Pat,

Thanks for the reply. It would be much easier if I could link directly from Sage to Filemaker but unfortunately the table I need to see taken into Filemaker (Sales_Ledger) is not visible. If I go from Sage to Access, the information is visible! I was chatting to someone earlier who advised that unless I buy the Sage Developer Kit (or whatever they call it) then linking to some software is a problem. No kidding!

I can't find a way to make the direct link from Sage to Filemaker as the table is not visible. I have to go through Access as it is visible there then pull into Filemaker. An unnecesserary step I agree but I can find no other way to pull the data in. That was why I was looking for a way to refresh the data in the Access file.

Hope that clarifies a little

 

[Pat Hartman]

I have a client that uses the Timberline product by Sage. Its underlying database is Pervasive which I understand is based on btrieve. It has some real quirks as a RDBMS. Maybe you can find an ODBC driver.

 

[jackmac]

Hi Pat,

Timberline is another Sage product which I want to get the information away from. That's why I'm trying to use Access. Sage seem to be very restrictive on how they allow information to be extracted - even when using their own ODBC connection! Not a fan of Sage I'm afraid

 

[Pat Hartman]

I haven't run into any restrictions per se. Except that some tables can't be updated directly. My problems have revolved around the definition of the customer field. My client's customer IDs are alpha and are 2-6 digits. The problem is that the field is text and defined as right-justified. That makes the two digit codes sort first, then the 3 then the 4, etc. Of course this is completely confusing to someone looking for the C's because there are 5 sets of them. This causes folks to make "new" customer IDs. It also made my combos not work correctly. I was eventually able to fix the problem in my combos and get them left-justified as text fields are supposed to be.

Another problem is the lack of indexing makes some reports extremely painful for the customer and I don't have any ability to tune up the indexes.

 

[gemma-the-husky]

Thanks for the replies. I'll try to summarise what I'm doing. I need to extract data from Sage and get it into Filmemaker - Filemaker good... Sage bad! If I use Access as a 'middleman' I can pull the data from Sage then Filemaker pulls from Access:

Sage ---> Access ---> Filemaker

The info comes into Access from Sage using a Linked table easy and updates as you corretly point out. Filemaker won't see the Access table as it assumes it's only a mirror and as such has no real table.

If I use Import into Access, it creates a true table which I can then pull into Filemaker. Unfortunately it's a snapshot and isn't automatically updated. If I can get the Import to recheck every say 120 seconds (I'd guess that would be a re-import)
then Filemaker will be a happy camper.

When you say 'take a copy' Gemma, do you copy those files to a new location then the Access file looks at them? If so, that may be another way. Do you have experience (or should I say 'had an experience') with connecting to Sage?

Hope that helps.

COPY

I create a table with the fields in that I need - you won't need lots of the sage fields. i then run a query (queries) to populate my table with the sage data - so i never actually keep a live connection to the sage data.

if you need several sage tables, maybe you will need to keep the live connection - but only sage users will be able to see the tables

 

[gemma-the-husky]

Hi Pat,

Timberline is another Sage product which I want to get the information away from. That's why I'm trying to use Access. Sage seem to be very restrictive on how they allow information to be extracted - even when using their own ODBC connection! Not a fan of Sage I'm afraid

do they? what data can you not get at from sage - the odbc connection lets you see lots of fields. it's one thing seeing them, and another knowing exactly what the data is used for though.