RESOLVED ** Unrecognizable database format

We have an existing MS Access 2000 database which we are gradually rewriting in Visual Basic/ADO to connect to the database. Therefore at the moment we have a dual system situation, both pointing to the same backend.

About once or twice a week we get the following message:

"Unrecognizable database format "......mdb". Please Specifiy the correct datafile."

This message appears when trying to open in normal access and also from VB. To fix, we get everybody off and let access repair the database.

Does anybody know of what causes this problem or has anybody ever seen this before? Any help would be much appreciated.

It's usually caused by people exiting the database with records still open, while it's doing a transaction, whilke there are connections and recordsets open or while it is trying to execute something.

Now just make sure when your form/s unload you close all connections and recordset and set them to nothing.

To check the problem pc's just open up the .ldb file access makes in a text editor like notepad. The list of machines should be in there!

later
b :)

Thanks for the input.
I've gone through all of the VB code and wherever there's a Recordset.Open, I've ensured that there's a Recordset.Close.
Any other suggestions?

Quote:

---

Originally posted by Beacon
Now just make sure when your form/s unload you close all connections and recordset and set them to nothing.

---

did you make sure all connections and forms closed as well?

regards,
Regan

Regan,

yes I've checked all that. And unfortunately it's happing with increased regularity too.

Any other ideas? We're becoming quite concernded.....

HELP!!!!

Anything else? We're desperate.

We just upgraded to MDAC 2.7 but no luck as yet.

Thanks All

Repair it.
Make a new mdb and copy it all in (import).
Compact.
Rename the old one
Rename the new one
Try and see what happens.

Occassionally mdbs keep corrupting, but somehow copying into a new mdb seems to solve... sometimes.

Vince

still happening about twice a day. we're thinking of logging a call with Microsoft. Anybody know what they'd charge for something like this?

How large is the mdb?

Pretty big. Between 52 and 56mb depending upon date since last compaction.

I tried looking on Microsoft.com for the maximum recommended .mdb size, but couldn't find anything useful.

Access starts to get a bit flaky at that size. I think max size is quite high - just not very practical or useable.

To be honest, I don't think MS would be to helpful if you logged a call. They'd probably tell you to use SQLServer.

Bah 56mb is pretty small access 2k can take 2gb apparently but isnt really good for 2gb if you know what i mean!

I'd say it's your code or locks or cursors or something!
Whats does the .ldb file say?

I have a database that ive been developing for sometime using access 97 mdb.
when i installed office 2000 and reset all the links i got the same error messages (unrecognizable database format), so i tried building the same application from scratch using 2000 mdb, and still had the same problem, i also found that i couldnt use xp office as well (same error).
ive tried various variations using win 98 and win XP and still kept getting the same results, so now im back to 97 mdb.
I might be wrong but i think that there is a compatability issue with anything other than a 97 .mdb

Are you using VB??

If so you need to use Jet4.0 not Jet3.5! Otherwise it will only work with 97!!

b :)

We've just had it happen again as I was reading your replies. BTW, thanks to everybody here for their suggestions.

In answer to the above query, then yes we're using 4.0

stingrae that was in reply to Quigley's problem!
Coz it different to yours!

Stingrae:

Your problem i think is because of your code for your recordsets:

1) How many people connect to it?
2) Post your connection code and how you open recordsets!

b :)

the entire program is written identically. here is a standard function:

Code:

---

Public Function CheckClientEnrolment(ClientID As String, _
                                    CourseID As Long) As Boolean
    Dim sSQLStm As String
    Dim recEnrolment As New ADODB.Recordset
   
    sSQLStm = "SELECT COUNT(*) " & _
              "FROM tblEnrolment " & _
              "WHERE ClientID = '" & ClientID & "' " & _
                "AND CourseID = " & CourseID & " "
    recEnrolment.Open sSQLStm, gconDatabase
    If recEnrolment.Fields(0).Value > 0 Then
        CheckClientEnrolment = True
    Else
        CheckClientEnrolment = False
    End If
    recEnrolment.Close
    Set recEnrolment = Nothing
End Function

---

thanks once again for your help

Ok see this code here:

recEnrolment.Open sSQLStm, gconDatabase

Is that missing something?
Or is that it?

If so ado is setting the defaults!
Which i do believe is the problem.

b :)

That's it. ADO is setting the defaults. Here's the connection string if that helps at all:

Code:

---

Global gconDatabase As New ADODB.Connection

Private Function ConnectToDatabase()
    Dim lReturnCode As Long
    Dim bConnected As Boolean
    Dim bCancel As Boolean
   
    On Error GoTo errConnectToDatabase
    ConnectToDatabase = gcReturnError
   
    frmSplash.lblDatabasePath.Caption = gsDatabasePath
    gconDatabase.Provider = "Microsoft.Jet.OLEDB.4.0"
    gconDatabase.Open gsDatabasePath
    bConnected = True
    If bConnected = True Then ConnectToDatabase = gcReturnOK
    Exit Function
