I'm dealing with a growing database in Access 97 which I access via ODBC and a View in VFP 6.0 Since the database is growing by 100-200 records per day, performance has started to sag very noticeably. The way this works is when my application starts, it copies the .MDB to a working folder using a batch file. I can then pretty much mangle the MDB anyway I want since it doesn't touch the original, however I have no control on how the .MDB is created and maintained.
The real issue is that searching the MDB is getting really slow. Can you index a VIEW? We're talking about 7,000 records right now; I was even wondering about trying to add an additional index within the .MDB using FoxPro to control JET via automation. Or maybe just do a complete SQL-SELECT into a FoxPro cursor and then do all of the seaching from there? A delay on start-up is OK, but I would really like to shorten the wait for the users once they are in my program.
This has come up with a couple other projects as well, as I generally use VFP to do reporting anytime I run into .mdb files
Any ideas?
--- L
-- Larry Keyes
Remember only You can prevent Gray Goo. Never release nanobot assembers without replication limiting code.