Access vs DBF...which to use?

#11

Thanks! Since this is such a simple application, there's no reason not to
just output an Access database as a .dbf file. I've tried it on a small test
and it works fine. So I don't need to know, or really care, what dBase is
about these days (I haven't used it in at least 10 years!). I'm basically
trying to find out if the files might be smaller, less subject to
corruption, faster searching, etc. The client couldn't care less since all
it is, is four fields per record being searched on one of them with the
results displayed on a web page. They just want it to work.

Thanks!

"Lyle Fairfield" <lylefair (AT) yahoo (DOT) com> wrote

Quote:

"Mad Dog" <md (AT) mdp (DOT) com> wrote in
news

PIUa.494$mr1.322 (AT) newsread3 (DOT) news.pas.earthlink.net:

I'm the original poster. I hate to break up this conversation, but does
anyone have a real answer to my question? I was trying to find out the
pros and cons of using Access vs DBF files for searching on a web
site(assuming I'm only searching one field and not writing to the file),
such as speed, file size, reliability, etc.

I've considered using a DBF file for what you are suggesting, but have not
done so because I've forgotten too much about DBF files over the years,
and I've not kept up with their changes (I don't know if there have been
any).

The DBF files I remember are very simple. Essentially they are a text file
with a header indicating record and field length.

Indexes are separate files, and in some ways are more powerful than JET
indexes. For instance, one can build conditional indexes: Index on
Surname, FirstName for City = Toronto to TorontoIndex; When one sets the
Index to TorontoIndex one has a small Index to use; in some cases this can
be very fast.

I suppose in trying to sell such an application, I am concerned about
clients who would think a DBF based solution is archaic.

One of the great things about DBF files that I have found over the years
is that a file recovery utility will almost always recover all ones data
even if you lose the file. Corruption seems to be limited to Memo fields;
the pointers for which can get out of whack, but this is infrequent.

--
Lyle

#12

Mad Dog wrote:

Quote:

It's for a web site and it's very straight forward: four fields, one will be
searched on. Using Access of course I can output it as .mdb or .dbf and use
whichever works best. If indeed one is better than the other.

- I guess what I'm really trying to find out is, if the dbf file is a
smaller (which it is), does that mean it will run faster?

- Does it have the problems you can have with multiple users in Access
(though I don't expect that to be a factor)?

- With all I hear about compacting and repairing Access databases, are dbf
ones less likely to corrupt?

Thanks for the help!

If you use a DBF, a consideration may be the index. In Access, you have

the AutoNumber to use to make a record unique. In a dbf, you can add
duplicate records. Although you can set up an index to display only
unique records duplicates can still exist.

#13

The fingers shake. The keys are struck. But the brain, like Elvis, has left
the building.

--
Lyle

#14

Thanks, that does help a bit. This wouldn't be user-updated, only by me.
It's roundabout, but the client is working with Filemaker Pro, so I'd take
that, output it as .dbf and either use it as is or import it into Access.
Everything I hear about Access dbs corrupting so much sounds like a pain.
Needless to say, I want it to be as fast as possible and as painless as
possible.

Thanks!

"Stan Schaefer" <Stans4 (AT) americanisp (DOT) net> wrote

Quote:

"Mad Dog" <md (AT) mdp (DOT) com> wrote

It's for a web site and it's very straight forward: four fields, one
will be
searched on. Using Access of course I can output it as .mdb or .dbf and
use
whichever works best. If indeed one is better than the other.

- I guess what I'm really trying to find out is, if the dbf file is a
smaller (which it is), does that mean it will run faster?

- Does it have the problems you can have with multiple users in Access
(though I don't expect that to be a factor)?

- With all I hear about compacting and repairing Access databases, are
dbf
ones less likely to corrupt?

Thanks for the help!

The only .dbf files I've used have been with Foxpro and VFP and never
with any web app. Given the dataset size you've mentioned, I doubt
you'll see any difference in speeds, although VFP is pretty fast with
large datasets. VFP has no problems with large numbers of users, it's
not related to the type of file structure underneath but rather the
database engine that your apps talk to. I've had a couple of
instances of VFP indexes being damaged in the last 8 years, copying a
backup copy of the index file followed by reindexing the data files
fixed it, nothing was lost. These happened after server drive
failures. They've weathered power outages, people just turning their
machines off, LAN people cutting cables and the like, without any
problems.

In the last 8 years, I've had a lot of troubles with Access
corruption, mostly caused by people turning their machines off without
logging out, sometimes a database just goes corrupt for no apparent
reason. At least once or twice a week, sometimes more. Some of the
VFP app files go a year or more without being packed and reindexed,
very low maintenance once the app is built and deployed.

I've never used .dbfs in an update capacity from Access, in earlier
versions, you couldn't do that and I'm not sure you can do it with the
latest version, haven't tried it. I do use them for a lot of linked
query-only Access databases, they tend to be slower since the files
are linked and not in the Access database.

Hope this helps

Stan

#15

Stan Schaefer wrote:

Quote:

In the last 8 years, I've had a lot of troubles with Access
corruption, mostly caused by people turning their machines off without
logging out, sometimes a database just goes corrupt for no apparent
reason. At least once or twice a week, sometimes more.

In your isolated case, I would abandon Access and stay with FoxPro.

#16

Mad Dog,

Go ahead and use the dbf's as your tables or ( back end tables.) Use
Access as you normally would or as a front end, then link the dbf
files. The number of records you speak of is quite insignificant.
I've used this set up (dbf tables) with over 20,000 records since
Access 97 first came out and I never had any problems with those
clients who have them. (Some clients still use some of my old Clipper
programs to manipulate the dbs files and they refuse to update to
Windows / Access , preferring their DOS-based interface. This allows
others in their offices who are comfortable with Access, to use Access
and those who are not, to use whatever front end they desire.

dbf's are smaller in size then mdb's, however, compacting your
database regularly will keep with the file small. Another
consideration is that the index files for dbf can become quite large
and greatly increase the size of your database.

Using this system, allows those with FoxPro, or any other DOS-based
program to use the dbf, and allows those using Access to do the same.

While I don't normally attach the ndx (dBase) or ntx (Clipper) index
files, I suggest you do so in order to maintain data integrity with
those not using the Access front end. I find that querying the
record set (creating and working with queries), makes data
manipulation a bit faster. Otherwise, there is no significant
difference between using the dbf's or converting to an mdb table. The
ntx files can become quite large, however, they work at lightning
speed and as others have indicated, are quite robust.

Why don't I use the index files in Access you ask? The Clipper
programs automatically detect which files have been updated and
require re indexing at every start-up. Those indexes that need
rebuilding, are quickly and automatically re indexed. While some may
question the reliability of this, it has proven to be bullet proof for
my clients. None have ever experienced any loss of data or corrupted
data since Access 97 first arrived on the scene.

rr

On Mon, 28 Jul 2003 20:56:04 GMT, "Mad Dog" <md (AT) mdp (DOT) com> wrote:

Quote:

Thanks, that does help a bit. This wouldn't be user-updated, only by me.
It's roundabout, but the client is working with Filemaker Pro, so I'd take
that, output it as .dbf and either use it as is or import it into Access.
Everything I hear about Access dbs corrupting so much sounds like a pain.
Needless to say, I want it to be as fast as possible and as painless as
possible.

Thanks!

"Stan Schaefer" <Stans4 (AT) americanisp (DOT) net> wrote in message
news:20e34ee4.0307281231.1a7c911 (AT) posting (DOT) google.com...
"Mad Dog" <md (AT) mdp (DOT) com> wrote in message
news:<GgSUa.652$mr1.364 (AT) newsread3 (DOT) news.pas.earthlink.net>...
It's for a web site and it's very straight forward: four fields, one
will be
searched on. Using Access of course I can output it as .mdb or .dbf and
use
whichever works best. If indeed one is better than the other.

- I guess what I'm really trying to find out is, if the dbf file is a
smaller (which it is), does that mean it will run faster?

- Does it have the problems you can have with multiple users in Access
(though I don't expect that to be a factor)?

- With all I hear about compacting and repairing Access databases, are
dbf
ones less likely to corrupt?

Thanks for the help!

The only .dbf files I've used have been with Foxpro and VFP and never
with any web app. Given the dataset size you've mentioned, I doubt
you'll see any difference in speeds, although VFP is pretty fast with
large datasets. VFP has no problems with large numbers of users, it's
not related to the type of file structure underneath but rather the
database engine that your apps talk to. I've had a couple of
instances of VFP indexes being damaged in the last 8 years, copying a
backup copy of the index file followed by reindexing the data files
fixed it, nothing was lost. These happened after server drive
failures. They've weathered power outages, people just turning their
machines off, LAN people cutting cables and the like, without any
problems.

In the last 8 years, I've had a lot of troubles with Access
corruption, mostly caused by people turning their machines off without
logging out, sometimes a database just goes corrupt for no apparent
reason. At least once or twice a week, sometimes more. Some of the
VFP app files go a year or more without being packed and reindexed,
very low maintenance once the app is built and deployed.

I've never used .dbfs in an update capacity from Access, in earlier
versions, you couldn't do that and I'm not sure you can do it with the
latest version, haven't tried it. I do use them for a lot of linked
query-only Access databases, they tend to be slower since the files
are linked and not in the Access database.

Hope this helps

Stan

#17

Thanks for the thorough and good explanation!

"rr" <roadrunner (AT) roadrunner (DOT) net> wrote

Quote:

Mad Dog,

Go ahead and use the dbf's as your tables or ( back end tables.) Use
Access as you normally would or as a front end, then link the dbf
files. The number of records you speak of is quite insignificant.
I've used this set up (dbf tables) with over 20,000 records since
Access 97 first came out and I never had any problems with those
clients who have them. (Some clients still use some of my old Clipper
programs to manipulate the dbs files and they refuse to update to
Windows / Access , preferring their DOS-based interface. This allows
others in their offices who are comfortable with Access, to use Access
and those who are not, to use whatever front end they desire.

dbf's are smaller in size then mdb's, however, compacting your
database regularly will keep with the file small. Another
consideration is that the index files for dbf can become quite large
and greatly increase the size of your database.

Using this system, allows those with FoxPro, or any other DOS-based
program to use the dbf, and allows those using Access to do the same.

While I don't normally attach the ndx (dBase) or ntx (Clipper) index
files, I suggest you do so in order to maintain data integrity with
those not using the Access front end. I find that querying the
record set (creating and working with queries), makes data
manipulation a bit faster. Otherwise, there is no significant
difference between using the dbf's or converting to an mdb table. The
ntx files can become quite large, however, they work at lightning
speed and as others have indicated, are quite robust.

Why don't I use the index files in Access you ask? The Clipper
programs automatically detect which files have been updated and
require re indexing at every start-up. Those indexes that need
rebuilding, are quickly and automatically re indexed. While some may
question the reliability of this, it has proven to be bullet proof for
my clients. None have ever experienced any loss of data or corrupted
data since Access 97 first arrived on the scene.

rr

On Mon, 28 Jul 2003 20:56:04 GMT, "Mad Dog" <md (AT) mdp (DOT) com> wrote:

Thanks, that does help a bit. This wouldn't be user-updated, only by me.
It's roundabout, but the client is working with Filemaker Pro, so I'd
take
that, output it as .dbf and either use it as is or import it into Access.
Everything I hear about Access dbs corrupting so much sounds like a pain.
Needless to say, I want it to be as fast as possible and as painless as
possible.

Thanks!

"Stan Schaefer" <Stans4 (AT) americanisp (DOT) net> wrote in message
news:20e34ee4.0307281231.1a7c911 (AT) posting (DOT) google.com...
"Mad Dog" <md (AT) mdp (DOT) com> wrote in message
news:<GgSUa.652$mr1.364 (AT) newsread3 (DOT) news.pas.earthlink.net>...
It's for a web site and it's very straight forward: four fields, one
will be
searched on. Using Access of course I can output it as .mdb or .dbf
and
use
whichever works best. If indeed one is better than the other.

- I guess what I'm really trying to find out is, if the dbf file is a
smaller (which it is), does that mean it will run faster?

- Does it have the problems you can have with multiple users in
Access
(though I don't expect that to be a factor)?

- With all I hear about compacting and repairing Access databases,
are
dbf
ones less likely to corrupt?

Thanks for the help!

The only .dbf files I've used have been with Foxpro and VFP and never
with any web app. Given the dataset size you've mentioned, I doubt
you'll see any difference in speeds, although VFP is pretty fast with
large datasets. VFP has no problems with large numbers of users, it's
not related to the type of file structure underneath but rather the
database engine that your apps talk to. I've had a couple of
instances of VFP indexes being damaged in the last 8 years, copying a
backup copy of the index file followed by reindexing the data files
fixed it, nothing was lost. These happened after server drive
failures. They've weathered power outages, people just turning their
machines off, LAN people cutting cables and the like, without any
problems.

In the last 8 years, I've had a lot of troubles with Access
corruption, mostly caused by people turning their machines off without
logging out, sometimes a database just goes corrupt for no apparent
reason. At least once or twice a week, sometimes more. Some of the
VFP app files go a year or more without being packed and reindexed,
very low maintenance once the app is built and deployed.

I've never used .dbfs in an update capacity from Access, in earlier
versions, you couldn't do that and I'm not sure you can do it with the
latest version, haven't tried it. I do use them for a lot of linked
query-only Access databases, they tend to be slower since the files
are linked and not in the Access database.

Hope this helps

Stan

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Access vs DBF...which to use?

comp.databases.ms-access comp.databases.ms-access

Re: Access vs DBF...which to use? - 07-27-2003 , 10:36 AM

Re: Access vs DBF...which to use? - 07-27-2003 , 12:01 PM

Re: Access vs DBF...which to use? - 07-27-2003 , 08:59 PM

Re: Access vs DBF...which to use? - 07-28-2003 , 03:56 PM

Re: Access vs DBF...which to use? - 07-28-2003 , 07:39 PM

Re: Access vs DBF...which to use? - 07-29-2003 , 03:18 AM

Re: Access vs DBF...which to use? - 07-29-2003 , 10:42 AM