How does FM7 compare to MS Access

[skip314]

All of my db experience is with MS Access (a number of years ago). A few weeks ago I downloaded the FM7 demo and I'm trying to decide if it's worth the price. My initial reaction is that FM is lame compared to MS Access but there's a good chance I'm just not familiar enough with FM. Of course, I'm a Mac-only guy now so Access isn't an option.

Is FM as powerful as Access? Has anyone else made the transition with success/satisfaction?

[KurtW]

Hi there:

I have done more work in Access (on the web) than FM, though I started years ago in FM. If you are programming in Access (VBA, modules) than you are advanced enough that you may find the transition to FM a bit rough as it operates on a different 'paradigm' for lack of a better term.

I am having trouble migrating JavaScript, VBS, SQL, ASP techniques to the desktop, but have always liked FM. I recall, when I started, that FM is very, very stable and will run well even when overtaxed. It had decent graphical capabilities and has been a standard for Mac users for years.

It probably comes down to you and how/if you are interested in making the though-shift necessary.

Kurt

[hayesk]

FM is more of a "designer's" database than a programmers database. FM has many powerful capabilities, that are often overlooked by Access developers. Most things done in Access are done *a lot* differently in FileMaker. If you have patience and can acknowledge that FM works very differently, then you will find FM is a very nice tool.

If you believe you absolutely need to make database solutions programmatically, then 4D is probably what you are looking for. It is cross platform and has a programming language.

[Kurt Knippel]

I started developing in database in Clipper and VB with some FoxPro, Paradox and Lotus notes work as well so I was used to developing programs. I switched to FM in 1995 during the time of FMP 2.1, while it was different it is just as powerful and far easier to use and develop in. If you are looking for a "programming language" with some database capabilities, then FileMaker is NOT for you, but if you are a database developer, then FMP is the tool to use for all small-mid sized workgroups (less than 250 users).

Filemaker allows the database developer to concentrate on developing the database and not futzing around with programming every little function and procedure. That said the scripting in FileMaker (more like macros) is robust and detailled enough that it contains all the basic control elemnets that you need and it is extensible via a number fo plug-in that are available.

[Crag]

Filemaker is powerful. But for developers you'd do better writing Foxpro or if you need cross-platform, RealBasic. FM is expensive... you can't compile (runtime) any Filemaker app you developer for multi-user. In other words, if whoever buys your product has more then one computer, they must buy a copy of Filemaker per seat. Not good.

The Filemaker development environment is YEARS behind any other tool I've seen. FM has just now (with the latest version) added visual representation of relationships. Jesus... Access has been doing that for years. And that's just one example.

if you have users who are tech savvy but not database savvy then Filemaker is a good solution right out of the box. And you, as a developer will have to find a place to fit in that relationship. Be the Filemaker expert sort of. But I gotta tell you, Filemaker doesn't work like any other database.

From a developer point of view, compared against Access Filemaker is much more powerful and can handle a lot more then Access ever could. But compared against MySQl or FoxPro or even the data engine built into RealBasic Filemaker has a lot of catching up to you. This is assuming they even want to go that direction. Filemaker has been doing very well in this little niche market it's created. So who knows...

I use Filemaker (a newbie - only about 3 months) cause I got handed a huge database app that was written in FM years ago. Told to rewrite it in Filemaker 7.0 cause the managers know how to use Filemaker itself. This is a good example of where Filemaker fits nicely.

[KurtW]

Crag:

Yeah, one thing Access does have going for it is that EVERYONE (at least on PC) has a copy running. It is the cockroach of the database world!

You can actually develop pretty deeply in Access, it just has a nasty, nasty learning curve. (Though going back from web to FM is proving a bit more difficult than I guessed...)

My first database job, back in 1996, I was asked to develop in Access, but never managed it in the short time I had there. Wound up using FM and setting it up graphically to LOOK like Access. And they didn't know.

Kurt

[Crag]

Coming from a SQL backend and Foxpro environments (and VB & RB) I've had my share of problems getting used to Filemakers way of doing things.

The Finds and Sort cause the first headache. After i figured out I could script a Find that worked out well... but sorting is handled so oddly in Filemaker. I still can't believe I just cant say "sort AccountName, aAaccountNumber order Accountname" or something like that. Having to perform the sort and then create the script is CRAZY.

