A Note To Access Developers [Resolved]
-
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
-
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 sad dilemma indeed. When did you notice the database was too big, after it crashed on ya? Not trying to be mean, but somebody (database manager) should have noticed something was wrong with the database getting so big. I know 97 use to have a problem growing exponentially depending on how you changed the design of the forms and the record sources on both forms and reports. But there was a way to correct it, to bring the file size back down. Being a huge MS Access fan and developer 96 - 2004, and occasionally since. Almost all of my projects started out with the customer having a large excel sheet or multiple excel sheets being passed around, updated, and eventually crashing or everyone doing their own version of the charts and reports. In 40 hours or less I could have all the employees that needed to input data (controlled or not) doing so with some very very user friendly forms. Then in the next 40 - 80 hours working with the customer and their users to determine what the standard would be for the reporting and charting and adding any new scope stuff to the database. Archiving I have to admit most customers didn't want it, no matter how hard I tried to sell them on the idea. It was usually no this is a temporary fix till we get something more permanent (SAP usually). There was many times when I'd get a call, hey we'd like some changes, we don't like the way the other system works and we know your not going to cost more then 25% what it'll cost for us to have our other system changed. (as noted earlier proper tool for the job) Yes, Access has it's limits and it has it's quirks, but those quirks show themselves as warnings before those files get corrupted. Sometimes network (had to prove to network admin they had a bad switch once), sometimes things user is doing, sometimes the access/jet engine, sometimes the developer. That database someone mentioned about having such a large code base, you couldn't break it up into smaller mdb front end files (data entry in one, reporting in the other) with those connected to the same back end? for the past 5/6 years been doing mostly VB6 (yes still, been teaching myself .net for past 4 years, hobby at home type stuff). It would take me a month to do the cool user interfaces I could get done with access in a week. But even though Access paid my bills for quite a few years, I have moved on to other pastures but that's a personal thing, but I have always liked access especially when they added the ability to create Access Projects and be the front end
-
A sad dilemma indeed. When did you notice the database was too big, after it crashed on ya? Not trying to be mean, but somebody (database manager) should have noticed something was wrong with the database getting so big. I know 97 use to have a problem growing exponentially depending on how you changed the design of the forms and the record sources on both forms and reports. But there was a way to correct it, to bring the file size back down. Being a huge MS Access fan and developer 96 - 2004, and occasionally since. Almost all of my projects started out with the customer having a large excel sheet or multiple excel sheets being passed around, updated, and eventually crashing or everyone doing their own version of the charts and reports. In 40 hours or less I could have all the employees that needed to input data (controlled or not) doing so with some very very user friendly forms. Then in the next 40 - 80 hours working with the customer and their users to determine what the standard would be for the reporting and charting and adding any new scope stuff to the database. Archiving I have to admit most customers didn't want it, no matter how hard I tried to sell them on the idea. It was usually no this is a temporary fix till we get something more permanent (SAP usually). There was many times when I'd get a call, hey we'd like some changes, we don't like the way the other system works and we know your not going to cost more then 25% what it'll cost for us to have our other system changed. (as noted earlier proper tool for the job) Yes, Access has it's limits and it has it's quirks, but those quirks show themselves as warnings before those files get corrupted. Sometimes network (had to prove to network admin they had a bad switch once), sometimes things user is doing, sometimes the access/jet engine, sometimes the developer. That database someone mentioned about having such a large code base, you couldn't break it up into smaller mdb front end files (data entry in one, reporting in the other) with those connected to the same back end? for the past 5/6 years been doing mostly VB6 (yes still, been teaching myself .net for past 4 years, hobby at home type stuff). It would take me a month to do the cool user interfaces I could get done with access in a week. But even though Access paid my bills for quite a few years, I have moved on to other pastures but that's a personal thing, but I have always liked access especially when they added the ability to create Access Projects and be the front end
Sadly, we don't have anyone watching the databases - it's store-bought stuff, preconfigured at the factory and shipped with the equipment. The factory guy who did the final checkout and GUI bits set up a self-purge routine, but it obviously didn't work. There's even a manual cleanup button available, but clicking it doesn't do anything. One last thing he did was leave one measurement set to log its value every ten seconds or so, maybe less. That totally useless parameter is what killed the database, as the logging feature is not intended to be left on; it's just supposed to be used to watch anomolies. It's not an Access issue, it's a problem with the way it was deployed. But fixing it has become an Access issue...
Will Rogers never met me.
-
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
Thanks! I will take a look at your Tip, and I expect it will help a lot of folks. But since it saves everything but the tables, and the only thing in this DB is the tables, I think I'll first attempt to compact and repair using the proper version of Access first, then try your code if that doesn't solve the problem. Since Access is far more common in the real business world than most here seem to think, I expect your Tip will receive a lot of attention! :-D There are a dozen tables, all but one of which are accessible. The accessible ones are small and static - mostly usernames and permissions, names of tags associated with measurements we monitor, and alarm states. These never change. The huge tables contain log records, and I couldn't care less if I lose them. The one really damaged table is some kind of Notebook, which I think is related to a feature that allows a technician working on a problem to record notes for future reference. It's never been used, so the damage must be something structural. If Compact and Repair doesn't fix it, I think I can use your code to save the intact data from all the other tables, then manually reconstruct the one bad table that should be empty. I'm hoping the manufacturer still has a design folder on this project that contains the table schemas...
Will Rogers never met me.
-
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
Try BULKDELETE