Wiki Home

VFP vs Access


Namespace: VFP
Issues In Making a Selection Between Them

Two sites:
I put a message out on the MS VFP newsgroups and got this reply:

HOW ABOUT THIS. I HOPE THIS HELPS...
Alexander Lawson, Principal Consultant, Yevich, Lawson and Associates, Inc. (A FoxPro Development Firm)

Visual Tool Features Comparison

Feature

Included in Microsoft Tool

Access

VB

VFP

Cross platform support

   

X - (3.0)

Full complement of object based design tools

X

X

X

Drag-and-drop to set relationships between tables

X

 

X

Highest number of ease of use features

X

   

Table, Form, Report, and Query Wizards

X

 

X

Import Wizard

X

 

X

Upsizing Wizard

X

 

X

Create SDI and MDI forms

 

X

X

Drag-and-drop fields onto forms

X

 

X

Field mapping to specify type of control or class used when field is dropped onto form

   

X

Updateable query result sets

X

X

X

Color coded editor

X

X

X

Debugger includes Locals, Watch, Call Stack window

X

X

X

Watch values in debugging

X

X

X

Debugger settings can be saved for later use

   

X

Best visual integration with Microsoft Office

X

   

Programmatic integration with Microsoft Office

X

X

X

Excellent support for ActiveX controls

X

X

X

Support for ActiveX controls that act as containers

 

X

X

Support for ActiveX controls that bind to a row of data

X

X

X

Spport for ActiveX controls that bind to multiple rows of data

 

X

 

Vtable binding for improved ActiveX control performance

   

X

Early binding in code for improved Automation server performance

X

X

X

Can control other Automation servers

X

X

X

Can be called natively as an Automation server

X

 

X

Can create custom Automation server

 

X

X

Remote Automation support

 

X

X

Component Manager to manage Automation servers

 

X

? 

Internet wizard

X

 

X

Object Browser

X

X

X

Integration with Visual SourceSafe (Access requires Microsoft Office 97, Developer Edition for VSS)

X

X

X

Full support for object oriented programming, including inheritance and subclassing

   

X

Object programming through class modules

X

X

.PRG?

 

Create reusable code

X

X

X

Create reusable interface elements

   

X

Subclass ActiveX controls

   

X

Event tracking tool

   

X

Coverage logging tool

 

X

X

Code profiling tool

 

X

X

[Microsoft] Note: This comparison is not an exhaustive list of the features of each database engine. Its intention is to highlight some of the similarities and differences between the engines. For more information, see the sources listed in the full article at http://premium.microsoft.com/msdn/library/bkgrnd/choosing.htm#choosappendd
[Note: the above URL requires MSDN registration -- Editor]

Database Engine Features Comparison

Feature

Included in Microsoft Engine

Jet

FoxPro

Sql Server

Rushmore query optimization

X

X

 

Top n and top n% queries

X

X

X

Validation rules

X

X

X

Validation rules can be custom code

 

X

 

Default values

X

X

X

Default values can be custom code

 

X

 

Triggers and stored procedures

 

X

X

Referential integrity through triggers

 

X

X

Declarative referential integrity

X

 

X

Engine level cascading updates and deletes

X

   

Basic locking unit

Page

Row

Page

Row locking on insert

X

X

X

Row locking on update and delete

 

X

 

Table-level replication

X

X

X

Row-level replication

X

X

X

Field-level replication

 

X

X

Custom code for replication conflict resolution

X

X

X

Scheduled replication (Jet engine requires the Microsoft Office 97, Developer Edition)

X

 

X

Replication over the Internet

X

   

Built-in security

X

 

X

Built-in encryption

X

 

X

Transaction processing

X

X

X

Distributed transactions

   

X

Dynamic backup and restore

   

X

Backup and restore a single table

 

X (every table separate file) .. automate backups must be programmed

 

X

Transaction log backups

   

X

Automatic recovery

   

X

32-bit engine

X

X

X

