Solved Unrecognised Database Format (1 Viewer)

[kevnaff]

I used to have frequent problems with database corruption, which ended with a major office update last year. So the usual advice applies, make sure everyone is running the latest version of Access and update the old MDB files..

Hi Roger.

How would you go about updating the old MDB files?

conception native has advised me to create a new blank database and import all objects in to it, is this what you've done too?

 

[kevnaff]

well.....just a few things first:



what I mean is that the "architecture" or "structural layout" of new files changed between 2007, 2010, 2016 versions etc... for instance, the introduction of split forms and data macros years ago. so, in essence what I mean are the types of objects being used in a file and what version of access is being used to try and open, read, and use them. but that's just one problem I've noticed. there are probably many others. I think the overall answer here is that no one really fully understands why this error occurs and it can probably happen for more than one reason - outside of the office bug that microsoft has acknowledged.

also,



re-distributing a front end should not lose any data whatsoever because tables should not be in a front end.



what I suggested was to create new files for everyone. everything new, doesn't matter who is using it or weather it is front or back end. all files brand new and make sure everyone is using the same version of access to run your application. that's only me though. I have never seen anyone else here make that suggestion but that's what I have always done. That way, you guarantee yourself minimal inconsistencies with regard to pretty much anything that should be off base. Does that make sense?

and no, I do not think there is a workaround. I think you have to go all in to try and fix it. although it is your app, not ours.

Hi conception,

So the mdb error continues to occur, but less frequent than previous.

I am going to follow your advice on creating new files. So I opened up our version of Access which is the 2010. I have gone to import the backend data in to a new database, and have now come across the following message:

If I click OK, the import seems to work OK.

Have you ever come across this issue?

 

[RogerCooper]

I had converted the MDB years ago using an older version of Access that could convert it directly. Check with Microsoft for a converter.

 

[isladogs]

As long as your MDB databases are A2000 format or later, you can still convert them to ACCDB using any recent version of Access up to and including 365.

 

[kevnaff]

I have created blank databases and exported the Frontend to one and the Backend to the other, so they're now both .accdb files.

When distributing a new version of the front end to users in the past, I have Saved and Published the backend and created a .mde file. I am now given the option to create a .accde file. When I attempt to do this I get the following message:

Is there another way to distribute this file to users or any tips regarding this?

Thanks all

 

[Gasman]

Does it even compile?

 

[kevnaff]

Does it even compile?

I've never compiled the database before.

I've just had a quick check on how to do this from the link below:

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

Decompile and Compact Your Microsoft Access Database to Improve Performance and Fix Corruption

www.fmsinc.com

I entered the following ("C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.exe" /decompile) and opened the .accdb file of the frontend.

I then went to Database Tools > Visual Basic > Debug > Compile Database

This brought up the following when trying to compile:

The full code is as follows:

Private Sub CommandCalculate_Click()
Dim strSQL As String
Dim db As Database
Dim REC As Recordset
Dim TotalRecords As Long

'Count All Devices In Service
strSQL = "SELECT * FROM [ImportEquipment] WHERE [Device in Service] = 1"
GoSub GetData
Me.TextInServiceCount = TotalRecords

'Count All Devices On PPM Scheduler
strSQL = "SELECT * FROM [ImportEquipment] WHERE [Device in service]=1 AND IsNull([Contract expires]) AND [Level 1 interval(mos)] >0"
GoSub GetData
Me.TextPPMCount = TotalRecords

'Count All Devices On Contract
strSQL = "SELECT * FROM [ImportEquipment] WHERE [Device in service] = 1 AND ([Contract expires]) Is Not Null"
GoSub GetData
Me.TextContractCount = TotalRecords

'Count All Devices BD Only
strSQL = "SELECT * FROM [ImportEquipment] WHERE [Device in service] =1 AND IsNull([Contract expires]) AND [Level 1 interval(mos)] = 0"
GoSub GetData
Me.TextBDCount = TotalRecords

Exit Sub


