I have an existing MS Access application which administers federal financial aid for various schools. I would like to port the application to FileMaker Pro.
The existing application uses several back-end databases, one for each school. When the user switches schools, the program code causes the table links to refresh and point to the proper back-end database for the school selected.
The proper method of porting this application to FMP would be (I believe) to combine all individual school databases into one, while adding a column for SchoolID, therefore eliminating the need for multiple back-end databases. Is this correct? Please advise best practice for this situation.
Regardless of above answer, there is a larger problem here. When students apply for financial aid, their information is processed by the federal government and supplied back to us in the form of an MS Access database (MDB type). This database contains records for each student in the school who applied for financial aid. Since we administer several schools, we need to be able to access data from several different MDB's, but only one at a time. The particular MDB is linked to when we select a different school, by program code. Since this particular data is read-only, it seems like ESS is the perfect solution for us. Unfortunately, this does not appear to work with an MS Access database.
All records in the federal database tables seem to be keyed by a combination of SSN and name, so they *should* be unique between different schools, therefore allowing combining records from several individual MDBs into FMP. But I stressed the "should" because we can't guarantee this. A student may have moved to another school, and be present in multiple of these MDBs.
Since FMP does allow importing MS Access tables, I suppose we could script the import of the 3-4 tables we need when we change schools. Never having worked with importing ODBC tables, I'm not sure if we need to first delete the current imported tables before importing the new tables.
Does anybody have a suggestion for how to solve this particular issue?
Thanks in advance...