Access or FoxPro?

[Simon Bailey]

I am a newcomer to databases and am not sure which DBMS to use. I have
a very simplified knowledge of databases overall. I would very much
appreciate a (simplifed) message explaining the advantages and
disadvantages of both programs.

Many Thanks

Simon

[Albert D. Kallal]

I would have say that since you asked the question in a ms-access newsgroup,
then you kind of have to expect some leaning towards using ms-access.

The other issue is how much previous development experience you have. If you
done a lot coding, and prefer a OO approach to application development, then
you certainly might find FoxPro your cup of tea.

If you spend a lot of time coding in a language like VB, then you will find
ms-access a lot easier to learn. (in fact, the programming language you will
use to write code in ms-access is really the same as VB, but the forms are
different).

About the only real downfall of FoxPro is that there is not as large of a
user base, and there are more books, web sites, and newsgroups for
ms-access.

Further, you also realize that products like ms-access are not really a
database system anymore. ms-access is a front end tool that lets you develop
reports, forms, and write code. The database engine is a separate issue.
When you purchase a database system like Oracle, or sql-server, you only get
a box that lets you store data. You still have to write the application and
the user interface using some tools. Those tools could be a web interface,
or VB or even ms-access.

Thus, one should keep in mind the separation between the database system, or
so called database engine, and the application development tools you use.

Thus, one should view ms-access as a application development tool, and not a
database. In fact, the office cd ships with two database engines. You can
choose to use either database engine with ms-access.

Both ms-access or FoxPro make good development tools that can connect to
your database engine of choice.

Microsoft has marketed the FoxPro environment more to developers, and
ms-access has been marketed more towards end users. Thus, ms-access is
considered easier to learn.

FoxPro also has some advantages in distribution of the final application.
However, those advantages are not that great if you use 3rd party tools for
ms-access. Many people will argue that the database engine for FoxPro in a
file share is more reliable then the JET engine for ms-access in a file
share (however, every FoxPro developer I know has suffered some form of
index corruption).

So, if you are in need of a application development platform that is Object
Orientated all the way, then FoxPro might be your ticket. If you need
something that will get you up and running a lot faster, then Ms-access is
probably the way to go.

Much will depending on your previous programming experience. If you don't
have a lot of development experience, then I would bet on ms-access.

However, don't count ms-access out as a serous development tool either. You
can create class objects, and any project you attempt in ms-access still
should be planned in detail.

Here is a article of mine on using class objects to give you and idea of
what I mean:

http://www.attcanada.net/%7ekallal.m.../WhyClass.html
Also, here is some notes on a project I did in ms-access, you might again
find this a good read:

http://www.attcanada.net/%7ekallal.m...000000003.html

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[BillB]

I am far from being an expert in either Access or Foxpro. I did a lot of
programming in Dbase, Foxbase and Foxpro 2.6. I have used Access for several
years, but just recently started development.

I probably just don't know how to do it in Access yet, but I miss the tools
I had in Foxpro to operate on individual records:

In Foxpro you can program a loop to skip (randomly) through each record in a
table and perform operations on individual records (ie calculate a running
total). In Access you operate on records as a group.

The Foxpro scatter/gather commands are quick ways for a screen to operate on
variables rather then directly on live data.

Foxpro records when deleted are not gone until you pack the table.

I find there are always to do things I need in Access, and plenty of help on
the web. I would say, at least for my level of knowledge, Access seems to
have more of a finesse way of doing things where Foxpro you can power your
way through.

My two cents worth
Bill.

[David W. Fenton]

to much SPAM to list here.com (BillB) wrote in
<vp************@corp.supernews.com>:

I probably just don't know how to do it in Access yet, but I miss
the tools I had in Foxpro to operate on individual records:

In Foxpro you can program a loop to skip (randomly) through each
record in a table and perform operations on individual records (ie
calculate a running total). In Access you operate on records as a
group.
There are some things that can't be done with a SQL update query.
And a running total probably doesn't belong stored in a table, in
any event!