'Get data from ImportEquipment
GetData:
Set db = CurrentDb()
Set REC = db.OpenRecordset(strSQL, dbOpenDynaset)
REC.MoveLast
TotalRecords = REC.RecordCount
REC.Close
Return

End Sub


Any advice would be much appreciated.

I imagine once I correct this issue in the compile, there may be many more.

 

[Gasman]

Well I wasn't thinking of decompiling
I used to just use database, but now actually specify DAO.Database if I use it?
Same with recordset?

I still have it in a test DB and that still works?

Sub TestRecordset()
Dim db As Database
Dim rst As Recordset

Do you perhaps have both ADO and DAO references selected?
I have neither in that DB, so perhaps it defaults to one type, and with both, it does not know which?

The experts here will know much more about that.

 

[conception_native_0123]

This brought up the following when trying to compile:

you need to connect your references again.

 

[The_Doc_Man]

This can happen either of two ways:

1. You are missing references to DAO or they are broken
2. Libraries are scanned in order of appearance in that list when resolving references, so an EARLIER reference than the one pointing to DAO libraries might be missing/broken.

 

[cjman]

Several years ago I started having issues with database corruption. It was finally solved by putting the backend into SQL Server Express then a couple years later into SQL Server Standard. It was a learning curve that I do not regret. The corruption was caused by the size of the database backend. Just my two cents.

 

[GinaWhipp]

Hmm, can you post what References you do have checked?

 

[kevnaff]

The references that were checked in my new front end that I was trying to compile are:

I have opened the old front end and the references that are checked are:

I assume that I would have to add a reference for the Microsoft Office 14.0 Access database engine Object, and the only one that I can see that is close to it is the following in the new front end:

However, when I select it and hit OK. I get an "Error in loading DLL" message.

 

[arnelgp]

which office are you using x32 or x64?
you can manually browse for that file (acedao.dll)
and add it.

you can download a freeware Everything (a simple File finder)

i have x64 and it is located in:

C:\Program Files\Microsoft Office\root\Office16

 

[kevnaff]

Several years ago I started having issues with database corruption. It was finally solved by putting the backend into SQL Server Express then a couple years later into SQL Server Standard. It was a learning curve that I do not regret. The corruption was caused by the size of the database backend. Just my two cents.

That's something I'll definitely look in to going forward.

How big was the backend that you had?

This is only currently around 134mb, which doesn't seem like it would cause an issue, but I'm new to this so could be wrong.

 

[kevnaff]

which office are you using x32 or x64?
you can manually browse for that file (acedao.dll)
and add it.

you can download a freeware Everything (a simple File finder)

i have x64 and it is located in:

C:\Program Files\Microsoft Office\root\Office16

I am using x32.

I have searched for the acedao.dll and it is located in the following folder:

C:\Program Files (x86)\Microsoft Office\Office14

Does this need to be moved elsewhere?

 

[arnelgp]

you dont move it.
you add Reference to it but first Untick Microsoft Office 16.0 Access database Object.
also, you only have one installed mso, do you?

 

[GinaWhipp]

I might have missed it but the engine Object suggest this is a 2010 database or, at the very least you are opening in 2010. What version of Access are you opening it in? For reference see (scroll about half way down)...
https://www.access-diva.com/d5.html

 

[kevnaff]

you dont move it.
you add Reference to it but first Untick Microsoft Office 16.0 Access database Object.
also, you only have one installed mso, do you?

Hi arnel,

I have unticked the Microsoft Office 16.0 Access Database Object.

I have then browsed for the acedao.dll and selected open.

Am I meant to now see an option to now tick Microsoft Office 14 Access Database Engine as I don't currently see one?


Yes we only have Microsoft Office 2010 installed.


Thanks

 

[kevnaff]

I might have missed it but the engine Object suggest this is a 2010 database or, at the very least you are opening in 2010. What version of Access are you opening it in? For reference see (scroll about half way down)...
https://www.access-diva.com/d5.html

Hi Gina,

I am opening the database in Access 2010.

I created a blank database in Access 2010 and imported all objects from a 2003 - 2007 .mdb file, and saved this as a .accdb file.

So I believe it is therefore classed as a 2010 database.

Thanks