A Note To Access Developers [Resolved]
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
A Note To Access Developers: Stop using that piece of crap 'software' and switch to a real programming environment.
Everything makes sense in someone's mind
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
Did you compact/repair the database prior to working with the tables. Access won't let you delete records in a corupted database unless you compact/repair it first. Worth a try I think.
-
A Note To Access Developers: Stop using that piece of crap 'software' and switch to a real programming environment.
Everything makes sense in someone's mind
Kevin Marois wrote:
Stop using that piece of crap 'software'
I second that.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer "Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon "Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
Access with 2.8 million records... That's pretty impressive... :)
I wasn't, now I am, then I won't be anymore.
-
Did you compact/repair the database prior to working with the tables. Access won't let you delete records in a corupted database unless you compact/repair it first. Worth a try I think.
I believe you're correct, but the new 2007 Diagnose and Repair feature doesn't detect any problem, though one of the tables is reported as not being a format recognized by Access. It's clearly corrupted, and I can't even open it to see the schema. I may have to go back to the original supplier to have the db recreated, but I'd really rather not. That's going to cost $$$.
Will Rogers never met me.
-
I believe you're correct, but the new 2007 Diagnose and Repair feature doesn't detect any problem, though one of the tables is reported as not being a format recognized by Access. It's clearly corrupted, and I can't even open it to see the schema. I may have to go back to the original supplier to have the db recreated, but I'd really rather not. That's going to cost $$$.
Will Rogers never met me.
Access 2007 can't compact/repair a Access 2000 database. You have to create a new Access 2007 database, import "all" objects from old db into the new one and then compact/repair. Problem should fix itself. However, your program is probably using components/dll's that only work with the .mdb file format. Access 2007 is .accdb format and a different engine alltogether. You will most likely have to have the original software company make the changes as you have already sugested. Good luck...I know Access is a pain. That is why I am trying to move all my database stuff (mostly personal/test/dev) to SQL Server 2005/2008 Express...2005 right now and then next year to 2008. HTH.
-
Access with 2.8 million records... That's pretty impressive... :)
I wasn't, now I am, then I won't be anymore.
Access has a 2 gig database size limit...but it really is about 1.5 gig minus the overhead it uses.
-
Access 2007 can't compact/repair a Access 2000 database. You have to create a new Access 2007 database, import "all" objects from old db into the new one and then compact/repair. Problem should fix itself. However, your program is probably using components/dll's that only work with the .mdb file format. Access 2007 is .accdb format and a different engine alltogether. You will most likely have to have the original software company make the changes as you have already sugested. Good luck...I know Access is a pain. That is why I am trying to move all my database stuff (mostly personal/test/dev) to SQL Server 2005/2008 Express...2005 right now and then next year to 2008. HTH.
That gives me an idea! I've got Access 2003 lying around here somewhere; maybe if I install it on my server I can repair it there. :-D
Will Rogers never met me.
-
That gives me an idea! I've got Access 2003 lying around here somewhere; maybe if I install it on my server I can repair it there. :-D
Will Rogers never met me.
Roger Wright wrote:
I've got Access 2003 lying around here somewhere
I think you are on to something. Just remember to backup the original (corrupted) database. Compacting/repairing a corrupted database can do weird things sometimes.
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
First of all, I assume you've already checked other backups of this database to see if any of them work? Depending on the corruption, even if your other backup is old you may still want to use it and just import the newer records over. I've had lots of experience with fixing broken Access databases, including those that cannot be repaired by the built-in Access routines. You're going the right direction with the import idea. Here's what I'd recommend: 1. Make 100% certain you have at least 1 good backup copy before doing anything and do all of the following operations on copies of the original. 2. Work on a clean (lots of free disk space, recently defragged, and recently scanned for block-level errors) local drive; any processes over the network, especially with a db that size, is not only going to be slow but may be subject to network issues (which may have caused the corruption in the first place; I had a client a decade ago that had their db going corrupt almost weekly until we convinced them to replace a network card) 3. Do a table import to fresh Access db; be sure to include relationships during the import. If it craps out on one table then start the import over again with everything except that table (you want to narrow down whether the corruption is just in a single table). 4. I had a problem once where I couldn't import or even open the table, but I was able to actually read in from within a recordset. I believe this is related to the jet record paging process (if you open the table in the UI then its accessing a ton of records at once, but if you open through a recordset it's accessing only a page at a time). Using that method I was able to import record by record until hitting the corrupt record. But with some basic error handling I could skip over the bad record(s) and continue on. You end up losing some records, but that's kind of inevitable at this point, right? As a side note, I'm not going to jump on the "Access sucks" bandwagon. I've coded in Access, Visual Studio, and many others (going back to Basic and my good ol TI-994A). I have seen absolute crap written in Access, but I've also seen absolute crap written in C# - in most cases it comes down to "blame the developer, not the platform". That being said, Access definitely has its limitations, but I find that the people that complain about it don't truly know enough about it (and people that are complaining about Jet datastore, not Access). The scariest thing about Access tends to be people with NO development background (or e
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
You can try up-sizing it to SqlServer.
-
First of all, I assume you've already checked other backups of this database to see if any of them work? Depending on the corruption, even if your other backup is old you may still want to use it and just import the newer records over. I've had lots of experience with fixing broken Access databases, including those that cannot be repaired by the built-in Access routines. You're going the right direction with the import idea. Here's what I'd recommend: 1. Make 100% certain you have at least 1 good backup copy before doing anything and do all of the following operations on copies of the original. 2. Work on a clean (lots of free disk space, recently defragged, and recently scanned for block-level errors) local drive; any processes over the network, especially with a db that size, is not only going to be slow but may be subject to network issues (which may have caused the corruption in the first place; I had a client a decade ago that had their db going corrupt almost weekly until we convinced them to replace a network card) 3. Do a table import to fresh Access db; be sure to include relationships during the import. If it craps out on one table then start the import over again with everything except that table (you want to narrow down whether the corruption is just in a single table). 4. I had a problem once where I couldn't import or even open the table, but I was able to actually read in from within a recordset. I believe this is related to the jet record paging process (if you open the table in the UI then its accessing a ton of records at once, but if you open through a recordset it's accessing only a page at a time). Using that method I was able to import record by record until hitting the corrupt record. But with some basic error handling I could skip over the bad record(s) and continue on. You end up losing some records, but that's kind of inevitable at this point, right? As a side note, I'm not going to jump on the "Access sucks" bandwagon. I've coded in Access, Visual Studio, and many others (going back to Basic and my good ol TI-994A). I have seen absolute crap written in Access, but I've also seen absolute crap written in C# - in most cases it comes down to "blame the developer, not the platform". That being said, Access definitely has its limitations, but I find that the people that complain about it don't truly know enough about it (and people that are complaining about Jet datastore, not Access). The scariest thing about Access tends to be people with NO development background (or e
G-Tek wrote:
The scariest thing about Access tends to be people with NO development background (or even basic understanding of RDBMS) building solutions; MS including it as part of Office obviously encourages blatant misuse.
Isn't this the fundament problem with Software development in general. The whole industy has been compromised by people with vey little knowledge or experience in software development; but with access (no pun intended) to tools and technologies they would be better-off leaving alone? I can't pick up a scalpel and call myself a surgeon (whilst hacking into people); but I can start MS-Word, hit ALT-F11 and call myself a programmer :mad: rant over... no it didn't help anyone... I'll be at the bar if you need me... merry christmas...
TEAMWORK A few harmless flakes working together can unleash an avalanche of destruction.| Despair Inc.
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
You have the temerity to criticise production database system that has been working for "a few years" without problem and without the need for expensive, per user licensing! All hail the mighty Access, god of all databases say I!
www.it-workplace.com
"If a man speaks in a forest where there is no woman to hear him, is he still wrong?" -
First of all, I assume you've already checked other backups of this database to see if any of them work? Depending on the corruption, even if your other backup is old you may still want to use it and just import the newer records over. I've had lots of experience with fixing broken Access databases, including those that cannot be repaired by the built-in Access routines. You're going the right direction with the import idea. Here's what I'd recommend: 1. Make 100% certain you have at least 1 good backup copy before doing anything and do all of the following operations on copies of the original. 2. Work on a clean (lots of free disk space, recently defragged, and recently scanned for block-level errors) local drive; any processes over the network, especially with a db that size, is not only going to be slow but may be subject to network issues (which may have caused the corruption in the first place; I had a client a decade ago that had their db going corrupt almost weekly until we convinced them to replace a network card) 3. Do a table import to fresh Access db; be sure to include relationships during the import. If it craps out on one table then start the import over again with everything except that table (you want to narrow down whether the corruption is just in a single table). 4. I had a problem once where I couldn't import or even open the table, but I was able to actually read in from within a recordset. I believe this is related to the jet record paging process (if you open the table in the UI then its accessing a ton of records at once, but if you open through a recordset it's accessing only a page at a time). Using that method I was able to import record by record until hitting the corrupt record. But with some basic error handling I could skip over the bad record(s) and continue on. You end up losing some records, but that's kind of inevitable at this point, right? As a side note, I'm not going to jump on the "Access sucks" bandwagon. I've coded in Access, Visual Studio, and many others (going back to Basic and my good ol TI-994A). I have seen absolute crap written in Access, but I've also seen absolute crap written in C# - in most cases it comes down to "blame the developer, not the platform". That being said, Access definitely has its limitations, but I find that the people that complain about it don't truly know enough about it (and people that are complaining about Jet datastore, not Access). The scariest thing about Access tends to be people with NO development background (or e
G-tek, I agree with everything you have said here. I have worked in Access since 1993 and with good code, Access works very well. Of course, one needs to keep the database "small" rather than thinking bigger is better. But with a good education in Access, the competent developer will help their customer pick the correctly sized database system for the customers solution. But, when the customer uses users to write their Access database, the majority of the time, they get the amateur solution that does not work well, and then Access gets blamed.
-
You have the temerity to criticise production database system that has been working for "a few years" without problem and without the need for expensive, per user licensing! All hail the mighty Access, god of all databases say I!
www.it-workplace.com
"If a man speaks in a forest where there is no woman to hear him, is he still wrong?":laugh: When I first started using Access, it wasn't free to distribute - you had to buy a copy of the runtime for each app you distributed. ;P
Will Rogers never met me.
-
First of all, I assume you've already checked other backups of this database to see if any of them work? Depending on the corruption, even if your other backup is old you may still want to use it and just import the newer records over. I've had lots of experience with fixing broken Access databases, including those that cannot be repaired by the built-in Access routines. You're going the right direction with the import idea. Here's what I'd recommend: 1. Make 100% certain you have at least 1 good backup copy before doing anything and do all of the following operations on copies of the original. 2. Work on a clean (lots of free disk space, recently defragged, and recently scanned for block-level errors) local drive; any processes over the network, especially with a db that size, is not only going to be slow but may be subject to network issues (which may have caused the corruption in the first place; I had a client a decade ago that had their db going corrupt almost weekly until we convinced them to replace a network card) 3. Do a table import to fresh Access db; be sure to include relationships during the import. If it craps out on one table then start the import over again with everything except that table (you want to narrow down whether the corruption is just in a single table). 4. I had a problem once where I couldn't import or even open the table, but I was able to actually read in from within a recordset. I believe this is related to the jet record paging process (if you open the table in the UI then its accessing a ton of records at once, but if you open through a recordset it's accessing only a page at a time). Using that method I was able to import record by record until hitting the corrupt record. But with some basic error handling I could skip over the bad record(s) and continue on. You end up losing some records, but that's kind of inevitable at this point, right? As a side note, I'm not going to jump on the "Access sucks" bandwagon. I've coded in Access, Visual Studio, and many others (going back to Basic and my good ol TI-994A). I have seen absolute crap written in Access, but I've also seen absolute crap written in C# - in most cases it comes down to "blame the developer, not the platform". That being said, Access definitely has its limitations, but I find that the people that complain about it don't truly know enough about it (and people that are complaining about Jet datastore, not Access). The scariest thing about Access tends to be people with NO development background (or e
Good advice, and this might be easy. There are no relationships - just tables - and only one table appears to be corrupt. I rather like Access for small jobs, and it works fime for apps that require no more than about 5 users. But you can't depend on users to purge old records; there must be some mechanism to automatically limit the size of the database included. I discovered the cause of the overload while spelunking. The software has the ability to log events and measurements of power line conditions, and the bonehead that set it up left logging on - at 10 secongd intervals - to record the line frequency. I've got 2,872,629 records of "60.0, 59.9, 59.9, 60.0,..." That would be just about the last thing I'd care about, and logging for this app is meant to be a temporary diagnostic tool, not a full time function.:mad:
Will Rogers never met me.
-
A Note To Access Developers: Stop using that piece of crap 'software' and switch to a real programming environment.
Everything makes sense in someone's mind
-
G-Tek wrote:
The scariest thing about Access tends to be people with NO development background (or even basic understanding of RDBMS) building solutions; MS including it as part of Office obviously encourages blatant misuse.
Isn't this the fundament problem with Software development in general. The whole industy has been compromised by people with vey little knowledge or experience in software development; but with access (no pun intended) to tools and technologies they would be better-off leaving alone? I can't pick up a scalpel and call myself a surgeon (whilst hacking into people); but I can start MS-Word, hit ALT-F11 and call myself a programmer :mad: rant over... no it didn't help anyone... I'll be at the bar if you need me... merry christmas...
TEAMWORK A few harmless flakes working together can unleash an avalanche of destruction.| Despair Inc.
fowlesp wrote:
Isn't this the fundament problem with Software development in general.
No more so than management of software development.
fowlesp wrote:
I can't pick up a scalpel and call myself a surgeon (whilst hacking into people); but I can start MS-Word, hit ALT-F11 and call myself a programmer
I doubt that heart surgeons from half way around the world meander into a neuro-surgery operation and insist that the surgeon doing the work would be better off using a "better" scapel because everyone "knows" that one is worthless.
-
Always include a feature to automatically archive or delete old records! We bought a very expensive, fancy SCADA system a few years ago, and it's based on Access 2000. It has been behaving strangely lately, so this morning I have been trying to diagnose the problem. When the provider set it up, a function was supposed to be included to autopurge records, but it apparently hasn't been doing so. I now have 2.8 million records and it can't even be manually purged; it also no longer works at all. :mad: Although I'm not familiar with the schema, I've copied the master database to a flash drive and I'm attempting to use DELETE queries to clean up the larger tables. It's taking forever. Of course, I probably should have copied it to a real disk first, but I was thinking clearly about, being distracted by the strong desire to choke the life out of the idiot who wrote this stuff. A second note - test your automatic cleanup code before you walk away from the job! [EDIT] I installed Office 2003 on my server and opened the damaged database file using that version of Access. Compact and Repair appears to have worked perfectly, shrinking the file 10:1 and fixing some odd bits. Thanks to all who offered helpful tips! [/EDIT]
Will Rogers never met me.
modified on Monday, December 27, 2010 9:41 PM
In case you don't know already, and unlike most databases, an Access database won't re-use deleted space for new records. You have to "compact" it to free up file space after deleting records.