For sequentially stepping through records in Access, you'll want to
look into recordsets. If you're storing your data in an MDB file,
use DAO. If you're using SQL Server for your data, ADO will
probably be more useful. Both have Recordset objects, though they
don't have the same properties and methods.
The Foxpro scatter/gather commands are quick ways for a screen to
operate on variables rather then directly on live data.
Sounds like an unbound form in Access.

Access is definitely prejudiced towards bound data. And that's a
good thing, most of the time. When you need to work around it's
problems, then it becomes more complex.
Foxpro records when deleted are not gone until you pack the table.
I've seen dBase applications where someone used a DELETE to filter
the records for a certain purpose, and then didn't undelete them.
They gradually lost more and more data from their database and
didn't discover it until years later.

I don't see any advantage to having that capability -- if a record
should be deleted, it should be deleted.

Of course, in most applications, there's not much place for
actually deleting records, because if you're records are related to
records in other tables, you likely need to keep the record for the
purpose of maintaining its relationships with other data. Because
of that, most of the time you don't want to actually delete
records, you just want to have a flag field that indicates that the
record has been "retired." Then you filter out those records from
your active data sets.

It can be very complex, actually, because for some tasks you may
wat the deleted records to show, and in others you don't.

Not a trivial subject.
I find there are always to do things I need in Access, and plenty
of help on the web. I would say, at least for my level of
knowledge, Access seems to have more of a finesse way of doing
things where Foxpro you can power your way through.

Sounds to me like Access forces you to do it right, whereas FoxPro
allowed you to kludge your way to a result that seemed to work.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Albert D. Kallal]

"BillB" <to much SPAM to list here.com> wrote in message
news:vp************@corp.supernews.com...

I am far from being an expert in either Access or Foxpro. I did a lot of
programming in Dbase, Foxbase and Foxpro 2.6. I have used Access for several years, but just recently started development.
I also did a good stint in the old FoxPro 2.6 days. As mentioned, the new
version of FoxPro is a true Object Orientated development platform now, and
bears little resemblance to what the old FoxPro was. However, we can create
class objects in ms-access, and I generally find that to be more then
adequate.


I probably just don't know how to do it in Access yet, but I miss the tools I had in Foxpro to operate on individual records:

I did a bit at first, but now I don't even think about this fact. I mean,
for most looping and update stuff you can, and should use sql. However, it
is not a lot of code to traverse a table, and do things. The basic loop to
do this is:

dim rstRecs as dao.RecordSet
set rstRecs = currentdb.OpenRecordSet("yourTable")
do while rstRecs.Eof = false
debug.Print rstRecs!lastname
rstRecs.MoveNext
loop

rstRecs.Close
set rstRecs = nothing
The above is a standard base record processing loop in ms-access, and it
certainly is not any more code then is the old FoxPro. The above just prints
all the lastname fields to the debug window. (and the debug window in
ms-access is certainly your .dot prompt in FoxPro. I could modify the above
to do a replace, such as:

do while rstRecs.Eof = false
if rstRecs!City = "N. Y" then
rstRecs.Edit
rstRecs!City = "New York"
rstRecs.Update
endif
rstRecs.MoveNext
loop

Of course, the above can be written with one line of code to run a sql
statement, but the above does show that you can do old fashioned record
processing in ms-access. And, once again, the amount of code is very similar
to what you had in FoxPro. You can even send the record pointer to a
absolute position. Of course, in FoxPro we would use the "replace" command.
However, I was just pointing out how a basic record processing loop in
ms-access works, and it is very similar to old fox.

However, the one line of code to replace all the above in ms-access would
be:

currentdb.Execute "update yourTable set City = 'New York" where City = 'N.
Y'"

So, you can certainly code your record update stuff, but it just we don't
need to very much.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[David W. Fenton]

NO************@msn.com (Albert D. Kallal) wrote in
<zzDlb.148900$9l5.58298@pd7tw2no>:

However, the one line of code to replace all the above in
ms-access would be:

currentdb.Execute "update yourTable set City = 'New York" where
City = 'N. Y'"

So, you can certainly code your record update stuff, but it just
we don't need to very much.

And doing it sequentially will probably be more than one order of
magnitude slower than doing it with a SQL update.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Steve]