Also, the fact that there is no separation from the Layouts to the data is VERY odd. A whole bunch of other things.. but I've gotten used to it.

But i gotta say, because of the horrible scripting interface (only allowed to open one script window at a time, etc, etc, ) it takes me MUCh longer to do anything in Filemaker then in FoxPro.

What a lot of Filemaker people don't get is "reusable code" (or objects and classes). Most programmers can throw a project together in hours cause we reuse code. You can do something like it in Filemaker.. but it's not as versatile as code.

But Filemakers has some things I like.... the calculation fields are VERY nice. But the fact that I can't trigger en event from a field is bad. So it's a love/hate relationship for now.

[H.P.]

I was in the same situation as you. My manager has previous experiences in filemaker and wants the project to be in filemaker. After this project, I don't think I will ever use filemaker ever again Other than lacks of programming functionalities & SQL, it costs too much over all comparing to other solutions.

You can use the free Hidden plug-in from the website below for triggering event:

http://www.databasepros.com/FMPro?-db=re...d=&-Skip=10

[hayesk]

The Finds and Sort cause the first headache. After i figured out I could script a Find that worked out well... but sorting is handled so oddly in Filemaker. I still can't believe I just cant say "sort AccountName, aAaccountNumber order Accountname" or something like that. Having to perform the sort and then create the script is CRAZY.

Crag, that limitation is gone in FM7. In the script step you can define the sort fields.

Also, the fact that there is no separation from the Layouts to the data is VERY odd. A whole bunch of other things.. but I've gotten used to it.

Again, limitation is gone from FM7. You can have separate interface and data files if you wish. You aren't forced to, but the option is there.

But Filemakers has some things I like.... the calculation fields are VERY nice. But the fact that I can't trigger en event from a field is bad. So it's a love/hate relationship for now.

FM7 Dev comes with a plug-in that allows you to run a script upon exiting (or entering) a field. This plug-in is also downloadable from databasepros.com

[Crag]

You can? How? How do i create a Layout with fields without the underlying table fields?

[Fenton]

The "underlying table fields" exist (of course), but they can be in another file/table. What does exist in the interface file is a Table Occurrence of that other file's table. And, for the TO to be created, you need a File Reference to the other file. The latter were hidden in previous versions, but are now visible and editable.

Once you have a valid File Reference and TO, you can create a Layout based on that TO. It's then pretty much the same as the original file's table, list view, Sort, Find, whatever.

If you create relationships to or from that TO, it's the same as creating relationships in the original file's table (while your in this interface table). In fact, File References and Table Occurrences effectively determine what a table is, and what records of that table are available, as far as most operations go.* The actual file on disk is just that, you are no longer tied to it for your interface.

*Obviously a step like "Save a Copy As" applies to the file on disk, TO's have nothing to do with that.

[DanBrill]

I don't consider myself a developer or even a database guy, but rather a subject matter expert who happened to be pretty good with a computer. My project was to create an all-in-one database for a very complex business model. I worked at it for almost 6 months in Access, got incredibly frustrated, and threw the whole thing out. It wasn't that I couldn't get VB scripting -- that wasn't too bad. But I found that it took soooo much of it to accomplish some pretty basic tasks. Moreover, as my forms got more complex and had a fair number of controls on them -- combo boxes, radio buttons, check boxes, etc. -- I found that the program got to be incredibly slow.

So I looked around for options and came up with FM. I couldn't be happier. It had all that I needed from Access, but I could do it faster and easier. It also runs faster in my experience, and it was (relatively) easy to create an xml interface with QuickBooks (and other xml programs).

95 times out of 100 I'll take FM over Access. Here are a few things I like better about Access:

- Value Lists and Drop Down menus are easier to work with and easier for end users. Moreover, as of FM7v2 there is a bug in the way these work making them a real pain.

- SQL queries

- Better event triggers. (Before FM 7 I would have said any event triggers. Now there is a free plugin for some limited ability.)

- Real Tab control is faster to develop than the way you have to fake it in FM

- Cut and paste code. Can't cut and paste between scripts in FM. Crag is right on this one for sure....

Hope this helps,

