Solved

FM - Using ODBC

Posted on 2011-02-22
12
713 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now