ODBC connection - problems

[BrentHedden]

I'm writing a VB.net app that will pull some info from a FM database. Fairly simple concept.

I'm using FMPA11 to host, and have already setup a System DSN that tested A-OK.

My problem stems from constructing the connection string in VB.

Here's a sample of what I've got -

[color:red]Imports System.Data.Odbc

Dim myConnection As OdbcConnection = New OdbcConnection()

myConnection.ConnectionString = ""

myConnection.Open()

What do I need to put in the connection string to work?

I've tried a few different combinations, but nothing works so far.

Thanks for any help!

[Wim Decorte]

Any reason why you can't use fmDotNet. It uses the XML interface under the hood so it doesn't need an ODBC DSN.

Check out the DataDirect forums on their website, they probably have some good info on how to construct those connection strings.

[Garry168]

Hi, Brent, Wim

I am doing the same thing, and stuck on setting up a System DSN. Where do you get now? could you please share the knowledge with me?

******

We need to extract data from FileMaker version 6 system used by one of our customers. I am completely new to FileMaker, and the customer doesn't have immediate resource to help with this legacy system.

This FileMaker 6 is still a live system on another server. I don't want to interrupt it.

I want to use FileMaker 6 as an external data source, and create a daily ETL process in Windows 2003/SQL 2005 to extract data from FileMaker 6 into SQL server for reporting purpose.

I already installed FileMaker Pro 6 and FileMaker Pro 11 trial. I can open the .fp5 files and see the data through the fm pro 6. And I can convert the fp.5 data files to fp.7 with fm pro 11, and see the data as well.

Now I am stuck on creating an odbc data source in Windows XP/2003 after installing the odbc driver found in fm 11 folder. I don't know how to fill up the Host and Database fields, to connect to the fp7 files.

I only see a new driver FileMaker ODBC. when I try to use it to create an ODBC data source, I get the message "There was a problem connecting to the host."

Do I need to install some kind of service stuff to host the data, expose a port for exteral access?

Thanks a lot

[BrentHedden]

Thanks for the response, Wim. I had a hunch that you would respond.

I have used the fmDotNet utility, and I must say - it's an incredible piece of work.

I'm constructing a "simple" example of robotics control using FM and .NET for a class I'm going to present, and wanted to use SQL statements, of which I'm quite familiar with. I've used ODBC plenty of times in the past to connect to other databases.

Another reason is that I'm using FMP Advanced, not Server in this example. I believe FM.NET only works with FMServerA, correct?

[BrentHedden]

Garry -

To answer (part of) your question - FM6 uses a different ODBC driver altogether than FM11. If you are trying to connect to the V6 server, the client drivers should be on the installation media.

One thing you've got to be aware of - if you're using a 64 bit O.S., you MUST setup the DSN on the 32 bit ODBC Administrator (%WINDIR%SysWOW64odbcad32.exe). FM doesn't have any 64 bit drivers yet.

It's been a while for me using pre FM7 products and ODBC, so I'm a bit rusty. I'm sure that FileMaker still has guides available for setting the DSN up.

[Wim Decorte]

fmDotNet works with both regular FMS and FMSA whereas for ODBC you need to have FMSA (doesn't work with regular server).

[Wim Decorte]

In FileMaker 6, there is no direct ODBC connectivity to filemaker server. You have to open the files in filemaker pro as a client of filemaker server and then use ODBC against the files opened on the client. Not very stable at best.

You do have to find the filemaker 6 odbc drivers and you need to set up the files for remote data access. You can't use filemaker 11 in this mix if the files are hosted on filemaker server 5/5.5 because that would require a conversion and an upgrade of all clients to the filemaker pro 11 software.