Dan

[KurtW]

I've been wondering that myself. That layout/data bond has me a bit perplexed, but I'm starting to feel my way around it.

Also, I searched DatabasePros and couldn't find that plugin that allowed for events. If anyone has the URL I'd love to get it. I keep wanting to script events but just have to work around that.

Kurt

[DanBrill]

Kurt,

Here is the link. I love this plug in.

PC

http://www.filemakerpros.com/PLUGINEX.zip

Mac

http://www.filemakerpros.com/PLUGINEX.sit

[DanBrill]

I guess my question is, why does it matter so much? The TO that it's based on is just the point of view that you are coming at the data from. But any related data or unrelated global can be put on the layout. So why would you want a layout that is divorced from a table?

[KurtW]

Thanks for the links, Dan.

Why does it matter? Just a paradigm-shift thing. In the long run it doesn't matter to the software and its functionality, it's MY functionality that's in jeopardy!

It's just odd to make the logic shift. Unlike some of the other folks, I'm not coming from application development, but from a few years of web development.

Though I started with FM many years ago, I have grown to love the sometimes messy, sometimes chaotic but very open methods of building in a scripting environment.

But I suppose mastering change is a vital part of life so I'm looking at this as more of a personal challenge. It's the deadlines that turn those challenges in headaches!

Thanks again

Kurt

[Fenton]

http://homepage.mac.com/jkornhaus/.cv/jkornhaus/Public/zippScript11.zip-link.zip

This is a link to an enhanced version of the plug-in, which accepts script parameters.

[KurtW]

Thanks Fenton:

I DL'd the last one but it has no docs so it's a bit opaque. I'll check out this one.

Kurt

[Crag]

Well I'll admit, I hate Access. I could list tons of faults Access has starting with using it online. Big mistake that many people do.

FM is fine. Takes some getting used too. My problems with it are that it takes too much time to develop anything. If you've used FM all your life or for years then you've gotten used to it. I am used to have multiple windows open, each one having different pieces of code (parts of the overall apps). I'm used to cut-n-pasting code, good debugging tools, variables (that are independent of the table and that can be used throughout the program), reusable code, good help. Look here's a short list of improvements to FM's development environment (copied from another thread) that I think would go a LONG way.

1. Debug mode: The ability to set Debug ON per script. Or at least get the Breakpoints working from any mode. Right now it's either all or nothing. You can set breakpoints in ScriptMaker but they don't trigger unless you are in Debug Mode. Also, god help you if your scripts call other scripts. You sit there and click away. Allowing me to set a breakpoint in ScriptMaker while writing the script and have it fire off ALL THE TIME (until I remove the breakpoint) from/in any mode would be nice.

2. Multiple windows. Like multiple script windows. What if I want to compare scripts? I have to print out the script now. Also, allowing me to open the Database Design window while in ScriptMaker would be nice. I am surrounded by screens, not printers. While I can print out the structure of any database I have to get up and walk across the room to the printer (or yell for someone to throw the print-out to me). It would be nice to be able to just leave the Database Design window open for reference and such. Save time too and trees.

3. The ability to copy (cut-n-paste) scripts steps. PLEASE! The Duplicate ability (button) is insulting - like someone threw it in at the last minute to make the masses a little happier. Let me copy what steps I want and paste them WHERE I want.

4. Improve the help. Right now it's terrible. Especially the OSX version. And I don't mean the content I mean how it performs. I know, on OSX this is partly Apple's fault (cause Apple Help sucks when compared to Windows). When I select the FM Developer Help [on OSX] from the Specify Calculation window I get the dreaded "No matching records found" from Mac help. This happens from several other obvious places in FM Developer as well. I mean, I'd expect to get a list of functions or maybe the docs explaining the layout of the window. Also, if I click the home button (after getting no results) I end up at Mac OS Help. I have to select FM Developer Help from the pulldown menu. Not good.

5. The ability to expand the function and database fields lists in the Specify Calculation window. I have a 23" Apple Flat screen monitor and on the Windows side, a 21" flat screen. Everyone geek in the office does. Also why can't I increase the front size in this window? Are you kidding? Some of these calculations get long and drawn out.. might be nice to enlarge the front size.

6. Oh and the ability to merge tables and datbase.

Yes I know some of these can be done via a plug-in. The merging of databases, and the variables I've seen plug-ins for. But why should I have to pay for plug-ins that provided what is considered a basic service in every other DB?

Also, one last thing - this is for FM Developer 7. So maybe this is in the wrong thread?

[KurtW]

Crag:

Yeah, I have wanted some of the same things. But as to using Access online, I have to say it has held up remarkably well for us. We serve about 8,000 records on a fairly busy site through Access and it handles it like a champ.

Started working with SQLserver200. Nice stuff.

[Crag]

Actually I'd dump SQL2000 unless you needed the extra processing and goodies that come with a full featured SQL server. Take a look at MySQl.. I bet it will do everything you need. Way cheaper and easier and faster. You should also take a look at FoxPro.

Now about Access, Doesn't Access have the connection limit of 10 users at one time?

Also, FM is a good database. I'm not bashing FM's cabilities. It's a fine system I just don't feel it's REALLY intended for the developer market, yet. Although FM disagrees with that I'd bet. And there is a whole catalog of apps made with Filemaker. So what do I know?

[MRT]

I have used Filemaker since version 2 (over 10).

In the past, the only experience I had with Access was to use a small Access db file in a Visual Basic project, though I didnt use Access itself, only the VB environment to create the file and add/read from it.

Filemaker is the best database for quick and easy database creation. It is all drag and click. You can get sometihning up and running in minutes, with really nice layouts.

Access takes a long time to get layouts/forms to look and function well. Everything has to be configured separately. Even the form wizards dont make nice layouts/forms. They are not user freindly and boring looking.

That said, Filemaker does not have a programming environment. Access has the advantage of coming with VBA, which can be powerful. It can also be integrated into any Visual Studio project.

Filemaker scripting is pretty basic. It really drives programmers nuts with the way you handle find/sort/imports.

If you want to create nice layouts quickly, and dont need programming behind them, FM is the winner.

But if youre creating a database solution that needs to accomplish advanced data operations, Access wins.

I dont know why the Access product team doesnt try to make it more user friendly. They could create more qizards and automatic formatting features for the novices.

If I was an Access Pro and a File maker pro, I would choose Access.

But there is a steep learning curve with Access, and I wouldnt recommend anyone to switch from FM to Access if theyre not familiar with Access.

Im trying to create a 30 table database with Access right now and Im having a hard time with the forms. Its so hard to display related data from multiple tables on the same form. With FM, you just drag the fields from any table onto the same form. As long as you set up the relationship right, you can easily drag any field from any table onto the same form without making all these settings and queries.

I cant get the same thing done in Access. SOmetimes when I have fields from multiple tables on the same form in Access, records wont be shown in view mode. It seems that if some fields dont have data, the whole record wont be shown.

Im having a hard time with Access and Im so tempted to go to FM7. But every computer here has Access installed,and no one has FM, nor can they get it expensed.

Not sure where to go from here.

[DanBrill]

Im having a hard time with Access and Im so tempted to go to FM7. But every computer here has Access installed,and no one has FM, nor can they get it expensed.

Not sure where to go from here.

Hmm. Computers are coming bundeled with Access and not FM, and so Access gets used by default? Sounds like the makings of an anti-trust lawsuit. I can't believe no one has thought of this.

[H.P.]

Computers are not bundled with Access.... It's the IT department that thinks that all computers must have MS. Office suites installed for productivity What a waste of resource! All of us should install Open Office instead

[Matthew F]

How about Drag and Drop programming? Are there any ways that either FM7 or Access that can accomplish this? A colleague showed me a solution they developed with 4D software in which inventory items can be moved from one bin to another simply by dragging and dropping the item to that new location. That feature alone is enough to make me consider migrating to 4D vs. upgrading to FM7.

[Kevin Wertz]

MRT, what about v7's instant web publishing? The basic Filemaker Pro 7, without server, allows 5 or 10 concurrent users (can't remember which) for no additional cost. If your application isn't too complex nor requires more than 5 or 10 concurrent users, this would be a great option and would not require all the users to buy a copy of FMP. I really like the instant web publishing in v7. It has some quirks, but it's pretty slick IMHO.

