HI Dave! Depends on the version of JET. BTW, Access itself is not a database, It's a frontend whose default db engine is JET(the MDE file). Access can be used also as a frontend to ODBC compliant databases, such as Firebird/Interbase, SQL Server, MySQL, etc. HI. Depends on the version of JET. BTW, Access itself is not a database, It's a frontend whose default db engine is JET(the MDE file). Access can be used also as a frontend to ODBC compliant databases, such as Firebird/Interbase, SQL Server, MySQL, etc. The version of JET that came with Access97 could handle up to 1GB of data; Access 2000-2003 up to 2 GB. So 600,000 records don't really sound like a lot to me. Since you said there are a lot of queries run against the db, the MDE file itself is used as a workplace, and that cruft doesn't get automatically cleared out. Probably what you need to do periodically is run a Compact and Repair. You'll have to look that up. Full Access has a menu selection to do that. Be SURE you back you data up first! This will reduce the size of your db and probably speed things up a bit. Long term, however, 12,000 to 15,000 transactions per week is a little scary. If you're running that many transactions and have more than 15-20 users at a time updating the db, it's probably time to upgrade to SQL Server, Firebird/Interbase(should have come with your Delphi?), MySQL, or some other db engine. Just upgrading to Delphi 6 and ADO is only going to be a short term solution if you don't replace your db engine. Take a look at comp.databases.ms-access in google groups. Lots of good info there, and you can find some links and FAQ's on this website http://www.granite.ab.ca/accsmstr.htm that cover your predicament. Also check out http://www.mvps.org/access/resources/index.html Granted this is all Access, and you're a Delphi user, but there's a LOT of info about JET, and how to upgrade/upsize from a JET db to other SQL db's. HTH Ruben Baumann
The version of JET that came with Access97 could handle up to 1GB of data; Access 2000-2003 up to 2 GB. So 600,000 records don't really sound like a lot to me. Since you said there are a lot of queries run against the db, the MDE file itself is used as a workplace, and that cruft doesn't get automatically cleared out. Probably what you need to do periodically is run a Compact and Repair. You'll have to look that up. Full Access has a menu selection to do that. Be SURE you back you data up first! This will reduce the size of your db and probably speed things up a bit. Long term, however, 12,000 to 15,000 transactions per week is a little scary. If you're running that many transactions and have more than 15-20 users at a time updating the db, it's probably time to upgrade to SQL Server, Firebird/Interbase(should have come with your Delphi?), MySQL, or some other db engine. Just upgrading to Delphi 6 and ADO is only going to be a short term solution if you don't replace your db engine. Take a look at comp.databases.ms-access in google groups. Lots of good info there, and you can find some links and FAQ's on this website http://www.granite.ab.ca/accsmstr.htm that cover your predicament. Also check out http://www.mvps.org/access/resources/index.html Granted this is all Access, and you're a Delphi user, but there's a LOT of info about JET, and how to upgrade/upsize from a JET db to other SQL db's. HTH Ruben Baumann Dave Murray <[EMAIL PROTECTED]> wrote: Hi, I've got a Delphi app that uses an Access database via BDE (Delphi 4 so no ADO). At the moment there are about 600,000 records in the main table and it is growing by about 12,000 - 15,000 records a week. The database itself is currently over 30Mb in size. The app runs a lot of queries against the main table and has been slowing down as it grows. The speed is now getting to the point where the users are complaining. So am I reaching the limits of what I can do with an Access database? Would I get a speed increase by switching to Delphi 2006 and using ADO? Is it time to start looking at SQL Server? Thanks, Dave. Dave Murray Delphi-En Moderator Glasgow, UK [EMAIL PROTECTED] ----------------------------------------------------- Home page: http://groups.yahoo.com/group/delphi-en/ To unsubscribe: [EMAIL PROTECTED] Yahoo! Groups Links When it hurts to look back, and you're scared to look ahead, you can look beside you and your BEST FRIEND will be there. God so loved the world that he gave his only son, so that whoever believes in him will not die, but have life eternal. John 3:16 --------------------------------- Yahoo! Mail Use Photomail to share photos without annoying attachments. [Non-text portions of this message have been removed] ----------------------------------------------------- Home page: http://groups.yahoo.com/group/delphi-en/ To unsubscribe: [EMAIL PROTECTED] Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/delphi-en/ <*> To unsubscribe from this group, send an email to: [EMAIL PROTECTED] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