The two applications are similar in many ways -
- they both are used for rapid application design of data centric
applications
-they both contain a report and form designer
-they both can use local database engines (JET/DBF) or serve as
front-end to server database engines (SQL Server)
-they both can process information via SQL or individually processing
each record
-they both contain technology "rushmore" for fast selection of data
using indexes (technology obtained from Fox Company which was bought
by Microsoft)
-they both can allow the developer to distribute applications royalty
free.

The biggest difference is that Access is part of Microsoft Office,
whereby Visual FoxPro is sold seperately (or part of certain MSDN
subscriptions). Access integration into the Office suite is superior,
and many users may have Access on their computers.

But such integration can cause implementation issues - users have
different versions of Access/Office than you, or they upgrade their
Office after you implement the system. This is not an issue if your
are not distributing over a large base of users; but can be an issue
if you are. And, there are tools to help you install Access over a
wide user base.

VFP does not have similar issues. It is a seperate development tool.
One of the draw backs is that potential customers may feel more
comfortable with you developing in Access than VFP, which is not as
widely known or used.
Steven


On 22 Oct 2003 04:04:02 -0700, go*********@hotmail.com (Simon Bailey)
wrote:

I am a newcomer to databases and am not sure which DBMS to use. I have
a very simplified knowledge of databases overall. I would very much
appreciate a (simplifed) message explaining the advantages and
disadvantages of both programs.

Many Thanks

Simon

[Mike Thomas]

I've used FoxPro and Visual Fox Pro since 89' and Access since 98'. I
definitely prefer FoxPro for what I would call middle sized applications, 6+
users and maybe some larger tables, say over 500,000 records.

I think the programming language is clearer, I like the oo setup, the
ability to create controls and then subclass them, and the very direct way
of handling tables, both procedurally and with SQL. There are a lot of
things I think are bad about Access: especially the "one big file" approach
which I think fosters file corruption and makes it diffuclt to isolate and
repair and what I think is the sloppiness in the referring to objects.

However, most of my work is now in Access for several reasons: there are
very few FoxPro programmers left, it looks like MSFT is finally going to
drop the product, corporate MIS people are very prejudiced against it, and
since almost everybody in the corporate world has heard of Access and maybe
even has it on their computer, they tend to think that they "already have
the software".

There are a couple of things going for Access. It is great for writing
reports, I think the query builder works really well, and if you make a
successful Access application, it won't be too long before your customer
will want to upgrade to SQL Server and some sort of other front end, so it
tends to keep your customers coming back.

If I'd had my druthers, MSFT would have marketed VFP and it would be where
it should be, a great tool for mid-sized applications which are too
complicated for the casual user. But it has come to where there is no way I
can recommend VFP to anybody, mainly because so few people use it anymore.

Mike Thomas


"Albert D. Kallal" <NO************@msn.com> wrote in message
news:73zlb.149533$pl3.127202@pd7tw3no...

I would have say that since you asked the question in a ms-access newsgroup, then you kind of have to expect some leaning towards using ms-access.

The other issue is how much previous development experience you have. If you done a lot coding, and prefer a OO approach to application development, then you certainly might find FoxPro your cup of tea.

If you spend a lot of time coding in a language like VB, then you will find ms-access a lot easier to learn. (in fact, the programming language you will use to write code in ms-access is really the same as VB, but the forms are
different).

About the only real downfall of FoxPro is that there is not as large of a
user base, and there are more books, web sites, and newsgroups for
ms-access.

Further, you also realize that products like ms-access are not really a
database system anymore. ms-access is a front end tool that lets you develop reports, forms, and write code. The database engine is a separate issue.
When you purchase a database system like Oracle, or sql-server, you only get a box that lets you store data. You still have to write the application and the user interface using some tools. Those tools could be a web interface,
or VB or even ms-access.