[Fitch]

Here are some possible workarounds for you:

1. Debug mode: ... god help you if your scripts call other scripts. You sit there and click away.

You know about Step in/Step out, don't you? There's more to the debugger than breakpoints.

2. Multiple windows. ... What if I want to compare scripts? I have to print out the script now...

I'm on OS X too -- I use the Preview button in the Print dialog box to look at scripts.

3. The ability to copy (cut-n-paste) scripts steps.

One trick that helps sometimes, but only within a single script, and only when when moving steps down: insert a comment where you want to move the script steps, somewhere down below the steps you want to move, then shift-click or cmd-click the steps you want, then cmd-click the comment. Now click Duplicate, and the steps duplicate down where you inserted the comment. Cool!

4. Improve the help. Right now it's terrible. Especially the OSX version. And I don't mean the content I mean how it performs. ...

I have the FileMaker help files bookmarked in Safari. Like so (your path may vary):

FileMaker Error Codes (FMDev7 OS X only)

5. The ability to expand the function and database fields lists in the Specify Calculation window... might be nice to enlarge the font size.

The lists are annoying, but one way to deal with with long calcs is copy/paste into a text file.

I'm not saying your gripes aren't valid, just trying to offer some constructive ideas.

[Fenton]

I would have to agree with the gripe about Fields lists in the calculation window. It would be so nice if the field list could go the height of the dialog window, ie., down the left side of the calculation. One tends to want a long list at least 90% of the time, versus a wide calculation window only about 10%. Same with the functions list. There are a lot of Get functions. Yes, you can type to scroll, but many of them start the same; it's awkward. It would be heaven to be able to toggle the configuration.

We used to be able to have a long fields list in versions 3-4, with an unsupported, but widely used hack to the resources. It was really nice. But starting in version 5 we couldn't. OK.

Then, Mac OS X broke scroll wheel support in FileMaker; and it still doesn't work. It is difficult to maintain a good development rhythm when such basic parts of an application get bent. I know that much of the blame belongs to Apple, who keep improving, hence changing OS X. But scroll wheel support? Come on, it's been years now.

Otherwise 7 is peachy :-) I notice when I have to go back and fix something in 6. It seems clunky.

[ffarmer]

If you're happy with Access, stay with it. I've been using Access95 on a Mac G4/450 for the past few years -- running it under OS 9.2.2, Virtual PC 2.1.3, and Windows 95. Worked great. These are all old versions, but they suited my needs.

When I upgraded to OS X, on the same computer, I found that this old version of Virtual PC would not run in Classic mode, but it DID run if I booted up in OS 9 -- a bit of an inconvenience. I'm now using OS 10.2.8 and this arrangement still works fine.

But, I'm also looking ahead, and thinking I'll want to upgrade to a G5 and OS 10.3x within a year. If so, I won't be able to boot in OS9, so I'll have to upgrade Virtual PC -- and probably Windows as well as Access/MS Office -- which would easily cost me $1000 CAD.

So, I figured, it would be cheaper to get Filemaker Pro 7 for OS X, and convert my database (about 10 tables, a dozen reports etc). I was able to get FP7 Upgrade for about $200 CAD, a considerable saving. My FP7 database is identical to my Access table -- identical tables, reports, "screens" etc.

HOWEVER, considering the time and pain I had converting (& learning FP7), it would have been cheaper for me to stay with Access and migrate with that.

Summary of my experience....

- as stated by others in this forum, FP7 has a different approach to database development and operation than Access. After three months, I'm still not fully "used to it".

- moving the data from my Access tables to the FP7 tables was the easiest part. No problem.

- creating most of the reports was labor intensive and there were some quirks that bogged me down (some simple things in Access, required me to write scripts in FP7 -- for example, being able to number pages as "Page x of y" on reports, or calculating running totals within control breaks in reports; I found this very klutzy and a pain).

- as mentioned, the databases are identical, and are on the same computer. They are not huge -- largest table is about 2500 records. However, FP7 is considerably SLOWER than ACCESS (even though Access is running under three layers of "systemware" -- OS 9, Virtual PC, and Windows 95)

