Solved

FM - Using ODBC

Posted on 2011-02-22
12
718 Views
Last Modified: 2012-05-11
I'm looking into FM's ODBC connections, but without any luck.  I've read all the FM help on ODBC.  I haven't done this for 7 years since I used access, but I remember it being much easier (just a couple of clicks and settings).  To clarify, we only import tab or Excel files exported from a DOS database.  However, I read somewhere that ODBC can be used for flat files such as tab delimited or Excel.  However, when I try, it asks for an ODBC driver and none are listed (on the host).  Do I have to load some drivers on the server?  There aren't any built in for tab or Excel files?  
0
Comment
Question by:rvfowler2
  • 7
  • 5
12 Comments
 
LVL 9

Expert Comment

by:jvaldes
ID: 34957789
Unfortunately Microsoft does not supply a database ODBC driver for excel, instead you need to buy one from actual technologies. It will come with a setup panel that will allow you to set it up to read the excel tables. If you have the excel tables on a PC, and you use the PC to serve the excel ODBC services then you can access it from filemaker as well and you don't have to buy drivers.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 34960557
What about a tab delimited file.  For us, that's the most common import into FM.  I can't seem to set it up.
0
 
LVL 9

Expert Comment

by:jvaldes
ID: 34962025

The way I have solved this problem is to import into another table. Then use the lookup option to update the changes in the old table with the values in the new table when related by a record identifier. Also at that time you can lookup a field to see if IsEmpty() gives you a true value . Telling you that record is missing in the other table. Use this approach to delete the records that didn't come in on the import. Use it in the other direction to identify new records that don't exist in the current table. This is fast and reliable, I use it on a number of autoupdate databases.  
0
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 
LVL 2

Author Comment

by:rvfowler2
ID: 34963363
Your last post answers one of my other questions, but not this one.  In this one, I'm asking if there is a way to do an ODBC connection with a tab delimited file so that I never have to import.  Changes to the tab delimited file would be immediately reflected in FM.  Is this possible or just not done?  Thanks.
0
 
LVL 9

Expert Comment

by:jvaldes
ID: 34963638
I don't know of a way to do it literally, but you could emulate the feeling by having a Filemaker application that makes imports every minute and updates a filemaker table that is connected to the other application as an ODBC connection.

Filemaker is a great database to migrate data between unlike data sources. We use a time triggered server to serve all foreign data as filemaker tables and it works great. When the trigger is made it imports tables from many different apps and updates the data files on the the other applications in a transparent fashion. Gives the impression of multiple systems updating the same data. We trigger some tables when the contents of the table is changed and stored. so as not to loose data.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 34963696
OK, maybe I am wrong, but what I've read on ODBC and my faint memory of using it 7 years ago is that an ODBC connection is dynamic in the sense that whenever you open a db or refresh a page, the data showing is updated without using the import function.  I think I'm going to test it with an Excel file.
0
 
LVL 9

Expert Comment

by:jvaldes
ID: 34963859
You are correct. My misunderstanding is that I thought you were using a tab delimited file. What application are you using to share that file on ODBC? The file by itself cant provide ODBC connection. If you import it into excel you can publish it as ODBC from excel and that will allow you to share it. That is what I was using filemaker to do with that file.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 34964115
No, you understood right.  I was using a tab delimited file.  I just thought I'd try connecting to some Excel file via ODBC to learn how to use ODBC (I'm also getting my first consulting job and it requires this very thing).  However, FM Help is useless as a guide to learning how to connect via ODBC.  I just tried to connect to an Excel file, but can't make heads or tails of the attached screen.  7 years ago when I did it in Access, I remember it being fairly easy.


-ODBC.JPG
0
 
LVL 9

Accepted Solution

by:
jvaldes earned 500 total points
ID: 34970717
I see you are on a PC so, go to administrator tools under the control panels and create and ODBC source of the excel type. Then go to the screen that you have above from filemaker and select that table. And at this point the data should be available to filemaker.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 34971530
Thanks for the heads up about going to Admin Tools.  I did create a system driver for Excel and for Text files (I thought all along it was possible to do so for text files).  However, when I go back to the FM screen above, I'm lost.  I don't see anywhere where I can connect to the specific file in the screen above or select the system driver I just created.
0
 
LVL 2

Author Comment

by:rvfowler2
ID: 34971909
So sorry to ask such basic questions.  I've looked around and don't see any basic ttraining on ODBC and Windows.  I just remember it being really easy 7 years ago from Access in a SBS2003 environment.  It's the FM piece that seems not so intuitive.  Is there anything I can read?  
0
 
LVL 2

Author Closing Comment

by:rvfowler2
ID: 34973311
Sorry, I had looked on the wrong FM Help page for my answer.  Looks like Filemaker does not support an ODBC connection to either Excel or a txt file.  See below.  However, Microsoft did provide the ODBC drive - I went into Control Panel:Admin tools and was able to add a systemDSN driver for Excel and one for text files.  However, when going through FM, it didn't list either, probably because of the limitation below:

Data sources supported in FileMaker 11.0
You can use the following data sources in the FileMaker Pro relationships graph. If you are importing ODBC data, or using the Import Records or the Execute SQL script step, you can use additional data sources, such as IBM DB2 or Informix ODBC.
 •  Oracle 9i

 •  Oracle 10g

 •  Oracle 11g

 •  SQL Server 2000

 •  SQL Server 2005

 •  SQL Server 2008

 •  MySQL 5.0 Community Edition (free)

 •  MySQL 5.1 Community Edition (free)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question