[RESOLVED] - Access 2003 indexing problem

[erickwidya]

we got Access that link to another access and use query...the problem is the
it got an error that said "exceed more than 2GB temp, ...."

the query takes so long and it takes more than 2GB temp file when we checked..after several tuning, we know the problem because of indexing in one of the line table that the query takes..after we remove the index..it runs smoothly..

but, 1 day after..the query stump again and we saw that the index is created again even though we already delete it the day before

why it happened? any insight?

thanks,
erick

[leinad31]

Migrate to a database more suited to your usage. Based on your 2GB volume, you are probably trying to use Access as a backend for a DSS/reporting system (data mart like app)... this is technically not appropriate.

[erickwidya]

Migrate to a database more suited to your usage. -> any other insight if user doesn't want to migrate?

the Access is used for front end as well as backend, they have their application inside it (reporting, form, query, etc)

[ShapeShiftme]

Migrate to a database more suited to your usage. -> any other insight if user doesn't want to migrate?

the Access is used for front end as well as backend, they have their application inside it (reporting, form, query, etc)

Perhaps its time to quote on a new development package. Ive noticed with most my clients. When they originall started an idea they used acces for ease. and at the begining it was fast. But over time itś slows way down.

So now they will have to consider an upgrade It will overall be a big improvment. but will come at a big expence.

Access is just not desighned to handle files of that size

Agrred that all of the above is my opinion. And sorry but i dont think the post was very helpfull either. But rather get them to change now as the problems will just keep getting worse as time progresses

Regards

[erickwidya]

ok, i think the only solution is to upgrade, nothing else...

thanks leinad31 and ShapeShiftme

[leinad31]

You can easily download trial versions of Oracle Express or SQL Server Express, then rebuild tables and finally import data from Access... only change in application would (or rather should) only be the connection string... that way you can do proof of concept in order to ease client concern.

[erickwidya]

it's not that easy leinad

the form, reports, etc is inside the Access itself..

[leinad31]

Move data warehousing like reports/forms out of access into a database designed for such (ETL), maintain OLTP transactions for the time being. That way you can phase the migration.

[erickwidya]

thanks for the input leinad