3 Replies Latest reply on Aug 30, 2010 10:06 AM by philmodjunk

    Converting Access 97 - 2003 to FM Pro Questions

    dnorth12

      Title

      Converting Access 97 - 2003 to FM Pro Questions

      Post

      I don't know where to begin, so maybe the best place to start is with the ultimate question. What can't be done in FM Pro that can be done in Access?

      Currently running an Access 97 db on a peer to peer network in one location with 3 users and in another location running Access 97 thru 2007 as a front end to SQL server with about 20 users. I am in the first location.

      We run a custom made Access program for invoicing, quoting, accounts payable, email notifications for orders receipt and orders shipped (which includes importing tracking numbers drawn from UPS world ship related to a specific order). There are thousands of customers with thousands of invoices.

      There are a multitude of options that can be applied to any item ordered and the problem is the options keep growing forcing the need for more fields on invoice and quote entry forms. This has caused us to run out field space in Access, which ultimately leads to corrupted programs due to out of memory errors. Subforms don't seem to alleviate the problem, due to the need for larger queries that support the forms. That is one major issue that I believe can be overcome, by moving to FM Pro, with its 8 gb capacity as opposed to Access's 2 gb capacity.

      The main bridge to cross is all the VBA code that would have to be rewritten in FM Pro. I know that no one can answer specifically without seeing the Access version is use, but generally what kind of things cause major issues when trying to convert to FM?

      We have code that calls out Outlook to send an email confirmation for each customer that has sent an order or had one shipped for that business day.

      We have code that calls out Outlook to send an email of an invoice or quote report that has been converted to a pdf using ghostscript

      Aging and monthly statements (some converted to pdf's for emailing) for all customers are things that will need to be addressed.

      There are other things to consider, but the for the purpose of this discussion, what is above, is enough for now.

      I would appreciate any thoughts any one would care to share.

        • 1. Re: Converting Access 97 - 2003 to FM Pro Questions
          philmodjunk

          I suggest downloading the free 30 day trial copy of FileMaker and giving it a test run. That will answer many questions for you that we can't without knowing more about your database.

          The biggest difference you'll see is that FileMaker doesn't use SQL to query its own tables nor to define relationships between them. Instead, it uses a graphic interface to define relationships and a kind of "query by example" approach to querying that makes simple user initiated queries extremely easy to support, but which can make more complex types of queries a challenge--sometimes it takes a scripted implementation to do it.

          FileMaker doesn't have a cross tab query wizard.

          On the other hand, FileMaker can generate PDF files and send Email without third party software. It also handles record locking much better than does Access.

          If you decide to buy, get FileMaker Advanced for development use. It has a script debugger similar to the one that comes standard in Access as well as other useful tools.

          Your other options might be to upgrade to the latest version of Access. '97 is a very old version. If the latest version of Access would meet your needs, that option would save you many hours of development effort recreating the database in FileMaker.

          • 2. Re: Converting Access 97 - 2003 to FM Pro Questions
            dnorth12

            Phil,

            Thank you for the response. Another reason I want to move to FM is the cross platform capability, which leaves Access in the rear view mirror. When push comes to shove, in the end I may be stuck with a newer version of Access.

            But I still am bumping up against the maximum number of fields in a query and out of memory issues on forms that need editing when they are close to maxed out. It will actually corrupt the front end Access programs, which can't be recovered. Thank God for backups.

            Have tried several attempts to get the link for the trial, but am not receiving the email from FM, although that is not an issue for this forum.

            I do have version 8.5 which will allow me to upgrade until the 23rd of Sept and I may just go that route.

            Another question: FM has the capability to create stand alone db's for people who don't have FM. Access has the same capability. In FM can these be created in such a way to connect to a server based db as remote work stations? Or does connecting to a server based FM db require a full blown installation of FM with license for each remote work station?

            • 3. Re: Converting Access 97 - 2003 to FM Pro Questions
              philmodjunk

              You need a license for each client unless you want to try web publishing the database so that users use their web browser to access the DB. Web published databases have interface limitations that you don't have if you use Filemaker Pro clients. (There's no need to put FileMaker Pro advanced on your client stations--you'd just use that on your machine for development use.)

              One nice thing about FileMaker is that you can expand your support for more users without having to convert your database over to a different system like you do with Access.

              You can host your files with a copy of FileMaker Pro and support up to 9 users with FileMaker Clients or 5 simultaneous web browser clients if you publish it to the web. You can then put the same file on a server and host it with Filemaker Server to support 100's of users and rarely do you need to make any changes to your file to do the upgrade.

              If all else fails on the 30 day download, you might try contacting customer support by phone and see if they can help you out. (Also make sure your Junk mail filter isn't filtering out the download email.)