14 Replies Latest reply on Aug 23, 2012 5:06 AM by fearless

    FileMaker newbie, Access vet

      Hi all, I'm getting very tired of my windows pc and am seriously thinking of switching to Mac. I am fairly sure my apps etc will have equivalents that I can quickly get to grips with apart from one important business app I have in Access 2007. I've built it myself and it is not object based. It has nearly 10,000 lines of code and 300 queries etc. I'm fine with data transfers into FileMaker but what about the code? Is there somewhere you can point me to see how the scripts are structured? Most important is the way in which data is loaded into memory, manipulated and then saved. I realise it is a different and no doubt improved mindset but right now I can't find much on the scripting. Thanks in advance!

        • 1. Re: FileMaker newbie, Access vet
          Stephen Huston

          You can download a demo version of FileMaker Pro (for Windows since you haven't yet made the change) and view its documentation and help files which cover all of the script steps. It even has sample files with existing scripts you can get into to analyse. If you move to the Make, you will need the FMP version for Mac, but all of the data files from the Windows version will still work in the Mac App!


          Back when I taught FileMaker for a training center, the Access instructer chose to sit in on one of my classes. He kept making comments about how "I wish it were that easy in Access."


          There is a learning curve to anything new, but FMP's is not bad. And you may find that some of the sample files provide a good head-start on what you need to recreate.


          Come on in...

          • 2. Re: FileMaker newbie, Access vet

            Stephen's right, but to answer fearless' question "I'm fine with data transfers into FileMaker but what about the code?"


            I've "converted" Access to FMP for many years now. I have not seen an easy way to move the code, value lists and queries from Access. I've just had both solutions up at the same time and bounced my eyes and thoughts between them.


            Now if someone has a plug-in or other means, I'd also be interested!


            • 3. Re: FileMaker newbie, Access vet

              Access does not work on a Mac unless you get an emulator like VM Ware or Parallels and run that on the Mac too.  But that does defeat the purpose of moving to a Mac. 


              Access is not a database back end, it is the "Access" user interface to real databases like MS SQL Server, MySQL and even FileMaker.  Access does allow you to store data in it, but Microsoft is clear that those tables and data are not multi-user aware and recommends multi-user systems to move the backend to MS SQL Server.  But if you're using Access to hold the data, you'll be very pleasantly surprised with FileMaker's ability to hold much larger amounts of data than Access as well as including multi-user support (e.g., record locking, etc.). 


              The backend tables and fields are easy to move from Access to FileMaker.  The code is completely not transferrable.  You have to rewrite all the scripts, layouts, relationships, etc.  This is not trivial.  I think FileMaker is a better product for rapid development of a good looking product.  But there are some strengths that Access has including the Visual Basic programming language.  FileMaker has pretty good database scripting (quasi java like), but it certainly is not a full programming language.  On the other hand, the FileMaker scripting is MUCH easier to learn the Visual Basic. 


              I like FileMaker's simplicity and flexibility better than Access, but I also know that throwing away a bunch of tried and working Access code is a tough thing to do and will reworking in FileMaker.  Granted it will be a lot simpler than doing something like converting it to PHP, but it still will involve a lot. 


              You could move the data to FileMaker Server and modify your Access code to read the data from FileMaker via ODBC.  That would not take much work and things could continue in Access until you have time to gradually transition them to FileMaker's User Interface and scripting. 


              Basically on the Mac, database choices are limited at the User Interface level unless you get into PHP or a programming language.  I personally swear by FileMaker because it provides the tools for the most rapid solution I can provide most customers, which keeps things inexpensive.  There are other databases on the Mac including MySQL, Postgres, 4D, the latter of which also provides a User Interface like FileMaker.  4D is a great database, but it clearly is more complicated than FileMaker and really is more comparable to the Access/MS SQL Server tool set. 


              What are you goals with the database? Will it be a on a server?  Will it be single user or multi-user? 

              1 of 1 people found this helpful
              • 4. Re: FileMaker newbie, Access vet

                Take a look at this product http://www.fmpromigrator.com/products/fmpro_migrator/index.html I have not used it but it sounds like it may work for you or you could look at 4D it's the closest thing to what you are use to and has a full text based (although proprietary) programming language and is fully SQL compatible as well. The other option is just to run it under Windows on a VM and not bother with converting  it. If that is the only thing you would be running Windows for it would probably work fine.

                1 of 1 people found this helpful
                • 5. Re: FileMaker newbie, Access vet

                  I'd suggest looking at it from a slightly different point of view (pov).


                  1.     What exactly does the Access db do?

                  2.     How many users does it have?


                  It might have a whole lot of code, but what are the key, really useful things that the code does?


                  Once the actual functionality that's required is identified, then you can look for the 'FMP answer', rather than trying to translate it into 'Access'.  I've spent a bit of time lately doing Access replacements and my personal take on it is that it's easier to interrogate the functionality and then apply the best FMP method to either recreate it or extend it.



                  Mardi Kennedy

                  • 6. Re: FileMaker newbie, Access vet

                    A big thank you to all who replied.  So much more helpful than MS forums!  The ultimate goal of this is to run my app on my iPad/iPhone so I am truely mobile.  In order to sync data I may well require a server setup (not looked this far ahead yet).  There is only myself using the db although it would be nice to think that one day I would have someone to help out!  Running windows in VM would be a quick win for my desk setup and I suppose I could take it with me on a laptop.  Hmmm, more thought required.  Anyway I shall certainly look further into 4D, but either route sounds like a massive re-write of code etc.  As for the functionality of the code and queries, there is very little glossy stuff - most is data manipulation.  I am a financial adviser and this system is my complete backoffice for my clients' portfolios.  It does everything from basic CFM to CGT.  Once again many thanks for pointers.  Onwards and upwards!

                    • 7. Re: FileMaker newbie, Access vet

                      I hear there is also Mac Office which will run MS office progams too... Definately on to 4D investigation, many thanks for pointer.

                      • 8. Re: FileMaker newbie, Access vet

                        MS Office has been around a long time on the Mac.  Technically, MS Office came out on the Mac even before it did on Windows, back when they charged like $900 for it.  Generally it has worked the same way on the Mac as Windows except there were a few years that the Visual Basic component didn't work on the Mac side, much to the frustration of a few Excel users. 


                        4D is a great and stable program, but it really is a level of complexity beyond FileMaker.  And getting it to make elegant reports and UI screens on an iPad will be quite a bit harder to do than in FileMaker since FileMaker Go works pretty much without modification other than realizing the pointer (finger) is different than on a computer.  Plus Apple has done a really great job with the iPads User Interface andn FileMaker Go on the iPad is very impressive. 


                        FileMaker also works on Windows in case you ever needed to send your file to a Windows User or, better yet, you bought FileMaker Server and multiple Mac and Windows Users wanted to connect at the same time.


                        One last convenience of FileMaker Server is that it does a great job of handling security including its AES 256 bit SSL cipher that can be turned on by a single checkbox as compared to most systems that you have to set up certificates and other complicated procedures.  And while on security, you can control security down to the field level on FileMaker and only down to the table level on Access.  Just something to think about if you get into multi-user access and you want to control various levels of access. 

                        • 9. Re: FileMaker newbie, Access vet

                          Many thanks for more info.  Looks like FMP will be best on iPad etc but I will have to see if the coding/scripting is up to the level I need.  Final solution for now is to take a week off then take a trial of FMP on my return.  Had a quick glance at 4D and found the coding odd in places, like naming a declared variable twice so it is a word and not a number!  I'm sure it is just something one gets used to but it gets comlex very quickly and the GUI does not seem 21st century.  If time allows I will report my findings for other migrating souls!

                          • 10. Re: FileMaker newbie, Access vet

                            If you are set on using an iPad for your mobile solution then FileMaker may be the best choice since it has a free native client for the iPad. If however you were to use a macbook air instead then that advantage is negated and you'll have to decide if FileMaker has the cababilities you need. Since you already know how to code in a text based language you may find FileMaker's scripting too restrictive for your tastes. Since FileMaker is more like a spreadsheet on steriods if your solution requires a lot of math all of that overhead is carried in the table structure where in 4D it's not and you can compile your solution in 4D so it's blazing fast if that's important to you.  You'll just have to decide which program will ultimatlely take you where you want to go. Good luck with your investigation.

                            • 11. Re: FileMaker newbie, Access vet

                              It makes for an interesting discussion, but of course you're actually in a FileMaker forum which isn't exactly a neutral place to discuss FileMaker alternatives.  FileMaker strengths are a much easier development and cohesion between UI and the back end.  But as most developers know, when you get big and complex (particularly with high simultanous transactions and large # of clients), SQL databases are generally better and faster than a FileMaker database.  4D falls in this category and it is a good product, but the learning curve is much greater and development is slower.


                              As a FileMaker developer, my target audience are businesses that need a custom but agile database capable of on-the-fly rapid development with minimized development cost.  Other database needs include extremely large or complex database, databases with very high transaction levels, those that require a primarily browser based UI, Big Data or No SQL databases, and other special needs that are not where FileMaker has its strengths. In dealing with potential clients, I talk with them about their needs and look to see where FileMaker is the best solution for them.  But that is not always the case.  There is no database software that is the best under all situations.  FileMaker has a great little niche and I enjoy working with it. 


                              I will say that in my experiences, I find a lot of small to medium sized businesses that have been talked into very expensive SQL database solution that could have been developed much less expensively and rapidly in FileMaker. Many have never even heard of FileMaker.  My goal is to find business needs where FileMaker is a good fit that benefits the bottom line of the business.  Often it is FileMaker, but not always and it is good to understand when it is best not to recommend FileMaker. 

                              • 12. Re: FileMaker newbie, Access vet

                                I appreciate your perspective on the issue and yes there can be do doubt there isn't a better database program you can built solutions in faster then in FileMaker. That being said  lot  of my development has gone beyond what FileMaker can really handle effectively and while it gives you good tools to build solutions quickly it gives almost none to manage them which is why I am moving on to 4D for my more complex and verical market solutions. FileMaker will always be an end user focused application which is a double edged sword and 12 only reinforces this approach. This is their niche and they excel at it.


                                Since the gentlemen that started this thread clearly already knows how to code and is used to traditional queries and so forth he made find the restrictions in FileMaker hard to live with but only he can say that for sure.


                                I agree with you many companies are talked into very expensive solutions they don't usually need that FileMaker could handle very well. On the other hand many solutions that started small and quickly using FileMaker can easily grow beyond FileMaker's ability to properly manage and support them as has happened with my customers in a number of cases. I agree with you it's always best to talk with potential clients to learn what tool will be best for their needs but this can be difficult to do in the early stages.


                                Thanks again for your input.

                                • 13. Re: FileMaker newbie, Access vet

                                  Thank you everyone for your input. I can't wait until the end of the hols so I can get out FMP.  Sad really but then it is raining!  However as you have eluded to it may not be up to it on the coding front.  We shall see and I will let you know...

                                  • 14. Re: FileMaker newbie, Access vet

                                    Yes, I completely agree!  Functions first, then try and replicate in the language of the beast you are developing in.  I still have not had time to look at the software since my holiday due to market excitment but it is on my list.  It may never happen because it is such a big project and I may cheat at first by running Access on the Mac.  I am the only person who uses it anyway.  As for "what does it do" - everything I need, and growing all the time, with regards to my business (Investment Adviser).  Any deadwood is cut away quickly.  There is little data but complete functions.  Thanks to all for your input, it is good to know that the community is welcoming!