Filemaker db to ms Access using ODBC

[Jelena Puzic]

Can anyone please explain to me how to link a FM Pro db into Access 97 (win98) using ODBC.

I have tried to set up the link with the File Maker Pro driver (there is also a Merant FM .txt driver which is not licenced with the FM5 Pro)and I am not able to connect for some reason.

Is there a special trick or specific driver I should be using? I am a new and very inexperienced FM user - so please excuse the question.

Thank yo for your help

Jelena

[kraftyman]

Which way is your data transfer?

Access into FMP or FMP into Access?

If Access is the source, then:

1.Open the control pane ODBC source applet

2.Create a new UserDSN from your Access database

3. Choose the MSAccess driver

4. Give your source a name

5. Choose the directory and database

6. Close the ODBC applet

7. Go to filemaker and open ODBC sources if you want to import the data. You might need to create a database structure first.

NB it doesn't matter if MSAccess is running or not. you can still pull the data.

If FMP is the source

1. Follow the same procedure as above, except you have to name the source with the name of your Filemaker database and the fp5 extension.

2.FMP HAS TO BE RUNNING to serve the data.

3. FMP is very slack with field names and types, not all of which are supported by ODBC.

Make sure you don't use empty spaces or symbols or punctuation marks.

Make sure the field names are not too long <10 characters. Otherwise it is a mess in other programs.

No repeating fields ..boo hiss...

The MSAccess client cannot handle more than 33 indexes, and the connection will be rejected. This is easily reached in a standard FMP database.

Changing indovidual records on the fly in Access from an FMP source is very easy, you just navigate to the record, and change the field value

By comparison, in FMP this type of operation is quite complicated because you have to create SQL statements and do a formal insert/update type statement, which involves calculated fields and scripts. This is done by MSAccess under the hood.

Overall, the ODBC support in FMP is not flash, but workable. If this is your main application, migrate to FMP, and vice versa. Having said that, it is generally easier to get things into than out of FMP