Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 726
  • Last Modified:

FM - Using ODBC

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
rvfowler2
Asked:
rvfowler2
  • 7
  • 5
1 Solution
 
jvaldesCommented:
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
 
rvfowler2Author Commented:
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
 
jvaldesCommented:

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
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!

 
rvfowler2Author Commented:
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
 
jvaldesCommented:
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
 
rvfowler2Author Commented:
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
 
jvaldesCommented:
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
 
rvfowler2Author Commented:
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
 
jvaldesCommented:
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
 
rvfowler2Author Commented:
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
 
rvfowler2Author Commented:
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
 
rvfowler2Author Commented:
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

Industry Leaders: 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!

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now