Thus, one should keep in mind the separation between the database system, or so called database engine, and the application development tools you use.

Thus, one should view ms-access as a application development tool, and not a database. In fact, the office cd ships with two database engines. You can
choose to use either database engine with ms-access.

Both ms-access or FoxPro make good development tools that can connect to
your database engine of choice.

Microsoft has marketed the FoxPro environment more to developers, and
ms-access has been marketed more towards end users. Thus, ms-access is
considered easier to learn.

FoxPro also has some advantages in distribution of the final application.
However, those advantages are not that great if you use 3rd party tools for ms-access. Many people will argue that the database engine for FoxPro in a
file share is more reliable then the JET engine for ms-access in a file
share (however, every FoxPro developer I know has suffered some form of
index corruption).

So, if you are in need of a application development platform that is Object Orientated all the way, then FoxPro might be your ticket. If you need
something that will get you up and running a lot faster, then Ms-access is
probably the way to go.

Much will depending on your previous programming experience. If you don't
have a lot of development experience, then I would bet on ms-access.

However, don't count ms-access out as a serous development tool either. You can create class objects, and any project you attempt in ms-access still
should be planned in detail.

Here is a article of mine on using class objects to give you and idea of
what I mean:

http://www.attcanada.net/%7ekallal.m.../WhyClass.html
Also, here is some notes on a project I did in ms-access, you might again
find this a good read:

http://www.attcanada.net/%7ekallal.m...000000003.html

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[Steve]

On Sat, 22 Nov 2003 18:12:14 GMT, "Mike Thomas" <Mi******@Yahoo.com>
wrote:

There are a lot of
things I think are bad about Access: especially the "one big file" approach
which I think fosters file corruption and makes it diffuclt to isolate and
repair and what I think is the sloppiness in the referring to objects.

There is absolutely nothing wrong with storing the data in one big
file. But if that gives one concern, they can always place one table
in a MDB, and then link all the MDBs to a central one. But that is,
IMHO, is clearly an improper approach.

Regarding storing the program objects (e.g. forms, reports, modules,
queries, ...) one should use a seperate MDB. This is a standard
practice in Access development.

SQL Server and other high-end server database engines store all the
data (and triggers, procedures, etc ...) in one big "file".

Steven Zuch

[TC]

And your question is ... ?

TC
"Mike Thomas" <Mi******@Yahoo.com> wrote in message
news:2G**********************@newssvr28.news.prodi gy.com...

I've used FoxPro and Visual Fox Pro since 89' and Access since 98'. I
definitely prefer FoxPro for what I would call middle sized applications, 6+ users and maybe some larger tables, say over 500,000 records.

I think the programming language is clearer, I like the oo setup, the
ability to create controls and then subclass them, and the very direct way
of handling tables, both procedurally and with SQL. There are a lot of
things I think are bad about Access: especially the "one big file" approach which I think fosters file corruption and makes it diffuclt to isolate and
repair and what I think is the sloppiness in the referring to objects.

However, most of my work is now in Access for several reasons: there are
very few FoxPro programmers left, it looks like MSFT is finally going to
drop the product, corporate MIS people are very prejudiced against it, and
since almost everybody in the corporate world has heard of Access and maybe even has it on their computer, they tend to think that they "already have
the software".

There are a couple of things going for Access. It is great for writing
reports, I think the query builder works really well, and if you make a
successful Access application, it won't be too long before your customer
will want to upgrade to SQL Server and some sort of other front end, so it
tends to keep your customers coming back.

If I'd had my druthers, MSFT would have marketed VFP and it would be where
it should be, a great tool for mid-sized applications which are too
complicated for the casual user. But it has come to where there is no way I can recommend VFP to anybody, mainly because so few people use it anymore.

Mike Thomas


"Albert D. Kallal" <NO************@msn.com> wrote in message
news:73zlb.149533$pl3.127202@pd7tw3no...

I would have say that since you asked the question in a ms-access newsgroup,

then you kind of have to expect some leaning towards using ms-access.

The other issue is how much previous development experience you have. If

you

