OK, the ODBC stuff doesn't work.

[RickDavis]

I need a way to read data from a SQL Server Database and then write/update a Filemaker Pro 5.5 database. I have been trying to get VB to work with the FM databases, but it is clear that it doesn't work.

Is it possible to write FM scripts that will read an external data source (i.e. the SQL Server database) and perform the updates that way?

[JerrySalem]

Rick,

It does work.

The main thing to know is that FM is not a 'front end' to the DB. What happens is that the SQL database is mirrored in the FM databse.

Here is how I implement a FM database against a SQL database;

I have a 'help desk' solution that tracks calls made to clients. The demographic info (address phone number etc) comes from our SQL database.

The first user in the morning triggers a syncronize scrip, that does an OBDC import. The querry is a calc field that finds all the active customers. FM then imports this result, updating records based on the customerid and adding new records.

I now have effectively replaced all the demographic info in the FM db with that of the SQL db (based on the id field).

If a user needs to update the SQL db, they change the field on the FM db, press a button that calls an 'exec SQL' script step. The sql is again a calc that does and update command.

I hope this helps.

Jerry

[RickDavis]

Here is the situation that I am working with. There will be a SQL database that will be updated through out the day with data from a field collection system. Once an hour, the newly collected data will need to update the FM system. The FM databases are also password protected.

What I wanted to do, was to write a VB program that will query the SQL Server and then update the FM system.

The reason that this will not work, is that the FM database is requiring a password be typed into a box.

Your solution that you presented is a workaround that should work. I will need to have this run unattended through. Where can I find docs on how to create a connection to an external data source from with an FM session?

[Vaughan]

Does it need to run unattended?

If nobody is using the FMP database then it does not matter if the data is out of date. The minute somebody opens the system they trigger a script to do the update before they get to see the data.

[RickDavis]

It could work that way. I am not familar with FMP, so I don't if running this unattended would be possible or not.

If all I have to do is to open a FileMaker Pro file to run the system, then I could use some sort of scheduling software open the filemaker pro file and pull the data.

[Vaughan]

Or the very act of a user opening the databsae performs the update.

[JerrySalem]

The only problem with updating on open is that in a database of any size it can take a while.

For instance, my system has about 5000 records. In order to redownload the data from the SQL datasource this can take as long as 5 minutes!

For this reason, I have the last person out the door at night as part of the end of day routine sync with SQL.