ODBC or scripted export/import?

[MHAriete64]

I have a complicated question for the list, but I hope someone can help.

My project is to build a database for a department within a small community college. My database is to be accessible to about 7 clients using the school’s intranet, so I had them buy Filemaker Server 8 (rather than Advance, to save $$) to host the Database on a Windows 2000 Server. I’ve designed the DB to stand alone and was promised access to the larger school’s student database for data. I have about 20 fields of data that I want updated from the student database on a semi-regular bases (6-8 times a year) (like classes taken, grades, GPA, etc).

The MIS manager and I have been discussing this for a bit and one solution we have is to interact files through scripts on a mapped directory. I’m planning to build a script to export a list of student ID numbers for the students I need info on and save it to a mapped directory on the network. They will build a script that looks for this new file, uses the information in the file to extract information from their student database and save it back to the mapped directory. I will then build a script to import that information into my Tables.

This solution sounds rife with potential problems.

Has anyone had experience with this type of data flow and are there recommendations on how to make this work?

My research says that it might be worth the investment to make the upgrade to Filemaker Server 8 Advanced to get the ODBC ability (hence my posting to this list vs. scripting) and interface directly with their student database.

Any insights would be greatly appreciated.

Thanks.

[jeffmcg]

You don't need advanced to use odbc.

I installed an odbc driver that lets me make SQL queries into our mySQL database.

It is the easiest way to import records of all.

[fmsavey]

If your school's student database is odbc compliant you could define a data source that points to that database and then using "import using odbc data source" write a select statement to import the data you want into FM. You could also insert, modify or delete data in the schools student database using fm's execute sql function.

I think you can connect to server 8 via odbc but you are limited to 5 simultaneous connections. I'm am not certain about this however.

Edited April 26, 2007 by Guest

[MHAriete64]

Thanks for your input. I'm trying to get them to allow me to use MiniSoft's ODBC driver to work with their HP3000 Image.

Any tutorials you can recommend so I can get up to speed on call/response language for ODBCs?