done a lot coding, and prefer a OO approach to application development,

then

you certainly might find FoxPro your cup of tea.

If you spend a lot of time coding in a language like VB, then you will

find

ms-access a lot easier to learn. (in fact, the programming language you

will

use to write code in ms-access is really the same as VB, but the forms are different).

About the only real downfall of FoxPro is that there is not as large of a user base, and there are more books, web sites, and newsgroups for
ms-access.

Further, you also realize that products like ms-access are not really a
database system anymore. ms-access is a front end tool that lets you

develop

reports, forms, and write code. The database engine is a separate issue.
When you purchase a database system like Oracle, or sql-server, you only

get

a box that lets you store data. You still have to write the application

and

the user interface using some tools. Those tools could be a web interface, or VB or even ms-access.

Thus, one should keep in mind the separation between the database system, or

so called database engine, and the application development tools you

use.
Thus, one should view ms-access as a application development tool, and not a

database. In fact, the office cd ships with two database engines. You

can choose to use either database engine with ms-access.

Both ms-access or FoxPro make good development tools that can connect to
your database engine of choice.

Microsoft has marketed the FoxPro environment more to developers, and
ms-access has been marketed more towards end users. Thus, ms-access is
considered easier to learn.

FoxPro also has some advantages in distribution of the final application. However, those advantages are not that great if you use 3rd party tools

for

ms-access. Many people will argue that the database engine for FoxPro in a file share is more reliable then the JET engine for ms-access in a file
share (however, every FoxPro developer I know has suffered some form of
index corruption).

So, if you are in need of a application development platform that is

Object

Orientated all the way, then FoxPro might be your ticket. If you need
something that will get you up and running a lot faster, then Ms-access is probably the way to go.

Much will depending on your previous programming experience. If you don't have a lot of development experience, then I would bet on ms-access.

However, don't count ms-access out as a serous development tool either.

You

can create class objects, and any project you attempt in ms-access still
should be planned in detail.

Here is a article of mine on using class objects to give you and idea of
what I mean:

http://www.attcanada.net/%7ekallal.m.../WhyClass.html
Also, here is some notes on a project I did in ms-access, you might again find this a good read:

http://www.attcanada.net/%7ekallal.m...000000003.html

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[Mike Thomas]

Well, as far as VFP goes its a dead issue anyway - but the "one big file"
approach in my opinion is ok for the data backend - I've never had a problem
there, but it seems very bad for the front end, where I have had a problem
with corruption. If the front end is going to be large and complicated I
would definitely stay away from Access and look at VB or .NET.

Mike Thomas
"Steve" <st***@nospam.com> wrote in message
news:3f****************@news.westnet.com...

On Sat, 22 Nov 2003 18:12:14 GMT, "Mike Thomas" <Mi******@Yahoo.com>
wrote:

There are a lot of
things I think are bad about Access: especially the "one big file" approachwhich I think fosters file corruption and makes it diffuclt to isolate andrepair and what I think is the sloppiness in the referring to objects.

There is absolutely nothing wrong with storing the data in one big
file. But if that gives one concern, they can always place one table
in a MDB, and then link all the MDBs to a central one. But that is,
IMHO, is clearly an improper approach.

Regarding storing the program objects (e.g. forms, reports, modules,
queries, ...) one should use a seperate MDB. This is a standard
practice in Access development.

SQL Server and other high-end server database engines store all the
data (and triggers, procedures, etc ...) in one big "file".

Steven Zuch

[TC]

If you've had a problem with corruption, wouldn't it be better to determine
the cause, rather than bagging the product? Lots of folks have big front-end
& do not get corruption.

TC
"Mike Thomas" <Mi******@Yahoo.com> wrote in message
news:4Q**********************@newssvr28.news.prodi gy.com...

Well, as far as VFP goes its a dead issue anyway - but the "one big file"
approach in my opinion is ok for the data backend - I've never had a problem there, but it seems very bad for the front end, where I have had a problem
with corruption. If the front end is going to be large and complicated I
would definitely stay away from Access and look at VB or .NET.

