A Note To Access Developers [Resolved]

[Joshua Quick]

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.

[Fenshaw]

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]

[Fenshaw]

Yeah. What he said. Good post.

"To do is to be." [Descartes] "To be is to do." [Voltaire] "Do be do be do..."[Frank Sinatra]

[Mike Baldini]

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

[cyplesma]

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

[Roger Wright]

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.

[Roger Wright]

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.

[Michael Kingsford Gray]

Try BULKDELETE

[Mike Baldini]

If you can somehow determine the schema of the corrupted table, you could simply import everything else into a new file. But getting the schema is the problem.... Perhaps you can try a modified version of my tip to get a txt file containing just the schema for the tables.... This version should export the table into a text file that is stored in a sub directory inside the folder that contains the file you want to filter. Now I haven't tried this so I am not sure if it will work.... but it might. so :-D

Function FilterDBModified(strFilePath As String)
Dim objAccess As Object
Dim strFolder As String
Dim strCurrentFile As String
Dim strCurrentObject As String
Dim strFilteredDB As String

Dim fs
Dim ref
Dim f As Object
Dim objtype As AcObjectType

Dim objAllObjects As New Collection
Dim objObjectGroup As Object
Dim intObjType As Integer
Dim I As Integer
Dim j As Integer
Dim intRefNum As Integer

Dim refItem As Reference
Dim arrayRefs() As String

Dim strErrMsg As String

'Open the source database
Set objAccess = CreateObject("Access.Application")

On Error GoTo ErrorHandler

objAccess.OpenCurrentDatabase strFilePath, False

strFolder = GetDirectoryFromPath(strFilePath)
strFilteredDB = Left(strFilePath, Len(strFilePath) - 4) & "filtered.mdb"

With objAllObjects
    .add objAccess.CurrentData.AllTables
End With

Set fs = CreateObject("Scripting.FileSystemObject")

If Not fs.FolderExists(strFolder & "\\texttmp") Then
    fs.CreateFolder (strFolder & "\\texttmp")
End If

For I = 1 To objAllObjects.Count

    If objAllObjects(I).Count > 0 Then
        For j = 0 To objAllObjects(I).Count - 1
           
           Set objObjectGroup = objAllObjects(I)

           'Maybe put an If statement here to only filter the one table....
           strCurrentObject = objObjectGroup(j).Name
           intObjType = objObjectGroup(j).Type
           Debug.Print "Saving object " & strCurrentObject
           objAccess.SaveAsText intObjType, strCurrentObject, \_
           strFolder & "texttmp\\" & strCurrentObject & intObjType & ".txt"
      
        Next j
    End If
    
Next I

Debug.Print ""
objAccess.Quit
Set objAccess = Nothing

End Function

"Reality is merely an illusion, albei

[kmoorevs]

Ahhh what fond memories...I started writing Basic on a TI-994a many years ago...must have been around '82 or so. THanks for the flashback! The Adventure games by Scott Adams were great fun!

[Roger Wright]

Surprisingly, I got it fixed. As I posted earlier, I have a copy of Office 2003 lying around here. Access 2003 uses the same file format as 2000, so I installed it on my server and tried opening the damaged mdb file. It worked great, and a quick run of Compact and Repair dropped the size from 2.7 GB to 276 MB, well within manageable range. It also created a new table listing a couple of repair errors, but none of them look significant. I'm assuming that, if the program doesn't know about the new table, it probably won't mind it being there, so I'll leave it for now. When I get back to work next week I'll install the repaired DB and see what happens. Thanks for all your helpful suggestions! :-D

Will Rogers never met me.