Access 2002 issues??

I have written and deployed a program that will update, delete, and add data to tables in microsoft access 2002. The problem that remains (and didn't before the switch to Access 2002 was made) is that the mdb file grows at enormous rates as the program runs, and gets to the point where the program can't connect to it. Then, if you try to open the database, Access tells you that your .mdb file is either corrupted or isn't a .mdb file. "You or another user may have exited microsoft access while the database was still open" is another line on the warning. It then asks if you want to repair; if yes, the size of the file is reduced, and it works fine. If no, nothing happens. The src files were constructed in a way that combatted unnormalization of the database. Since all the tables had 4 fields in common, all records were combined into a master table with only these fields represented. This master table (7000 records) is used to draw info on what table to go to to make database changes in the program. (hope that made sense) I thought this piece of information may be part of the problem. The file size will grow (usually up to about 75~ MB from 7~ MB after repair) until it decides not to work. We probably add around 50 records in a day. Has anyone had any experience close to this? Thanks

access db's grow, this is a common thing with access. you need to compact it every once in a while here is the code, that way you can give ability from within the application to compact it. //********************************************************************** //CODE EXTRACTED FROM ICODELIBARRY.NET http://www.imetasoft.com/iCodeLibary //********************************************************************** //SEE CODE EXAMPLE FOR USAGE using System; using System.IO; namespace iCode_Library.NET { ///

/// Summary description for CompactDB. ///

public class CompactDB { JRO.JetEngine jro; public CompactDB() { jro = new JRO.JetEngine(); } public bool CompactDBAccess2000(string dbDirectory, string dbFile) { try { string backupFileName = "backup.mdb"; string sourceConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbDirectory + dbFile; string destConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbDirectory + backupFileName + ";Jet OLEDB:Engine Type=5"; jro.CompactDatabase(sourceConn, destConn); FileInfo fi = new FileInfo(dbDirectory + backupFileName); fi.CopyTo(dbDirectory+dbFile, true); fi.Delete(); return true; } catch(Exception msg) { System.Windows.Forms.MessageBox.Show(msg.Message); return false; } } } } //FROM MICROSOFT SITE: /* HOW TO: Compact a Microsoft Access Database by Using Visual Basic .NET The information in this article applies to: Microsoft Visual Basic .NET (2002) IN THIS TASK SUMMARY Requirements Steps to Build Example Pitfalls REFERENCES SUMMARY Neither ActiveX Data Objects (ADO) nor ADO .NET provide the means to compact or repair Microsoft Access databases. However, you can accomplish this task by using the Microsoft Jet OLE DB Provider and Replication Objects (JRO) that was introduced with Microsoft Data Access Components (MDAC) version 2.1. ADO .NET allows the use of COM-based object libraries through the Interop layer. This article demonstrates how to compact an Access database by using Visual Basic .NET. back to the top Requirements Microsoft Visual Basic .NET Microsoft Jet and Replication Objects 2.1, 2.5, or 2.6 Library back to the top Steps to Build Example Open a new Visual Basic .NET console application. In the Solution Explorer window, right-click the References node and select Add Reference. In the Add Reference dialog box, click the COM tab and select Microsoft Jet and Replication Objects 2.I Library. Click Select to add it to the "Selected Components". Click OK. A warning will be displayed if there is no wrapper found for the selected library. Click Yes to generate a wrapper. Microsoft ActiveX Data Objects Library (ADODB) and JRO references will be added to the project's References. In the Solution Explorer window, right-click Module1.vb and click View Code. Delete all of the code from the code window. Copy the following code and paste it into the code window: Module Module1 Sub Main() Dim jro As JRO.JetEngine jro = New JRO.JetEngine() jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb", _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewNwind.mdb;Jet OLEDB:Engine Type=5") MsgBox("Finished Compacting Database!") End Sub End Module Change the path to the Source and Destination .mdb files as appropriate. Press F5 to build and run the project. The compacted database will be in Access 2000 (Jet 4.0) format. For a different Jet format, see "References." back to the top Pitfalls In order to compact a database, the Jet Database Engine requires exclusive access to the database file. Attempting to compact a database file that is currently in use will result in an exception. This exception can be caught using a Try...Catch structure. back to the top REFERENCES */ //CALL compactAccess2000DB(GlobalStuff.ProgramDirectory + @"\DB\", "iCodeLibrary.mdb"); //FUNCTION THAT CALLS CLASS public void compactAccess2000DB(string dbDirectory, string dbFile) { try { CompactDB cpDB = new CompactDB(); cpDB.CompactDBAccess2000(dbDirectory, dbFile); MessageBox.Show("Compacting the database complete", "Compacting complete", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch(Exception) { MessageBox.Show("Failed to compact the database", "Compacting failed", MessageBoxButtons.OK, MessageBoxIcon.Error); } }

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

kovan22y ago
access db's grow, this is a common thing with access. you need to compact it every once in a while here is the code, that way you can give ability from within the application to compact it. //********************************************************************** //CODE EXTRACTED FROM ICODELIBARRY.NET http://www.imetasoft.com/iCodeLibary //********************************************************************** //SEE CODE EXAMPLE FOR USAGE using System; using System.IO; namespace iCode_Library.NET { ///
/// Summary description for CompactDB. ///
public class CompactDB { JRO.JetEngine jro; public CompactDB() { jro = new JRO.JetEngine(); } public bool CompactDBAccess2000(string dbDirectory, string dbFile) { try { string backupFileName = "backup.mdb"; string sourceConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbDirectory + dbFile; string destConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbDirectory + backupFileName + ";Jet OLEDB:Engine Type=5"; jro.CompactDatabase(sourceConn, destConn); FileInfo fi = new FileInfo(dbDirectory + backupFileName); fi.CopyTo(dbDirectory+dbFile, true); fi.Delete(); return true; } catch(Exception msg) { System.Windows.Forms.MessageBox.Show(msg.Message); return false; } } } } //FROM MICROSOFT SITE: /* HOW TO: Compact a Microsoft Access Database by Using Visual Basic .NET The information in this article applies to: Microsoft Visual Basic .NET (2002) IN THIS TASK SUMMARY Requirements Steps to Build Example Pitfalls REFERENCES SUMMARY Neither ActiveX Data Objects (ADO) nor ADO .NET provide the means to compact or repair Microsoft Access databases. However, you can accomplish this task by using the Microsoft Jet OLE DB Provider and Replication Objects (JRO) that was introduced with Microsoft Data Access Components (MDAC) version 2.1. ADO .NET allows the use of COM-based object libraries through the Interop layer. This article demonstrates how to compact an Access database by using Visual Basic .NET. back to the top Requirements Microsoft Visual Basic .NET Microsoft Jet and Replication Objects 2.1, 2.5, or 2.6 Library back to the top Steps to Build Example Open a new Visual Basic .NET console application. In the Solution Explorer window, right-click the References node and select Add Reference. In the Add Reference dialog box, click the COM tab and select Microsoft Jet and Replication Objects 2.I Library. Click Select to add it to the "Selected Components". Click OK. A warning will be displayed if there is no wrapper found for the selected library. Click Yes to generate a wrapper. Microsoft ActiveX Data Objects Library (ADODB) and JRO references will be added to the project's References. In the Solution Explorer window, right-click Module1.vb and click View Code. Delete all of the code from the code window. Copy the following code and paste it into the code window: Module Module1 Sub Main() Dim jro As JRO.JetEngine jro = New JRO.JetEngine() jro.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\nwind.mdb", _ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\NewNwind.mdb;Jet OLEDB:Engine Type=5") MsgBox("Finished Compacting Database!") End Sub End Module Change the path to the Source and Destination .mdb files as appropriate. Press F5 to build and run the project. The compacted database will be in Access 2000 (Jet 4.0) format. For a different Jet format, see "References." back to the top Pitfalls In order to compact a database, the Jet Database Engine requires exclusive access to the database file. Attempting to compact a database file that is currently in use will result in an exception. This exception can be caught using a Try...Catch structure. back to the top REFERENCES */ //CALL compactAccess2000DB(GlobalStuff.ProgramDirectory + @"\DB\", "iCodeLibrary.mdb"); //FUNCTION THAT CALLS CLASS public void compactAccess2000DB(string dbDirectory, string dbFile) { try { CompactDB cpDB = new CompactDB(); cpDB.CompactDBAccess2000(dbDirectory, dbFile); MessageBox.Show("Compacting the database complete", "Compacting complete", MessageBoxButtons.OK, MessageBoxIcon.Information); } catch(Exception) { MessageBox.Show("Failed to compact the database", "Compacting failed", MessageBoxButtons.OK, MessageBoxIcon.Error); } }