- knowing what I know now, I would not have bothered with FP7. I feel more confident with Access. I'm not a Microsoft fan, but I found Access easier to learn and deal with than FP7. Microsoft has taken over Virtual PC (from Connectix) and are expected to release a new version this summer for OS 10.3/G5. I believe it will run like an OS X application, and allow you to run Windows and Windows applications on the G5.

- unless you have some other compelling reason to switch from Access, stick with what you know. If it ain't broke, don't fix it.

[Leader]

Over and over again, I hear the Finance Maker refrain, "But MS Access is FREE!" in companies from Disney to Eli Lilly.

Yeah, there's a reason it's free! The local grocery gives away moldy bread, but I wouldn't ingest it.

I can- and have- been critical of FM for one reason or another, most recently about the 'pretty' but poorly organized FM7 documentation and especially the non-working IWP. However, when it comes to producing, it's tough to beat FileMaker. I can accomplish with FM in hours, what takes weeks with Access. FM frees you to design and not get bogged down in details.

I've been in company after company that have bundles of unfinished Access projects, but you don't see unfinished FM projects because they're so easy to do. My most recent FM7 project happened because the client had intended to allow 7 months for an Access project, and they suddenly realized they had 2-1/2 weeks. I promised them I could do the project if they'd opt for FM, and they did and I did.

I confess that coming up from early versions of both products, I don't see much of the paradigm shift from Access to FM, except that Access takes a LOT more manual labor. Also, I always have to look up bits and pieces in MS manuals, which I seldom have to do in FM. Whenever I come back to FM, I find I have to look up portals each time, but that's about all.

My main hump (as a programmer) in the early days of FM was the lack of control logic in scripting. After a while it hit me that the way the designers had built FM, the control logic wasn't needed. Still, my comfort level increased when Claris finally added in loops and the like.

Most importantly, FM is multi-platform, so what you develop on one, you can use on another.

[KurtW]

I started in FM years ago and loved it at first site. I absolutely could NOT figure Access out. And, like you, I was able to do pretty advanced things in FM fairly fast.

But for the last few years it's been web development with Access or SQL server 2000 where you are doing the dirty work (most of it) through your SQL code. It's easy to get into the mindset of raw SQL 'chatting' to your database. It is this mindset that has been making the transition back to FM a bit rustier than it might be otherwise.

I like 'em both for different things. FM is a great desktop solution but Access has it beat 100% (imo) as a web-application back end.

And one nice thing about using SQL with Access or SQLserver 2K is that, with a minimal amount of tweaking, you can move to other 'flavors' of SQL, like those used by MySQL, IBM, Oracle, etc.

Each has its place. My only gripe is that since FM is such a different paradigm from raw sql, it sometimes has an odd feel. I keep looking for a window where I can just write in some damned SQL code and be done with it.

Both have a place for me, though.

[George]

Have a look at this product www.alphasoftware.com

Has runtime, frontend to MySQL, separate data files from rest of application, no plugin "pay-in" necessary for expected functionality, affordably priced, cross platform capable with Web Application Server...

[KurtW]

Heard of it but this is the first timem I've seen it. Thanks

Kurt

[bruceR]

Can what? No, you can't have a layout without a predefined table/fields and of course a script can't trigger on exit from a field that doesn't exist. So I'm not sure what your point is. However - perhaps you haven't discovered editable file references yet and the concept of table occurrences as aliases. So just because you originally linked to one table when defining the layout doesn't mean you can't dynamically relink the layout to a tablel half way across the world in 2 seconds.

You CAN use SQL to add/drop/alter Filemaker table definitions. Table occurrences are linked to physical files by file references, and file references are just text; and you can have multiple references. So try this simple test. Create a file that references and external file; for instance Contacts contains a file reference for Invoices, and the file reference is

file:Invoices

Change the file ref to read

file:Invoices

file:Invoices2

Duplicate Invoices.fp7 and rename it Invoices2.fp7 Add or delete a record. Contacts doen't "see" this yet because Invoices.fp7 is still there. Close the files, move/rename Invoices.fp7. Reopen Contacts. It is now looking at Invoices2.fp7. This is kind of a quick and oversimpified example but the point being that yes you can have dyanmic tables and yes you can have dynamic table references.

[Leader]

You write the testiest damned notes, Bruce, but you make an excellent point.