Design Help needed

[rick]

Hi,
I have an Access 2k db that I will be deploying on several
workstations. Since linking to a centralized backend db
is NOT an option, and since the data only needs to be
viewed and can't be edited in any way, I bring in a new
data set each night with a simple three step process: 1) I
run a batch file to get 2 text files from an ftp site 2) I
run a delete query that deletes all of the records from
two tables 3) I then run a transfertext procedure that
appends all of the records to the two tables. Everything
works great. However, I have been reading that
transfertext causes database bloat so I was wondering if
my design here will be ok over time. I do have the
Compact on Close box checked so the db does not swell
permanently, but is deleting 40,000 records each night
going to cause any problems? Thanks for any help,
-Rick

 

[John Vinson]

is deleting 40,000 records each night
going to cause any problems?

Yes. None of the space occupied by those deleted records will be freed
until you Compact.

You might want to consider downloading a backend .mdb file (perhaps
zipped) and completely replacing the existing backend database, rather
than importing the files; if you don't do so, regular compaction will
be essential.

 

[Rick]

Thanks for the reply. The problem is that the data
originates from two text files (downloaded from a
mainframe) and at some point, I have to import the files
into access. I suppose I could establish links to the
text files, and just overwrite the text files each night.
Would this be better? Thanks,
-Rick

 

[TC]

It's not very attractive (from a design perspective) to continually delete &
reload records; but if you compact on close each night, I can't see there
being a problem.

HTH,
TC

 

[John Vinson]

Thanks for the reply. The problem is that the data
originates from two text files (downloaded from a
mainframe) and at some point, I have to import the files
into access. I suppose I could establish links to the
text files, and just overwrite the text files each night.
Would this be better? Thanks,

It sounds like you're between a rock and a hard place! Linked Text
files cannot be updated, for one thing; they cannot be indexed; and
queries against them will be much slower than against native Access
tables.

You might want to consider either compacting nightly (even though it's
a hassle), or storing these two tables in a separate backend database
which you destroy and recreate each night.