Data capacity

1.2 GB per database

2.1 GB per table
Unlimited per Database
(or, # Tables X 2.1GB )

1 TB per database

Maximum number of users

255

Unlimited

32,767

Hyperlink data type

X

   

From MSDN Re:Access 2000 (emphasis added)

Record-Level Locking
With the increased page size (from 2K to 4K) required to support the Unicode format representation, there is the potential for decreased performance and concurrency. To minimize the impact of the increased page size and respond to a long-standing request from developers building applications based on the Microsoft Jet database engine, row-level locking was added to Jet 4.0.

Instead of locking an entire page and possibly multiple rows of data, an application can choose to lock only a single record at a time, thereby increasing concurrency and performance.

A database can be opened in one of two modes:
Page Locking Mode
Record/Page Locking Mode
Page Locking Mode is essentially the former method of locking pages, that is, locking the entire 4 KB page whenever a user updates a value in a record.

The new Record/Page Locking Mode, as its name implies, supports either record or page locks. Note that while record-level locking reduces concurrency conflicts and thereby increases performance, the disadvantage of record-level locking is also performance related. Performance decreases when there are many records being updated at once (for example, a SQL Data Manipulation Language (DML) statement or a loop in a program that modifies a large number of records). This is because a lock request must be placed for each record, instead of one lock request for each page. Therefore, depending on the size of a record, performance could be severely hampered. This is the reason why both locking modes are available in Jet 4.0, giving the developer the option of returning to the former Page Locking Mode if that method is desired. By default, Access will enable record-level locking, but the user can disable this within Microsoft Access by pointing to Options on the Tools menu, clicking the Advanced tab, and clicking to clear the Open databases using record-level locking check box. By default, access to data via Microsoft Access forms will use record-level locking (in other words, two users can update or delete different records on the same page).

A limitation to record-level locking is that users will not be notified when another user is locking the record. Also, record-level locking is not enabled for Memo data types.

When using SQL DML bulk-operation statements, Jet will default to page-level locking. When using Access forms or DAO/ADO recordset objects, Jet will default to record-level locking.
Posted on Foxite June 2013
From: Cetin Basoz of Turkey
To: Suresh Ramtekkar of India
Thread ID: 378051 Message ID: 378142
Category: Databases, Tables and SQL Server
Archive URL: http://www.foxite.com/archives/0000378142.htm

> Dear Cetin Sir,
> Why are not use Access database, what is drawbacks... please explain.
> Thanks & Regards
> Suresh Ramtekkar

For a good explanation there should be a compared to what database too.

First, Access is a file based database system, just like VFP is. Other databases are service based (there are some new file based databases too but they are mostly NoSQL databases and I think out of this comparison anyway and they are already suggested for single and embedded uses). Being file based is #1 drawback. At least, to be able to read/write you need to have file level access. That doesn't generally pose a problem in a LAN system but automatically puts it out of options in a WAN. Just like VFP it is not client/server either. Thinking about just these I wouldn't even attempt to compare it to other databases but just VFP. For example, comparing Access to MS SQL is simply nonsense but I will come to that later. First, as a VFP developer why not choose Access vs VFP database? These are enough show stoppers for me. I would use VFP database, if I had to use file based data (and I don't know why I would need that other than some quick create, use ... etc). If I would do anything that needs a connection Access is not even on my last option, probably a text file or an XML, json file would be a much better alternative. I can't see a single plus to use Access.

If I had a chance to choose from other databases too then I wouldn't even think the 'drawbacks' it is nowhere near the comparisons: All these databases and many more of them have undeniable plusses compared to Access (and VFP as well).

Maybe you should ask yourself, what plus does Access have worth to using. For myself I can't see a single plus.

Cetin Basoz

See also Comments On MSAccess
Anyone have any other takes on this?
Contributors: Russell Campbell, Steven Black
Category Data Category Programming Languages
( Topic last updated: 2013.06.06 09:48:33 AM )