asked on Access vs. Filemaker Pro vs. SQL Server
The only DB experience I have is (unfortunately) with MS Access. I'm now creating a software application that interfaces with a DB, so I just went with what I know (Access). But, I don't really like the user interface, especially for dealing with relations. Also, it has a very limited range of data types (no real numbers, Booleans use a strange convention for storage, etc.). And most recently, I've run into an intractable problem whereby I cannot programmatically add or update records (covered in a separate post). So, now I'm seriously considering moving to another platform. The two I'm considering are Filemaker Pro (Is that different from Filemaker?), and MS SQL Server. But, I know essentially nothing about either. So, I'm looking for some advice on the trade-offs / suggestions for which way to go. Also, of course I'm wondering what is involved with importing / translating my existing DB to one of the other tools. (If it matters, the DB has 38 tables, and about 200 fields, but very little data so far.)
FileMaker ProSQL* FileMakerMicrosoft Access
Last Comment
PatHartman
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Pat,
Thank you for your response. By "FE" do you mean Front End = User Interface? One example, I'm looking at a couple tables and see I need to add a relation between them. So, I add a row, put int he field name, select the wizard, tell it the table, then fill out several more forms, and when I'm finished it THEN tells me I can't because the other table is open. Why doesn't it tell me that immediately when I select the table. Also, how about giving me the option to "Try Again" after I close the table, so I don't have to fill out all the forms again? Another issue is if I have two relations from Table 1 to Table 2, the report draws two copies of Table 2, and on top of that, the second one does not show the relations from it to other tables. Also, when I Edit a relation in the relations window, I get a completely different form than I see in the wizard, and cannot see how to set up the relation the way I want to. So, what I end up doing is deleting the relation and reentering it from scratch. I would also like to see some sort of indication in the Design View that a field is a relation. Instead, it says it's a number or a string.
Steve
Thank you for your response. By "FE" do you mean Front End = User Interface? One example, I'm looking at a couple tables and see I need to add a relation between them. So, I add a row, put int he field name, select the wizard, tell it the table, then fill out several more forms, and when I'm finished it THEN tells me I can't because the other table is open. Why doesn't it tell me that immediately when I select the table. Also, how about giving me the option to "Try Again" after I close the table, so I don't have to fill out all the forms again? Another issue is if I have two relations from Table 1 to Table 2, the report draws two copies of Table 2, and on top of that, the second one does not show the relations from it to other tables. Also, when I Edit a relation in the relations window, I get a completely different form than I see in the wizard, and cannot see how to set up the relation the way I want to. So, what I end up doing is deleting the relation and reentering it from scratch. I would also like to see some sort of indication in the Design View that a field is a relation. Instead, it says it's a number or a string.
Steve
I just read your other thread and you have some misconceptions about what Access is and what you are actually working with.
1. No other database engine is going to allow you to make design changes while a table is in use. In fact it is really poor practice to be making design changes on the fly. You should always be working in a testing environment and then when the modifications are complete and tested, move them into the production environment after first getting everyone out of the database. In order to be as user friendly as possible, Access does not LOCK the first table when you open the relationship dialog and it would have to be prescient in order to lock the second table. How would it know at the beginning what table you wanted to make the relation with?
2. The dialogs are modal for a variaty of reasons and that is why you won't ever get a try again option. MS expects you to be aware of your surroundings and to not be making design changes to objects which are currently open.
3. If you think the Access GUI dialog for relationships is confusing, wait until you try to work with SQL Server or other RDBMS where they prefer that you run scripts rather than use a GUI.
4. You can see the relationships by using the relationship diagram. You can present a much more understandable schema by using a good naming convention. All my primary keys are named somethingID and NOTHING else ever ends with "ID". Therefore, when you see a column named SomethingID in a table you KNOW that it is a foreign key and if your names are good, you can deduce the name of the other table from the first part of the Foreign Key.
No other RDBMS is going to solve these issues for you.
Here's a picture of a relationship diagram that should illustrate what a good naming convention can do for you. The tables with "lkp" as a prefix are strictly for lookup. They don't contain transactional data. They simply provide the source for combos and allow me to use autonumbers![DrawingLogRelationships.JPG]()
1. No other database engine is going to allow you to make design changes while a table is in use. In fact it is really poor practice to be making design changes on the fly. You should always be working in a testing environment and then when the modifications are complete and tested, move them into the production environment after first getting everyone out of the database. In order to be as user friendly as possible, Access does not LOCK the first table when you open the relationship dialog and it would have to be prescient in order to lock the second table. How would it know at the beginning what table you wanted to make the relation with?
2. The dialogs are modal for a variaty of reasons and that is why you won't ever get a try again option. MS expects you to be aware of your surroundings and to not be making design changes to objects which are currently open.
3. If you think the Access GUI dialog for relationships is confusing, wait until you try to work with SQL Server or other RDBMS where they prefer that you run scripts rather than use a GUI.
4. You can see the relationships by using the relationship diagram. You can present a much more understandable schema by using a good naming convention. All my primary keys are named somethingID and NOTHING else ever ends with "ID". Therefore, when you see a column named SomethingID in a table you KNOW that it is a foreign key and if your names are good, you can deduce the name of the other table from the first part of the Foreign Key.
No other RDBMS is going to solve these issues for you.
Here's a picture of a relationship diagram that should illustrate what a good naming convention can do for you. The tables with "lkp" as a prefix are strictly for lookup. They don't contain transactional data. They simply provide the source for combos and allow me to use autonumbers
On the surface, I like what Filemaker Pro offers but it costs more than Access and doesn't seeme to offer more (unless you need web access) and there is a big learning curve.
If you don't like booleans, use integers.
What problem are you having with relations? As someone who works both with SQL Server and Jet/ACE, I would take the Access inteface hands down over what SQL Server offers for schema maintenance. The only "better" piece that SQL Server offers is the relationship diagram is more flexible.
If you prefer working with SQL Server as the BE, Access will be fine. I use it for most of my aps. If it is the FE you have issue with, good luck finding something as good as Access.