Solved

FM - Using ODBC

Posted on 2011-02-22
12
720 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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