errConnectToDatabase:
    Debug.Print Err.Description
    MsgBox "There was an error opening the specified data file or it was not found. " & vbCr & _
          "The full error text is: " & vbCr & _
          Err.Description & vbCr & _
          "Please Specifiy the correct data file."
    End
End Function

---

Well thats where the problem is!

Multi user with ado defaults = trouble!

Try this:

VB Code:

recEnrolment.Open sSQLStm, gconDatabase, adOpenKeyset, adLockPessimistic, adCmdText

instead of what you have!

I aint at my computer so i just ripped it out of my ADO tutorial in the top thread! :)

How many users will be adding/deleting/editing data at the same time?

b :)

we have between 5 to 15 users logged on at once.

there's quite a few recordsets being opened and closed. i like your suggestion as it seems practical enough. how sure are you that this is the cause? I'd like to think it is as I don't have any other leads.

Because this is ado not dao!
With dao you could leave it and it selects the most appropriate type but ado you have to specify!

I'm pretty sure becuase ado's default cursor is forward-only and the default lock is adLockReadOnly!

These 2 would make it hard to add,edit or delete records! :)

Try using that code.

:)

ok, I've done a find and replace and changed all my recordsets, but what about Ado Commands? Are there additional parameters that you recommend for those too?

Here's an example:

Code:

---


Private Sub cmdSave_Click()
    Dim sSQLStm As String
    Dim cmdEnrolment As New ADODB.Command
    If cboStartDate.ListIndex < 0 Then Exit Sub
    sSQLStm = "UPDATE tblEnrolment " & _
              "SET CourseID = " & cboStartDate.ItemData(cboStartDate.ListIndex) & ", " & _
                  "[Last Changed Date] = '" & Now & "', " & _
                  "[Last Changed By] = '" & gsUserID & "' " & _
              "WHERE EnrolmentID = " & EnrolmentID
    cmdEnrolment.ActiveConnection = gconDatabase
    cmdEnrolment.CommandText = sSQLStm
    cmdEnrolment.Execute
    StatusBar1.Panels(1).Text = "Enrolment Record Changed"
End Sub

---

Nah we'll try and elliminate 1 thing at a time! :)

believe it or not we're still getting it! It lasted about a day and half and we had our hopes up, but then a phone call and my head sunk..........

do you think I should change all the ADO commands too?

Yeah i'd try it!

This is worth a read from MSDN:

Quote:

---

If the command is not intended to return results (for example, an SQL UPDATE query) the provider returns Nothing as long as the option adExecuteNoRecords is specified; otherwise Execute returns a closed Recordset. Some application languages allow you to ignore this return value if no Recordset is desired.

---

I was getting the same type of error. It usually happened around 11:45am.

It turns out that one of the users was turning off her computer without shuting down properly before going to lunch.

Next time it happens, if possible, go to each of your users computer and see if anyone computer was just turned off.

I get the same message whenever i insert a database ive created.

erm whats jet 5 about? could be what i need?

Gary, Thanks, I'd never heard of that one before.

What exactly does that do? Is there a microsoft paper or anything I can read about it on?

Gary's confused i think!

Jet4.0 = Access 2000
Jet3.5 = Access97

The engine Type part of the connection string tells the connection which access version you are using. 5 is access 2000 4 is access 97

If you use Jet 4.0 and compact an access 97 db it compacts it into a access 2000 db you can also use the Engine Type part to compact into which ever version of access you want.

Gary - see the able computing links below ;) hehehe


Jet 3.51 is Access 97
Jet 4.0 is Access 2k (and 2002... apparently ;) )


Vince

ignore the provider part look more at the OLEDB:Engine Type part, this is what denotes the version you are using.

I'm confused!:confused:

Dont be gary was just stating that if your using Access 2000 and above use Jet4.0 else use jet3.51!

Check my ado beginners tutorial and check for anything that looks different. :)
Did you change your commands?

No I haven't changed the commands yet. Are you able to give me any advice as to how to change the command that I list about 10 threads above?

much appreciated,

stingrae

Your command seem fine!!

To me it seems your database is corrupting.

Do you replicate it or anything?

no, we don't replicate. what we have is the Backend.mdb and then two front ends.
The old one is Frontend.mdb which is a series of access reports and forms which simply has linked tables.
The other is what is being developed by myself which is frontend.exe which is in vb and will eventually replace the frontend.mdb

Recreate the database!!

Make a new one called db1.mdb and import all the tables!

Then try your vbapp on db1.mdb not the existing .mdb!

Other than that i'm stuffed for ideas!

b :)

Ok im a really big newbie, what do i need to do to get jet 4?

Just thought I'd let everyone know how this was resolved.

We ended up opening a case with Microsoft as the situation didn't go away. After reading many articles on the knowledge base, they pointed us to this one:

Q300216

It mentions a problem about opportunistic locking. By simply adding one little key in the registry of the server, the whole problem has gone. It's now been over three weeks without an incident.

Thanks heaps to all of you who helped on this.