Unable to compact Access 97 database 2

[AccessHelp123]

Hi,

We have an access 97 database that has reached 1,048,580 KB. I am unable to compact the database to reduce its size so users can enter data into it. Here is what I have tried so far.

I created a new 97 database and imported all tables into this new db. As soon as I do that I get an error saying the sourse database is in an unrecognizable format. After this point I am unable to open the source database, so i have to use of another copy of the original database.

Next, I tried deleting all table except for one table which I really need and then tried compacting the database. I got an error saying can't read data, you dont have permissions to read data. I then ntocied there are queries in the database I cannot make changes to for whatever reason (although i have the admin user and password). I cannot delete the queries even though I only want that one table.

If I export the table the database error shows up "unrecognizable format.." I am stuck on this one. Any ideas will be great.

Thanks.

 

[MajP]

Might try

http://support.microsoft.com/kb/273956

 

[genomon]

Or this

http://www.granite.ab.ca/access/decompile.htm

Beir bua agus beannacht!

 

[kjv1611]

The information found at genomon's link has helped me fix several Access databases in recent times. I will put my vote on using the methods found in the link. And I also highly suggest you learn those methods, so you can use it again and again if need be. Personally, I've kept a few shortcuts setup just for the purpose of this, so whenever I have another to fix, I simply copy the shortcut, and change the database path that I'm needing to repair.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[AccessHelp123]

Thanks for all your replies. I tried all suggested methods. Still no luck. I think I finally see what the issue is. There is a row in one of the table with #Error values in it. As soon as I click in the cell with the error the database goes bad (Unrecognized database format) and I have to start all over from a backup copy. There is also a row with #Deleted values in it (I am able to manually delete this row however). How do I cleanup the table so there are no #Error records? I am unable to insert any rows or columns to this table. Thanks again.

 

[kjv1611]

Well, if you cannot delete the #Error record(s), what I would do is:
1. Export to text file
2. Manually delete any #Error records or NULL records if you see any such.
3. Import to new table
4. Verify data in new table
5. If you feel confident enough, delete the original table, and rename the new table to the old table's name
a. Or if you are not 100% confident, rename old table to something like zzz_TableName_AsOfYYYYMMDD_Damaged. Then rename the new table to the old table's original name. Just a thought - hopefully you can just delete the old one.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[AccessHelp123]

I had tried that as well. In the process of export, I get the same error message "unrecognizable format and the database becomes unusable" and nothing gets exported.I am sort of stuck in a loop.

 

[AccessHelp123]

Was finally able to transfer data from this table by using queries and evoided those error rows in the queries. I reporpulated the table with non-erroneous data. New issue is that the database wont let me compact the databse it says "Record(s) cannot be read because of permissions". I dont have the right permissions on some of the queries and I ma unable to change it from Tools->Security->User and Group Permissions. Anyone run into this issue before?

 

[MajP]

The table sounds to be pretty corrupted, so not sure if you will be able to do anything like export, open, query, etc
1) Can you do a copy and paste into excel?
You might have to do this in pieces like a few hundred rows at a time. After that you could reimport into a new database
2) Can you do a make table query?
3) Can you build a recordset in code?

 

[MajP]

May try

Function CopyDataFromCorruptTable(tbl As String)
  'Flag good records. Use Make-Table query to copy
  'flagged records to new table.
  Dim rst As New ADODB.Recordset
  Dim strSQL As String
        
  'Add flag field to tbl.
  strSQL = "ALTER TABLE " & tbl _
   & " ADD COLUMN CopyFlag YesNo"
  Debug.Print strSQL
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
  
  'Flag good records.
  rst.Open tbl, CurrentProject.Connection, , adLockOptimistic
  rst.MoveFirst
  Do While Not rst.EOF
    'On Error Resume Next
    rst!CopyFlag = -1
    rst.MoveNext
  Loop
  On Error GoTo 0
  
  'Create new table using tbl's structure.
  strSQL = "SELECT * INTO " _
   & tbl & "_copy FROM " _
   & tbl & " WHERE CopyFlag = -1"
  Debug.Print strSQL
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True

  'Delete CopyFlag from new table.
  strSQL = "ALTER TABLE " _
   & tbl & "_copy " _
   & "DROP COLUMN CopyFlag"
  Debug.Print strSQL
  DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
  DoCmd.SetWarnings True
  
End Function

 

[AccessHelp123]

I had to create a new database and import all the tables. The errorneous table had to be imported in pieces using queries. Thanks for all your replies.

 

[kjv1611]

MajP,

I like the thought behind your method, but if the table is corrupted to where it cannot be queried, would you also not be able to add or remove fields? Or is that not affected by corruption?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MajP]

You may not be able to do any of this, depending on how bad it is corrupted. Most of these approaches are iterative "try this, if it fails try this, if it fails try this, ...". Not sure if anyone can predict which approach will work and which will not. There are somethings that might work in code and not through the user interface. You may not be able to open a query in the designer, but you may be able to run a query in code, or access the data through a recordset.

If I cannot query, If I cannot copy manually from the table and paste into excel, I would try using a recordset. I did not write that code and would probably do it a little differently. I would create a new table based on the structure of the corrupted table. Then simply read the corrupted table row by row and insert into the new table. That way I am not altering or querying the corrupted table.

 

[kjv1611]

Thanks for the clarifications.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57