Hi Michael, Most of my work comes from Access development but I am still active with DP development.... I actually like both environments, they are as different as chalk and cheese... I think Access is Microsoft's best retail application. I think SQL Server is an excellent backend database... I love working with DP.
DP has a particular paradigm that stops it having the flexibility of other products. Both the presentation of data (ie its user interface), its programming interface, and its data store are intimately bound together. This paradigm makes it exceptionally good for rapid development, and it makes for a consistent user interface and programming environment so it makes it a very stable consistent platform to develop with and use. Its lockless multiuser editing of the same record makes it incredibly good for high volumes of users and in particular where there are stong possibility of users editing the same or related records. SQL Server is much closer to DP that you would think. SQL Server is a backend product in that it does not have any user interface at all... I use DP for web applications, and I treat it in a similar vein to someone using SQL Server. I have had my sporting web application working in a club for over three years with about 200 current users, Onloy a small fraction of those users use the application concurrently, however I have had at times 10 copies of DP being executed simulataneously... I have server in the vicinty of 200,00 database interactions without the slightest problem or donw time . DP is nowhere near as powerful as SQL Server, and SQL Server can be scaled up for far more concurrent users. SQL Server runs as a service on the host machine meaning it is always in memory and ready to serve requests, whereas each user interaction of DP means a sequence of applications need to be loaded into memory before the application can do anything. DOS, DP.EXE, then loading of the application STR, and after it has finished the applications need to be unloaded from memory. So why do I persist with developing in DP when there is so much overhead? well firstly for small numbers of user on average webserver hardware, it has only a slight performance penalty so the comparison to SQL Server is not so bad. But the main reason for using it is that I can develop robust complex applications far faster. In DP developing the data rules, occur via the definition of the user interface, so the data rules are less abstract, they have real and obvious meanings. For instance, take the Keep A Total function which allows you to define a field whose total can be added or subtracted to fields in related tables when a change is made to the child record. Firstly I have to say, that there is no equivalent whatsoever in Access (you have to do all that by programming at the client end, and you must do it each and every time you modify that record. This makes Access extremely prone to error, as you have to remember to code that interaction any time you write a piece of code or form that users that table. SQL Server (and other similar DB's eg MySQL, Informix, Oracle etc) have what are called Triggers. Triggers are pieces of programming that are attached at the data end of things and not in the client application, (as it effectively is in DP). You can write a Trigger to say that any time a change, addition or deletion of a record occurs that another action will be performed on another data table,and that like DP can be used in a cascading effect. The true advantage of that is that the client application developer does not need to think about what happens, all he does is add, delete or change the record he is interested in, and the consequently changes like Keep A Total happen in the background without his or her knowledge. Triggers in these types of databases are ultimately far more powerful than in DP. DP has a particular default interaction such as Keep A Total, whereas triggers for example can be used to ensure far more complex events. Ror example deleting a record, my trigger and action to copy it to an archive table, updating the username, timestampe etc, or it might act on multiple other records in multilpe other tables. One of DP's major deficiencies is its inability to truely interact with other records when you are making an edit. For example for many DP developer they have had times when they might like to check for a duplicate record before adding a record, however the issue of duplications is not always straightforward. DP allows you do do it in a fashion with indexes and exception lists, however sometimes the interaction needs to be far more complicated, For example I need to add new players into my sporting database, but I do not want to create duplications, so I want it to fail if the first and last names are the same, and additioanlly if any of the conditions such as phone number, address, date of birth, or email address are identical. In SQL Server that can be performed in a trigger. In Access you can perform that by program code on the client application, in DP the way most people interact with it, it can't be directly done, and with a web enabled DP it has to be done via the a Report (which makes it very similar to what you have to do with Access). In this area SQL Server is by far the winner. Programming multuser in Access is far more complicated. If you only have a few users, or users do not frequently edit and update the same or closely related records, then it is no problem, but soon you will run into locking issues and inconsistent updates. SQL Server is far better than Acecss because it is not the multiple instances of the client application manilpulating the data but one instance of the data engine serving multiple users, so it can manage muliple users updates far better, although you still need to add programming and administration consideration into it when in DP you will be oblivious to the potential problems as DP handles it so elegantly. When you web enable DP, rather than using DP interactively, you are then confronted with multiuser issues. Never in regards to locking, but more about inconsistent updates. For example if DP is asked to provide data to fill in an web form, you run the report, and once it is finished you are disconnected from DP, DP does not know what you might do with this data. If you simply save that data and any edits back , you might inadvertently overwirte new data in another field where because they had updated before you. The programming in DP and the web application is immensely complicated to avoid that happening. I use XML as my output data from DP. XML is self describing, and can be used by so many client applications. Although Access can export XML, it can only do it in a very crude fashion, with almost no control on things like names spaces, entitites and CDATA. SQL Sever can deliver SQL statement results in XML form, with far great control that Access but without the flexibility of DP. The reason for this is that DP does not understand XML so you need to do it very primitively within the report writer. however becase it is primitive and because the report writer in DP is absolutely amazing in is capabilities, you have total and absolute control of your output in ways that Access and SQL Server can't. In fact for SQL Server you really need a middleware application layer to do things like this. On the other hand things coming out from Access or SQL Server are standards based, and middleware layers can expose incredibly simple yet powerful web services interfaces that DP and complex CGI middleware programming I suppose could do if you ever had the time to write it. DP does not slow down with large volumes of data. Access does, and in fact Access is limited to 500mb in a single MDB file which might be where all the tables are housed.. SQL Server can outgrow both Access and DP. My web applications with DP give users everything they expect from a GUI web application. They do not know that the backend is DP, I would write the same user interactions if I were doing it with SQL Server. My applications include conections with otehr databases, extracting information from other websites, sending emails and sending mobile phone text messaging, but all of this processing occurs in my middleware layers on the server. One of the really great things aobut Access is that you can replace the Access tables with data from SQL Server... This means applications can be scaled up... Although it generally is not as straightforward as it sounds. Acecss has the cabailities to use Visual Basic programming code in queries on Access data. If you use those same functions with SQL Server then you have to bring all the data into the client application rather than have the server produce the results. You can write stored procedures on the server, and you can pass SQL statements thru to the server but then you can only use the vocabulary of SQL. In many cases complex Access applications run better as Access applications run that upsizing them to SQL. I actually have written various mini tools for managing the migration of DP to Access or the administration of DP. For example, I have updated the STE Manager application so that it can document DP panels and output the results as an XML file, where I can use various XSLT transformations to view varying reports about the application. I also have it outputting an SQL DDL (data definition language) statement so that I can reproduce the structures in SQL Server or MySQL, it is not by any means perfect, actyually they are pretty kludgey and usually the only reason I want to do that is because I can import them into ER Diagram software so I can produce ER diagrams of my DP application. The tools are pretty rough and ready as they are for my own use so I am guessing that you might not find them all that useful, although you are welcome to try them. On the other hand Access and SQL have a wealth of high quality administrative tools, from a variety of vendors. It is a shame more people are not giving the DP web applications a try because I am sure if people did they would probably think that they already have a Windows version of DP. At the conference in Los Angeles in 2004 I discussed a large DP application that I had been using for many years. Just after I arrived back in Australia the company was sold to a much larger public company who were horrified that DP and WordPerfect were being used by the largest supplier of this product and service in Australia, they thought we had far "better" technology for the results had achieved. They put our success down to dumb luck. They immedaitely set on a plan to replace the application, and to increase its functionality into being a webapp, and the timetable for that was 3 months, and $300,000 ... That was almost 4 years ago, and rumours are that the transition to VB.NET and SQL Server has cost nearly 1.5 million dollars, and is still does not have the much vaulted functionality they had planned and has only in the last few months come up to have a similar functionalist to the application they replaced albeit slower, and with less operator satisfaction. I have been involved in a patent dispute because a company managed to get a patent through a few years back but which my original 1990's DP and WP application combination had, one of the barristers who was documenting my aplication had to learn intimately how it worked, and said he could not believe how easy it was to use DP, and downloaded a copy of it and documentation from the DataPerfect.nl website so he could play with it some more himself. I hope this helps with your migrationm whichever way that is Regards Brian ----- Original Message ----- From: Michael Iannantuoni To: Dataperfect Mailing List Sent: Friday, February 22, 2008 9:00 PM Subject: [Dataperf] DataPerfect V's Access & SQL I have someone who wants to change from DataPerfect to Access/SQL for no good reason other than it's Windows and modern! Can someone please help me list the pro's & cons of DataPerfect v's Access/SQL and the DP approach v's the "all data can be represented in a table" approach. Thanks, Michael ------------------------------------------------------------------------------ _______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf
