MS Access database not closing
-
I have a VB application that updates one database using input from another database. The code looks like: Dim InputDC as OldDatabaseConnection Dim OutputDC As ... InputDC.Open OutputDC.Open For each table in Input Database Read data from old table Copy data to new table Write new table to Output database ... InputDC.Close OutputDC.Close Sometimes [often] the Output database does not close, that is the Output.ldb file is left hanging around for an indefinite time period. Sometimes the program runs fine e.g. 50 times in a row; then suddenly it will hang. I've been programming for 45 years and have done consulting and know the first rule is that "Users lie about no changes"; however in this case there ARE no changes to the code. The program wants to rename the data bases so that Input.mdb becomes Input_Old.mdb and Output.mdb becomes Input.mdb. With the control file (Output.ldb) hanging around, the Rename causes an exception. It is [almost] always the Output.ldb file that is still around implying that Output.MDB is still open... I have taken the code apart line by line; there is only one open and close per database. There are closes within Catch paragraphs. But, there is only one .Open per database. I've tried a variety of loops testing for the .ldb to go away, but this usually just results in the program running for a very long time doing nothing. When the program is terminated thru its main form's Exit button, the .ldb files go away almost immediately. I can watch the history of file creation, etc. in Windows Explorer as the program runs. Has anyone experienced this "feature"? If so have you figured out how to solve it?
Charles Wolfe C. Wolfe Software Engineering
-
I have a VB application that updates one database using input from another database. The code looks like: Dim InputDC as OldDatabaseConnection Dim OutputDC As ... InputDC.Open OutputDC.Open For each table in Input Database Read data from old table Copy data to new table Write new table to Output database ... InputDC.Close OutputDC.Close Sometimes [often] the Output database does not close, that is the Output.ldb file is left hanging around for an indefinite time period. Sometimes the program runs fine e.g. 50 times in a row; then suddenly it will hang. I've been programming for 45 years and have done consulting and know the first rule is that "Users lie about no changes"; however in this case there ARE no changes to the code. The program wants to rename the data bases so that Input.mdb becomes Input_Old.mdb and Output.mdb becomes Input.mdb. With the control file (Output.ldb) hanging around, the Rename causes an exception. It is [almost] always the Output.ldb file that is still around implying that Output.MDB is still open... I have taken the code apart line by line; there is only one open and close per database. There are closes within Catch paragraphs. But, there is only one .Open per database. I've tried a variety of loops testing for the .ldb to go away, but this usually just results in the program running for a very long time doing nothing. When the program is terminated thru its main form's Exit button, the .ldb files go away almost immediately. I can watch the history of file creation, etc. in Windows Explorer as the program runs. Has anyone experienced this "feature"? If so have you figured out how to solve it?
Charles Wolfe C. Wolfe Software Engineering
I've seen this a few times before with other files on windows system. (Windows Server 2003) If I had to guess, the OS is doing some caching or something and the operation is not finished. My solution to this was to use a Try-Catch block and sleep for a few seconds before trying to access the file again. I put a counter in the loop so that if it tries more than 100 times, it finally aborts the operation. Not the best solution, but at least my program no longer crashes because Windows didn't finish renaming the file. I'm an old VAX/VMS guy and the crap you have to deal with Windows is just awful. (getting off my soapbox now ...) ;) Give the Try-catch loop thing a try and see if it works for you.
-
I have a VB application that updates one database using input from another database. The code looks like: Dim InputDC as OldDatabaseConnection Dim OutputDC As ... InputDC.Open OutputDC.Open For each table in Input Database Read data from old table Copy data to new table Write new table to Output database ... InputDC.Close OutputDC.Close Sometimes [often] the Output database does not close, that is the Output.ldb file is left hanging around for an indefinite time period. Sometimes the program runs fine e.g. 50 times in a row; then suddenly it will hang. I've been programming for 45 years and have done consulting and know the first rule is that "Users lie about no changes"; however in this case there ARE no changes to the code. The program wants to rename the data bases so that Input.mdb becomes Input_Old.mdb and Output.mdb becomes Input.mdb. With the control file (Output.ldb) hanging around, the Rename causes an exception. It is [almost] always the Output.ldb file that is still around implying that Output.MDB is still open... I have taken the code apart line by line; there is only one open and close per database. There are closes within Catch paragraphs. But, there is only one .Open per database. I've tried a variety of loops testing for the .ldb to go away, but this usually just results in the program running for a very long time doing nothing. When the program is terminated thru its main form's Exit button, the .ldb files go away almost immediately. I can watch the history of file creation, etc. in Windows Explorer as the program runs. Has anyone experienced this "feature"? If so have you figured out how to solve it?
Charles Wolfe C. Wolfe Software Engineering
Maybe, just maybe, your newly created/modified file is in use by another program, e.g. an anti-virus or an indexer (such as Google Desktop Search). What you could do is provide a loop that tries 5 times, with 1 second interval. It is what Windows Explorer does when you tell it to delete a file! :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
I have a VB application that updates one database using input from another database. The code looks like: Dim InputDC as OldDatabaseConnection Dim OutputDC As ... InputDC.Open OutputDC.Open For each table in Input Database Read data from old table Copy data to new table Write new table to Output database ... InputDC.Close OutputDC.Close Sometimes [often] the Output database does not close, that is the Output.ldb file is left hanging around for an indefinite time period. Sometimes the program runs fine e.g. 50 times in a row; then suddenly it will hang. I've been programming for 45 years and have done consulting and know the first rule is that "Users lie about no changes"; however in this case there ARE no changes to the code. The program wants to rename the data bases so that Input.mdb becomes Input_Old.mdb and Output.mdb becomes Input.mdb. With the control file (Output.ldb) hanging around, the Rename causes an exception. It is [almost] always the Output.ldb file that is still around implying that Output.MDB is still open... I have taken the code apart line by line; there is only one open and close per database. There are closes within Catch paragraphs. But, there is only one .Open per database. I've tried a variety of loops testing for the .ldb to go away, but this usually just results in the program running for a very long time doing nothing. When the program is terminated thru its main form's Exit button, the .ldb files go away almost immediately. I can watch the history of file creation, etc. in Windows Explorer as the program runs. Has anyone experienced this "feature"? If so have you figured out how to solve it?
Charles Wolfe C. Wolfe Software Engineering
Hello I had a very similar problem with an Access database not closing in a VB.NET application. I tried various things like waiting for 5 seconds, a loop retrying every few seconds, adding "OLE DB Services=-4" to the connection string. Nothing worked reliably until I added these two lines after all the database close statements:
GC.Collect()
GC.WaitForPendingFinalizers()I hope this helps. Marek