• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3070
  • Last Modified:

FileMaker Pro Client / Front End and SQL Server Backend

Hi,

   I've been developing on MS Access for quite some time now, and recently I have been asked to do a few things with FileMaker Pro.

With MS Access, I've been using Access as the front end client (with all the business logic), and MS SQL Server Database as the back end database / data repository.  This was achieved by 'linking' the SQL Server tables via ODBC (system / user DNS) - this integration ability is seamless and logical, especially for scalable deployment.

As I'm hoping to duplicate this deployment model for FileMaker Pro - i.e. use FileMaker Pro as the client end, and SQL Server as the back, I was wondering whether there is this facility to 'link' a remote table and have it display as a local table, like MS Access.

I've done some investigations, and it seems that Filemaker, through ODBC, requires scripts to perform SQL statements (to be defined by the developer) on tables at the remote end - i.e. the logical nature of displaying remote tables as though they are local is just not there.

is this true? or can remote tables be treated as through they're local like MS Access?

Any pointers to the right direction?

Many thanks,

Jenny.

0
jenny_kong
Asked:
jenny_kong
  • 3
  • 2
  • 2
2 Solutions
 
lesouefCommented:
unfortunately not.
every bit you wanna work on must be imported first into a filemaker table.
so basically you need to re-create the same data structure in filemaker first.
On the other way around, you can write to a foreign database using SQL queries.
Why do you have to tale filemake for that?
0
 
jenny_kongAuthor Commented:
I have Access and FileMaker Pro databases at work.  I'm hoping to retain the user interfaces (access and filemaker pro) and consolidate the tables into a single database for data sharing.

1) If everything needs to be imported into a Filemaker table beforehand, how does it refresh the data at the backend, say SQL Server?  Do i need to import the data by ODBC EACH and EVERY time I want updated information from the backend? i.e. user interaction is required each time (or by script)?

or do you mean that it is just not possible to link the data up in real time?  By this, I mean browse records on FileMaker Pro from SQL Server.

2) If a foreign database is used - isn't that the same as writing to SQL Server?

Many thanks,

Jenny







0
 
lesouefCommented:

1) If everything needs to be imported into a Filemaker table beforehand, how does it refresh the data at the backend, say SQL Server?

 Do i need to import the data by ODBC EACH and EVERY time I want updated information from the backend?
--------- yes, at least records modified since last import, which implies a field to keep a record modification timestamp.

 i.e. user interaction is required each time (or by script)?
--------- yes

or do you mean that it is just not possible to link the data up in real time?  By this, I mean browse records on FileMaker Pro from SQL Server.
------------
I do mean no real time

2) If a foreign database is used - isn't that the same as writing to SQL Server?
-------------
does not use a foreign database, but imports records from it, and can write them back.

I know you are disappointed.
What else to say? my personnal advice would be to choose something else than filemaker, and as I am also a servoy user, I can only suggest to use it, it would the perfect tool, as it can work real time on 10 different kind of foreign databases wihtout ODBC. http://www.servoy.com
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!

 
billmercerCommented:
Just to chime in with a few more points...

>i.e. the logical nature of displaying remote tables as though they are local is just not there.
Yep, and there's a reason. FileMaker is not a SQL-based system. It doesn't even use anything remotely like SQL. It uses its own, proprietary file format, with its own data types.

Since you already have Access experience, you might want to just consider re-working the FileMaker database in Access, rather than learning a new and quite different system. Unless you need cross-platform compatibility, which of course Access cannot do. Lesouef mentioned Servoy. There's also another cross-platform option, 4th Dimension.
  http://www.4d.com/products/4thdimension.html 

>I have Access and FileMaker Pro databases at work.  I'm hoping to retain the user
>interfaces (access and filemaker pro) and consolidate the tables into a single database
>for data sharing.

Actually, you could do this, just not with a SQL back end. If you use FileMaker as the back-end database, and rework your Access front-end to talk to it via ODBC instead of to SQL Server, then you could keep the FileMaker interface as is. That might not be practical for you, but it's worth mentioning at least.
0
 
jenny_kongAuthor Commented:
thanks guys for the comfirmation.

Yes Lesouef, i'm quite disappointed about the inability of FMP to connect in real time to remote sources - Especially as a lot of banter i read from various sources go on about the supposed 'excellent' choice of using FMP as the client with the backend being an ODBC datasource.

But anyway, thanks for the confirmation - which i wanted, as I've played around with it already, yet wasn't absolutely certain.

Thanks also Bill Mercer(?) for your comments.  Yes, the reason indeed is that there are many forms (user interfaces) within FMP that I didn't want to redo on Access.
 
but one final question - If i indeed use FMP as the backend, will Access see it (tables) in realtime like it does with SQL Server? (linked tables?).

If you don't know, that's OK - I'll experiment in the Office when I return.

Jenny
0
 
billmercerCommented:
>If i indeed use FMP as the backend, will Access see it (tables) in realtime like it does with SQL Server?
Yes, with some caveats.
You'll need to turn on ODBC sharing for the FileMaker database, which is off by default, and you'll need to install the ODBC/JDBC drivers on the client machine, which are a separate install on the FileMaker installation CD.
You will need a separate DSN for each FileMaker file.
Things you'll need to look out for:
1. FileMaker allows field and table names that are illegal in ODBC and Access. Experienced FM developers generally avoid this, but it's perfectly legal to have a FileMaker field called "This is my field name/isn't it great & wonderful?"
2. FileMaker data types are not comparable to Access/SQL ones, and you will need to take this into account.
   a) There is no integer, long int, float, double, etc. FileMaker simply has Number fields, which
   b) There's no separate boolean type, just use numbers or Y/N text fields instead.
   c) There are no fixed-width text/character fields. All FileMaker text fields are sort of like MEMO fields.
   d) Date/time fields. FileMaker has separate Date and Time field types, and the latest versions have timestamps
3. FileMaker database developers have different naming conventions than Access developers, and this may cause confusion, especially if Access tries to create relationships based on field names.
4. Since FileMaker isn't natively SQL, the ODBC drivers must translate stuff

Note, you can't do a File DSN with FileMaker like you can with an Access MDB, so in order to access the database via ODBC, it must be up and running, either on a client workstation, or on FileMaker Server Advanced (not regular FM Server, only Server Advanced allows ODBC.)

If only a handful of users will be accessing the FileMaker data, you can easily do this simply by hosting the Filemaker data on a client computer running the FileMaker application. But if it's going to be used more heavily, you'd need FileMaker Server Advanced, which gets expensive.

>a lot of banter i read from various sources go on about the supposed 'excellent' choice of using FMP as
>the client with the backend being an ODBC datasource.

When your favorite tool is a hammer, everything looks like a nail.

FileMaker is an excellent choice for creating small and medium workgroup databases where speed of development and fast, flexible modification is desireable. It's the best platform out there for rapidly creating cross-platform databases that will run on both Mac and Windows, especially for less technically savvy users.

But as a front-end for a SQL-based back-end database, FileMaker sucks rocks. There are third-party plugins available for FileMaker that make it handle SQL better, but we're talking about going from rocks to slightly smaller rocks.

It's a shame in some ways, because FileMaker COULD be an awesome front-end for SQL databases, if only it would talk to them.
0
 
jenny_kongAuthor Commented:
Thanks Bill!  Your explanation is greatly appreciated - many thanks once again.  I'll see if i can recommend moving on to Access in this case.  

<snip>
When your favorite tool is a hammer, everything looks like a nail.
</snip>

Ha! - Very well said.

Jenny.


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!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now