12 Replies Latest reply on Apr 13, 2014 4:27 PM by danielfarnan

    Import Access Data to FMP 13: Need Suggestions

    rmittelman

      I presently have an Access application to process student loan applications for multiple schools.

       

      In recommended Access fashion it uses data separation, so the "program" is an Access DB on each user's PC, and the "data" is stored in multiple Access DB's on a server. Each school has their own DB, containing all students, etc. for that school. When the users switch from one school to another, the linked tables in the front-end DB are re-linked to the tables in the database belonging to the proper school.

       

      When considering upgrading to a FileMaker Pro solution, I am thinking all school databases will be combined into one. In preperation for this, I've already added a School_ID field in all tables in all school databases. When we combine the individual DB's into one, this new field will keep data logically separated by school.

       

      Here is the real challenge, and the reason for this post: In addition to the school database, each school also has a Federal Student Aid Database (an Access MDB), which is supplied by the federal government. Each database is unique to the particular school, and contains student aid data for that school only. In my Access solution, when I change from one school to another, I re-link to the proper school DB tables, and also to the proper Federal DB tables. No problems at all.

       

      In the proposed FMP solution, I can't really combine Federal data from different schools in a single set of tables. The supplied Federal MDB's must remain unique and separate. I was hoping to use ESS, but that will not work with Access. Therefore, I'm stuck with some type of data importing. The Federal data will be read-only. I only need to import it occasionally.

       

      The problem is, different users may be processing different schools at the same time. Therefore, I can't just import the Federal data from school A in case somebody else is working with school B or C.

      I *think* the proper method would be proper to have an FMP Federal database file for each school, and script the import into the proper school's Federal FMP data database. Is this correct? Are there any suggestions about how to do this? I suppose when switching from one school to another, the script could test whether there is in fact a Federal FMP data file existing for that school, and if not, just create it. If so, I guess it would be proper to first delete all data and re-import all current new data, or simply delete the entire file and re-create it (but what about other users who may be "in" that school?).

       

      I could really use some suggestions for the proper way to handle this situation.

       

      Thanks in advance...

        • 1. Re: Import Access Data to FMP 13: Need Suggestions
          erolst

          rmittelman wrote:

          In preperation for this, I've already added a School_ID field in all tables in all school databases.  When we combine the individual DB's into one, this new field will keep data logically separated by school.

          Just thought I mention it: don't forget that you need a Schools table …

          rmittelman wrote:

          I can't really combine Federal data from different schools in a single set of tables.  The supplied Federal MDB's must remain unique and separate.

          Why can't you treat the Federal data like the other ones, i.e. import them into one table and distinguish them by a foreign schoolID field? I can understand that they must remain unique, but why separate?

          rmittelman wrote:

          In my Access solution, when I change from one school to another, I re-link to the proper school DB tables, and also to the proper Federal DB tables.  No problems at all.

          Which is exactly what you'd do in a FileMaker solution, exceopt that after having defined the relationships, this 're-link' happens automatically.

          • 2. Re: Import Access Data to FMP 13: Need Suggestions
            rrrichie

            In the past I have uploaded access data to a SQL server and retrieved that via ESS (via ODBC) or sometimes via SQL queries, using the "old" SQL (& ODBC) script step.

             

            That way you could upload the FederalData to the SQL server (add the school identifier) and build to FMP solution to retrieve it from one source.

             

            If memory serves me right you used to be able to import access databases on the pc with the pc filemaker version... But that's version 5 or 6 I think.

             

            The ActualTech ODBC driver can connect to Access databases, read-only :-)

            • 3. Re: Import Access Data to FMP 13: Need Suggestions
              rmittelman

              Thanks for the reply.

               

              Yes, I do have a school table.

               

              Good point, I could import the school A tables (there are 20+ tables in the MDB, but we only use about 4 of them) into into common tables with the school ID on each record. I would need to delete all records in each table for school A, then append the MDB records for those tables.  Not being familiar with importing, I don't know if that is easier than deleting the table and re-importing (which would require different tables - or different Federal files - for each school, of course). If it was different Federal files, then I would need to refresh something when I changed schools, right? At least point my application to the proper school file.

               

              I also have the issue of when to refresh (re-import) the data. Periodically we may receive new data from the feds. This comes in the form of a text file, which their separate application then imports into their MDB. I only need to do my FMP import if new data was received. I will inquire if there is anything in the Federal MDB that indicates the date it was refreshed.

               

              This would be SO much easier if I could just, er, access the Access MDB remotely read-only.

              • 4. Re: Import Access Data to FMP 13: Need Suggestions
                rmittelman

                Thanks, @rrrichie. That is a good suggestion, but in our situation it is not practical to have a SQL Server database.

                 

                I've installed the ActualTech ODBC driver, and when I tried to use the table(s) from my Access DB, I couldn't get that to work. Could you be more specific on how I would do this?

                 

                Thanks...

                • 5. Re: Import Access Data to FMP 13: Need Suggestions
                  taylorsharpe

                  Instructions for Actual ODBC driver connection go to Access files are at:  http://www.actualtech.com/product_access.php

                   

                  I really like the Actual drivers other than they cost money whereas most ODBC drivers on Windows are free. 

                  • 6. Re: Import Access Data to FMP 13: Need Suggestions
                    danielfarnan

                    Importing in FileMaker has the option to update matching records and append any new ones to the table. This would be quicker than deleting and reimporting, in addition to being less harmful to active users at the point of execution.

                    • 7. Re: Import Access Data to FMP 13: Need Suggestions
                      rmittelman

                      I'm sure you are right about this. I will need to evaluate the data in the federal tables and see if it lends itself to updating / adding as opposed to deleting and reimporting. Since it's federal government data, I need to be careful that the update would be flawless. New data brought down from the federal site is all-inclusive, I believe, meaning it is a replacement for the last set of data that was in the federal database.  Not too worried about the time to replace, as it happens only occasionally. More important is your point about stepping on other users. I believe the users have a convention of only one person working on a given school at a time. This is just a convention, but I could put in rules to enforce this, which would prevent any of those issues.

                       

                      Most important is architecture. Data separation is an obvious need. So one file for the application, and one for school data (all schools' data in one database), and then either one single file for federal data, or multiple files for each school's federal data. This is the big question. How much overhead is incurred when the users change schools?  If separate school federal files, then just delete the file and re-create. If single federal file, then must delete all records in all tables for the school that is being refreshed, then import from the new school's MDB.

                       

                      I'm new at this, but it seems a single file is much easier to work with in the relationship graph, using school ID to link tables with. If different files, I would need to figure out how to reset the relationships in script steps when user switches from one school to another. Don't even know if that's possible.

                       

                      Any further thoughts?  Thanks so much for the reply.

                      • 8. Re: Import Access Data to FMP 13: Need Suggestions
                        taylorsharpe

                        Just remember that if you separate things, then you are not going to as easily make comparisons because they will not be in the same table or file.  When possible, I avoid separating the school data and instead make sure I have a field that identifies which data goes to which school.  There is a time and place to separate things, but I more often see the mistake of separating when really what you wanted was to narrow a found set and after separating, you can't easily do searches across multiple schools anymore. 

                        • 9. Re: Import Access Data to FMP 13: Need Suggestions
                          rmittelman

                          Thanks @taylorsharpe. I appreciate the suggestions.

                           

                          To clarify, Data Separation as a general concept is desired, right? The program logic, layouts, scripts, etc. in the main file, and the data in a different file. Therefore, when I need to modify program logic or add features, I can do it on my schedule, then install at customer site, not needing to worry about losing the work they've done in the interim.

                           

                          Specifically, we were referring to separating schools. In the current Access solution, each school has their own school database and their own federal database. When a user switches from one school to another the front-end database has code to re-link the tables to the proper school databases.

                           

                          I agree with your point. In the proposed FMP solution, the school DBs wll be combined into a single DB, with each table having a School_ID field to keep the records grouped by school. Even though the business use never involves searches or reporting over multiple schools, it is still a good idea to combine the school databases for many reasons. Access has so many limitations, it made sense to segregate. Not so with FMP. And who knows whether the customer may someday want to run reports across multiple schools.

                           

                          My issue was the federal databases for each school. It's supplied by the feds. It is maintained by a Windows front-end program called EDExpress, and records are periodically downloaded from the government source as text files and imported using that program into their Access Database. So every time they do a data import into their student aid program, we would need to re-import the appropriate tables from the Access DB into the FMP file. I still need to research whether the data imported into the federal program is a total replacement or an update / insert of records, and whether I can simply update / insert into my FMP Fed Data file, or must replace all.

                           

                          But since ESS doesn't work with access, and since I'm forced to do an import into FMP each time that federal DB is refreshed, I have control over how to import, so I tend to agree with you that I should also combine the federal tables from each school into a single set of federal tables. Whether they should be in the data-separated SchoolData.fmp12 file also, or be in a separate FedData.fmp12 file is another question. They are logically different data, and if one file gets corrupted, the other may still be safe. What are your thoughts on this?

                           

                          Thanks again...

                          • 10. Re: Import Access Data to FMP 13: Need Suggestions
                            beverly

                            One more suggestion, just because it can be done...

                            Export data from Access as XML (use XSLT before export from Access or before import into FileMaker).

                             

                             

                             

                            Also, I've done many 'conversions' from Access to FileMaker and wanted to remind everyone that "value lists" , queries and "forms" aren't 'ported. I usually make screen shots and had Access open at the same time as FileMaker, so I could bounce back and forth to use these, if desired. I have not researched other methods to get this information, but surely VB script could be used.

                             

                            Beverly

                            • 12. Re: Import Access Data to FMP 13: Need Suggestions
                              danielfarnan

                              I don't see any practical drawbacks to keeping the federal data in a separate file; as you say you get some protection against file corruption taking out your entire data set. The tradeoff is likely to be performance (it's slower to get data into and out of multiple files) but from what you've said that's unlikely to be a significant impact.

                               

                              Data Separation as a general concept is useful but not always appropriate. Here, because you want the ability to work on the interface separately I think it's the right way to go.

                               

                              Data Normalisation as a general concept is also useful, but the practicalities of implementation often prevent the use of a more theoretically "pure" level of normalisation. Here, those of us commenting on your situation are recommending that you follow one of the basic rules of normalisation - all data that pertains to a group of identical entities should be held within a single table. Since the federal data for all of the schools is highly likely to have exactly the same fields no matter which school is involved, store it in a single table. Since each school is highly likely to store the same data about "students, etc" just store it in a single table.