A Note To Access Developers [Resolved]
-
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.
-
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.
BACKUP! There's a word to conjure by. Don't you have an archived backup of the database from before the present troubles? You do backup your disk space, don't you? If so, you can copy the offending table without data, and (probably) move the data from the corrupted table to the copy. That usually will get a significant portion of the data, unless the table has been totally hashed, much of the data is probably still good. You should use 2003 as noted above. Good luck, you're going to need it.
"To do is to be." [Descartes] "To be is to do." [Voltaire] "Do be do be do..."[Frank Sinatra]
-
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
There are some severe limitations to size in Access. We have an extremely large codebase in access (274,916 KB AFTER Compact & Repair). This does not include the actual tables. All of the tables reside on a SQL server and are linked via ODBC. We have had severe issues with coding in such a large codebase. We had a ticket open with Microsoft for 2 years, ultimately resulting in hotfix KB945674 being developed specifically for us to remedy 'Out of Memory' issues when attempting to save code after modifications (even adding 1 character to a comment in code would result in 'Out of Memory' errors and would corrupt the .mdb). http://support.microsoft.com/kb/945674/en-us <- link to Hotfix for Access 2003 SP3 We have since converted our entire application over to .NET (7.6M lines of code at the moment... and we are currently in beta). At the end... even with the hotfix, we could not make any changes to the code. The hotfix eliminated the 'Crash' after the 'Out of Memory' error, but as you added code, Access would purge other code from the .mdb. We were constantly getting errors because code mysteriously disappeared after making a change to a completely separate section of the program. We had 160 modules/classes, 1152 queries, 514 forms, 418 Reports, and 110 macros. We can only use the .MDB for reference now. If you get the 'Out of Memory' issue and corrupt your file, i have some code you can use that will help... It essentially saves every object (except tables and menus) to a text file, then creates a new database and imports all of the objects into the new file. This actually helps quite a bit, and it is only 175 lines of code. EDIT: I just created a Tip that contains the code if you want... http://www.codeproject.com/Tips/139318/Help-for-the-Access-2000-2003-developer-with-Out-o.aspx[^]
"Reality is merely an illusion, albeit a very persistent one." -Albert Einstein
modified on Thursday, December 23, 2010 3:24 PM