Mike Thomas
"Steve" <st***@nospam.com> wrote in message
news:3f****************@news.westnet.com...

On Sat, 22 Nov 2003 18:12:14 GMT, "Mike Thomas" <Mi******@Yahoo.com>
wrote:

There are a lot of
things I think are bad about Access: especially the "one big file" approachwhich I think fosters file corruption and makes it diffuclt to isolate andrepair and what I think is the sloppiness in the referring to objects.

There is absolutely nothing wrong with storing the data in one big
file. But if that gives one concern, they can always place one table
in a MDB, and then link all the MDBs to a central one. But that is,
IMHO, is clearly an improper approach.

Regarding storing the program objects (e.g. forms, reports, modules,
queries, ...) one should use a seperate MDB. This is a standard
practice in Access development.

SQL Server and other high-end server database engines store all the
data (and triggers, procedures, etc ...) in one big "file".

Steven Zuch

[Steve]

On Mon, 24 Nov 2003 13:37:04 GMT, "Mike Thomas" <Mi******@Yahoo.com>
wrote:

Well, as far as VFP goes its a dead issue anyway - but the "one big file"
approach in my opinion is ok for the data backend - I've never had a problem
there, but it seems very bad for the front end, where I have had a problem
with corruption. If the front end is going to be large and complicated I
would definitely stay away from Access and look at VB or .NET.

I have never had such problems, and have not heard of such problems
except for the case that the front-end is being shared (e.g. stored on
a server and used by multiple users at the same time). Well, I guess
I have now heard of a problem :)

On the contrary, I have found the front-end, which stores the forms,
reports, queries, and VBA modules, very stable and reliable. Like
most developers, I convert the production front-ends to MDE files.

I have a relatively complex commercial portfolio system, being used by
many hedge funds and a hedge fund administrator. And there are
larger systems (e.g. costing over $100,000) developed with Access
being the front-end. Such systems are extensive and complex, and
clearly are at the same scale as other high-end accounting systems.

There are valid reasons to select VB6 or VB.Net over Access for
development, but corruption is not one of them.

Steven R. Zuch
Cogent Management Inc.

[Bingo]

"TC" <a@b.c.d> wrote in news:1069724829.614179@teuthos:

If you've had a problem with corruption, wouldn't it be better
to determine the cause, rather than bagging the product? Lots of
folks have big front-end & do not get corruption.

TC
"Mike Thomas" <Mi******@Yahoo.com> wrote in message
news:4Q**********************@newssvr28.news.prodi gy.com...

Well, as far as VFP goes its a dead issue anyway - but the "one
big file" approach in my opinion is ok for the data backend -
I've never had a

problem

there, but it seems very bad for the front end, where I have
had a problem with corruption. If the front end is going to be
large and complicated I would definitely stay away from Access
and look at VB or .NET.

Mike Thomas
"Steve" <st***@nospam.com> wrote in message
news:3f****************@news.westnet.com...

> On Sat, 22 Nov 2003 18:12:14 GMT, "Mike Thomas"
> <Mi******@Yahoo.com> wrote:
>
> >There are a lot of
> >things I think are bad about Access: especially the "one big
> >file"

approach

> >which I think fosters file corruption and makes it diffuclt
> >to isolate

and

> >repair and what I think is the sloppiness in the referring
> >to objects.
> >
>
> There is absolutely nothing wrong with storing the data in
> one big file. But if that gives one concern, they can always
> place one table in a MDB, and then link all the MDBs to a
> central one. But that is, IMHO, is clearly an improper
> approach.
>
> Regarding storing the program objects (e.g. forms, reports,
> modules, queries, ...) one should use a seperate MDB. This
> is a standard practice in Access development.
>
> SQL Server and other high-end server database engines store
> all the data (and triggers, procedures, etc ...) in one big
> "file".
>
> Steven Zuch

Dolly Parton, for example ;) Personally, I don't have a problem
with OBF method, except that it's not self-maintaining. It grows
and grows until you Compact and Repair it. Seems a little sloppy to
me.