Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2004
    Posts
    15

    Unanswered: Database is failing to repair correctly.

    I am running Access 2000 on a small NT network and running into a problem. Some time ago several users were all working on the database at once when it crashed. Upon restarting we get the message

    "The database F:\ConstituentLetters.mdb needs to be repaired or isn't a database file."

    After we click the repair the computer goes comatose for about a quarter of an hour as it repairs the database. After it is finished repairing the database works fine until multiple users begin to work on it again. After a few minutes of this we get the error message.

    This database is an unrecognized format, or something to that effect and then we need to repair it again.

    How can I rid myself of this problem?

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Database is failing to repair correctly.

    Originally posted by ManOAction
    I am running Access 2000 on a small NT network and running into a problem. Some time ago several users were all working on the database at once when it crashed. Upon restarting we get the message

    "The database F:\ConstituentLetters.mdb needs to be repaired or isn't a database file."

    After we click the repair the computer goes comatose for about a quarter of an hour as it repairs the database. After it is finished repairing the database works fine until multiple users begin to work on it again. After a few minutes of this we get the error message.

    This database is an unrecognized format, or something to that effect and then we need to repair it again.

    How can I rid myself of this problem?
    Sounds like you may have some corruption going on there... The first thing I'd do is create a new blank database and try importing all objects and relationships from the old database... If there's anything corrupt, you may get a prompt saying something like "can't import tblWhatever due to..." ... If everything imports no problem, I would go to design view for a module and try compiling the project... If no errors there, I'd compact and repair, and then rename and replace the new one for the old one....

    You can also check for stray .ldb files in the directory where the database is located... When the thing crashed there might have been a lock file that stayed put and is now causing problems...

    HTH

  3. #3
    Join Date
    Oct 2003
    Location
    New York
    Posts
    23

    Re: Database is failing to repair correctly.

    It may also be the size of the database..+ the more users, the more bloated/fragmented.

    If Trudi's reply doesn't work...

    If the database is not already, you may want to split it...front-end, back-end.

  4. #4
    Join Date
    Feb 2004
    Posts
    15

    Re: Database is failing to repair correctly.

    There was a lock file when we first crashed but it is now gone and the .ldbs are opening and closing as normal now.

    We are working with the forms themselves and aren't compiling into a project so there is also no need to compile.

    I will work on importing all of the seperate tables, queries and forms but we have quite a few of them so it might take some time.

    On a side note and out of curiosity. Is there a file size limit on Access databases? How big is big with these?

  5. #5
    Join Date
    Feb 2004
    Posts
    15

    Re: Database is failing to repair correctly.

    Originally posted by tiggernyc
    It may also be the size of the database..+ the more users, the more bloated/fragmented.

    If Trudi's reply doesn't work...

    If the database is not already, you may want to split it...front-end, back-end.

    Yeah that might be it then. I am fairly new and didn't know I could split these up? Can I query and sort between two different databases? Where is a good place to find out more info on that sort of thing? Currently it is about 1.3 gigs, with one table having over 3.2 million records, and we have about two dozen different queries, forms, tables, and such.

  6. #6
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    The max size of a non-split .mdb file is 2gb.

    Front-end back-ended database's size with linked tables is only limited by the size of the hard drive(s).
    I won't byte... hard!

  7. #7
    Join Date
    Feb 2004
    Posts
    15
    Originally posted by The Byte
    The max size of a non-split .mdb file is 2gb.

    Front-end back-ended database's size with linked tables is only limited by the size of the hard drive(s).
    Where can I find out more about splitting my database into a front end-back end database?

    I think it has got to be that table, I checked the specs and it looks like the size of a single table is limited to a gig and it is getting pretty close to that.

  8. #8
    Join Date
    Oct 2003
    Location
    New York
    Posts
    23
    1.3G is large, but not unusual...but would probably be the main source of the problem...that coupled with network traffic...I just ran into a client that has a database just under 2G...working on it as we speak...

    Anyway...the easiest way is to load all data tables into the back and and all programming into the front-end...if you are not changing the code often, you can put the front-end on each pc...trying to run code, queries, etc across the network can be cumbersome.

    Just attach the data tables into the front-end...

    Good Luck...

  9. #9
    Join Date
    Oct 2003
    Location
    New York
    Posts
    23
    Just saw your last reply...

    You may want to see if you can split the table, as well...any information that is repetitive, so you can use another table to interpret...

  10. #10
    Join Date
    Feb 2004
    Posts
    15
    Originally posted by tiggernyc
    1.3G is large, but not unusual...but would probably be the main source of the problem...that coupled with network traffic...I just ran into a client that has a database just under 2G...working on it as we speak...

    Anyway...the easiest way is to load all data tables into the back and and all programming into the front-end...if you are not changing the code often, you can put the front-end on each pc...trying to run code, queries, etc across the network can be cumbersome.

    Just attach the data tables into the front-end...

    Good Luck...
    Hmm, Is the Access Database splitter wizard what I am looking for?

  11. #11
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: Database is failing to repair correctly.

    Originally posted by ManOAction
    There was a lock file when we first crashed but it is now gone and the .ldbs are opening and closing as normal now.

    We are working with the forms themselves and aren't compiling into a project so there is also no need to compile.

    I will work on importing all of the seperate tables, queries and forms but we have quite a few of them so it might take some time.

    On a side note and out of curiosity. Is there a file size limit on Access databases? How big is big with these?
    I meant for you to compile any vba code... by going into any module (including form modules) that has some... If you select compile it's going to compile the whole database, not just the one module...

    Importing is pretty quick... Just "Get External Data", browse and select the old database, hit Select All, make sure the options are chosen correctly and it does the rest for you...

    The bigger the size of the database, the more opportunity for one corrupt item or record or something to get in there...

    Access has a Wizard to split the database into front-end/back-end... Look up "Splitting the database" in Access Help and it's easy from there... Just make sure you keep a back up of the original database before you do the split ... (Just in case... )

  12. #12
    Join Date
    Feb 2004
    Posts
    15

    Re: Database is failing to repair correctly.

    Ok a couple more small problems. While I was attempting to split a table up using the table analyzer I received the message "system resources exceeded". I thought I would change the size of the table by changing the field size of a few fields that are too large. However when I do this, I get the error message.

    "There isn't enough disk space or memory".

    How can I change the field size property of a table without using the design view of that table. Is there an update query that I can write for that?

  13. #13
    Join Date
    Feb 2004
    Posts
    15
    Isn't there an Access function that I can use to change the field size property with an update query? Similar to the format of "Right (FieldName, 5)" or something. Isn't there one to change the field size?

  14. #14
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Originally posted by ManOAction
    Isn't there an Access function that I can use to change the field size property with an update query? Similar to the format of "Right (FieldName, 5)" or something. Isn't there one to change the field size?
    Nope... You'd have to do this either manually or programmatically...
    Access Jet Engine doesn't support ALTER TABLE sql statements...

    Here's some code that'll do it... You'll just have to call it with the correct parameters...
    Code:
    Public Sub change_field_size(DBPath As String, _
      tblName As String, fldName As String, fldSize As Integer)
        ' this routine changes the field size
        
        Dim db As Database
        Dim td As TableDef
        Dim fld As Field
            
        Set db = OpenDatabase(DBPath)
        Set td = db.TableDefs(tblName)
        
        If td.Fields(fldName).Type <> dbText Then
            ' wrong field type
            db.Close
            Exit Sub
        End If
        
        If td.Fields(fldName).Size = fldSize Then
            ' the field width is correct
            db.Close
            Exit Sub
        End If
        
        ' create a temp feild
        td.Fields.Append td.CreateField("temp", dbText, fldSize)
        td.Fields("temp").AllowZeroLength = True
        td.Fields("temp").DefaultValue = """"""
    
        ' copy the info into the temp field
        db.Execute "Update " & tblName & " set temp = " & fldName & " "
        
        ' delete the field
        td.Fields.Delete fldName
        
        ' rename the field
        td.Fields("temp").Name = fldName
        db.Close
        
    End Sub
    HTH

  15. #15
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,420
    Provided Answers: 7

    Re: Database is failing to repair correctly.

    Originally posted by ManOAction
    Ok a couple more small problems. While I was attempting to split a table up using the table analyzer I received the message "system resources exceeded". I thought I would change the size of the table by changing the field size of a few fields that are too large. However when I do this, I get the error message.

    "There isn't enough disk space or memory".

    How can I change the field size property of a table without using the design view of that table. Is there an update query that I can write for that?
    If the split does not work then try this

    Create a new DataBase
    right click in the white area
    IMPORT everything but not the tables

    in the tables
    LINK tables the tables

    what i do is use the '\\servername\MYdata$\database.mdb'
    just type it in the '\\servername\MYdata$\' in the filename [...]

    I don't use the drive letters

    then the user can't see the data
    if they can't see it they can't delete it LOL

    that it linking Done

    Know you can put the newly created file on each user computer.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •