Access Issues

[Terry]

All:

I am trying to automate an existing system that currently has about 138
queries and has 4 data sources.

2 of the data sources as supplied as excel files that I import into tables
withing access. Once these 2 tables are imported I end up with a DB with a
size of 162MB.

The other 2 data sources are supplied as text files that are 684MB and 711MB
respectively. These files are just linked due to their sizes.

My problem comes in when I try to access the large files to many times in a
row. Access starts returning Data Mismatch errors. I can delete the linked
file and copy in a fresh version of it and the error goes away until I again
try to access it to many times in a row. I am never updating the files, they
are used for read only purpose to build a recordset that I then use to
populate an Excel spreadsheet.

I would appreciate any suggestions that are provided. I have a feeling that
while processing the files to build the recordset I am approaching a size
limitation. In one query I process just under 600,000 records to produce 50
records. I need to process these 600,000 records in a total of 19 different
queries and the same for the second large file.

thanks

Terry

 

[Maurice]

This might be a good time to consider upsizing to a SQL-server backend
(Express edition could be a next step). You can keep your Access front end.

 

[Terry]

I have been considering that. But instead of a SQL-Server backend the
company I support uses a Terradata server.

I don't think access really likes using these large files plus the desktop
system I was given only has 2 gig of memory. I have tried working with just
the 2 smaller tables and no problems, but as soon as I start working with the
large files the system becomes unstable after only a couple of runs with the
large tables.

thanks

Terry

 

[Chris L.]

I have been considering that. But instead of a SQL-Server backend the
company I support uses a Terradata server.

I don't think access really likes using these large files plus the desktop
system I was given only has 2 gig of memory. I have tried working with just
the 2 smaller tables and no problems, but as soon as I start working with the
large files the system becomes unstable after only a couple of runs with the
large tables.

thanks

Terry






- Show quoted text -

Maybe you could import the 600,000 records you need, instead of
linking the whole TXT files. Further, when you import, maybe you can
leave out unneeded columns (if there are any).

By importing the records into a table, you can define indexes on it,
which should also speed up your process considerably.

 

[Terry]

Once my customer gets back from vacation I will approach her with that. I
attempted to import one of the txt files and it jumped my DB upto 1.7 gigs.
I do see alot of columns in the file that I am not sure if she is using also.

thanks

Terry

 

[Arvin Meyer [MVP]]

Since the files are linked, SQL-Server will not provide any improvement
without importing them into that. Then you have the same problem, but linked
to SQL-Server. My guess is that you are overtaxing the ODBC driver.

I have a huge dBaseIII database that had a similar problem. I built a new
table and an append query to fill it, and a delete query to clean it out.
Every morning when the database runs, it reduces 40 GB of data to about 8 MB
for the roughly 18,000 records that I need for the day's work. I can close
and reopen the database but it won't rerun the queries if they've already
been done for that day. I have an override button that deletes the date
entry and will refresh the data if necessary.

Each user does it locally because I'd prefer that they only each tax the
network only once for each of the 5 users on the system. Although it takes a
few minutes to run the first time, it runs everything within a second after
that.

 

[Arvin Meyer [MVP]]

Set your database to compact on close to make sure that it is minimum size.