RESOLVED ** Unrecognizable database format

**stingrae** · Apr 16th, 2002, 10:58 PM

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.

**Beacon** · Apr 17th, 2002, 12:15 AM

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

**stingrae** · Apr 21st, 2002, 07:07 PM

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?

**MOHH** · Apr 21st, 2002, 07:11 PM

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

**stingrae** · Apr 21st, 2002, 09:36 PM

Regan,

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

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

**stingrae** · Apr 24th, 2002, 02:22 AM

HELP!!!!

Anything else? We're desperate.

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

Thanks All

**Ecniv** · Apr 24th, 2002, 02:52 AM

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

**stingrae** · May 10th, 2002, 05:56 AM

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?

**RSINGH** · May 10th, 2002, 07:15 AM

How large is the mdb?

**stingrae** · May 10th, 2002, 07:34 AM

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.

**RSINGH** · May 10th, 2002, 07:38 AM

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.

**Beacon** · May 12th, 2002, 07:40 PM

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?

**QuigIey** · May 12th, 2002, 11:11 PM

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

**Beacon** · May 12th, 2002, 11:22 PM

Are you using VB??

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

b

**stingrae** · May 12th, 2002, 11:37 PM

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

**Beacon** · May 12th, 2002, 11:47 PM

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

**stingrae** · May 12th, 2002, 11:55 PM

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

**Beacon** · May 13th, 2002, 12:07 AM

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

**stingrae** · May 13th, 2002, 12:14 AM

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

**Beacon** · May 13th, 2002, 12:22 AM

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

**stingrae** · May 13th, 2002, 12:48 AM

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.

**Beacon** · May 13th, 2002, 12:59 AM

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.

**stingrae** · May 13th, 2002, 01:52 AM

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

**Beacon** · May 13th, 2002, 02:20 AM

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

**stingrae** · May 14th, 2002, 11:05 PM

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?

**Beacon** · May 15th, 2002, 12:19 AM

Yeah i'd try it!

This is worth a read from MSDN:

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.

**Ken Bradford** · May 15th, 2002, 10:35 AM

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.

**Jabba The Hunt** · May 15th, 2002, 01:32 PM

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

**Jabba The Hunt** · May 15th, 2002, 01:42 PM

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

**stingrae** · May 15th, 2002, 06:24 PM

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?

**Beacon** · May 15th, 2002, 08:05 PM

Gary's confused i think!

Jet4.0 = Access 2000
Jet3.5 = Access97

**Gary.Lowe** · May 16th, 2002, 07:17 AM

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.

**Ecniv** · May 16th, 2002, 07:19 AM

Gary - see the able computing links below

hehehe

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

)

Vince

**Gary.Lowe** · May 16th, 2002, 07:21 AM

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

**stingrae** · May 16th, 2002, 04:45 PM

I'm confused!

**Beacon** · May 16th, 2002, 07:50 PM

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?

**stingrae** · May 16th, 2002, 08:28 PM

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

**Beacon** · May 16th, 2002, 09:29 PM

Your command seem fine!!

To me it seems your database is corrupting.

Do you replicate it or anything?

**stingrae** · May 16th, 2002, 10:10 PM

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

**Beacon** · May 16th, 2002, 10:21 PM

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

Thread: RESOLVED ** Unrecognizable database format

Thread Tools

Display

RESOLVED ** Unrecognizable database format

unrecognizable database

Posting Permissions