CurrentDb.Properties("MDE")

[PhilOfWalton]

I have a number of DBs that check to see if the database is an AccDb / Mdb or Accde / Mde

A function uses

CurrentDb.Properties("MDE")
 

to return the value. This has worked for years and years.
Suddenly I am getting an error that the property is not found.

AFIK it used to be in the DAO 3.6 library but is now in the Microsoft Office 14.0 Access Database engine object library. I have a reference to the latter, but if I try to add DAO 3.6, I get a Name conflicts with existing project or library.

Have MS in their wisdom, recently changed th Office 14.0 Access Database engine object library?

Has anyone else had this problem?

Interestingly, I have a AccDe version of a database that has never been touched, and that works OK

Phil

[NeoPa]

I really don't know Phil.

In order to avoid the error though, you could use an intermediate function procedure that returns some default value when a property doesn't exist. EG. GetProperty().

I use a module for all such stuff. My GetProperty() function will default to using the result from CurrentDb() if no object is specified.

Option Compare Database
Option Explicit
 
Private Const conNoProp As Integer = 3270
Private Const conVBToDB As String = "\2|3\3|4\4|6\5|7\6|5" & _
                                    "\7|8\8|10\11|1\14|20\17|2"
 
'SetProperty() requires that either intPType is set explicitly OR that
'              varPVal has a valid value if a new property is to be created.
Public Sub SetProperty(strPName As String _
                     , varPVal As Variant _
                     , Optional ByVal objVar As Object _
                     , Optional intPType As Integer = -1)
    Dim prpVal As DAO.Property
 
    Call SetObj(objVar)
    If PropertyExists(strPName, objVar) Then
        objVar.Properties(strPName) = varPVal
    Else
        If intPType = -1 Then intPType = DBVal(varType(varPVal))
        Set prpVal = objVar.CreateProperty(strPName, intPType, varPVal)
        Call objVar.Properties.Append(prpVal)
    End If
End Sub
 
'GetProperty() returns the value of the specified property if found.
Public Function GetProperty(ByRef strPName As String, _
                            Optional ByVal objVar As Object) As Variant
    Call SetObj(objVar)
    GetProperty = Null
    If PropertyExists(strPName, objVar) Then _
        GetProperty = objVar.Properties(strPName)
End Function
 
'PropertyExists() returns True if the property exists and False if it doesn't.
Public Function PropertyExists(ByRef strPName As String _
                             , Optional ByVal objVar As Object) As Boolean
    Dim varTest As Variant
 
    On Error GoTo ErrorHandler
    Call SetObj(objVar)
    PropertyExists = True
    varTest = objVar.Properties(strPName)
    Exit Function
 
ErrorHandler:
    If Err <> conNoProp Then
        On Error GoTo 0
        Resume
    End If
    PropertyExists = False
End Function
 
'DelProperty() deletes the property if it exists.
Public Sub DelProperty(ByRef strPName As String _
                     , Optional ByVal objVar As Object)
    Call SetObj(objVar)
    If Not PropertyExists(strPName, objVar) Then Exit Sub
    Call objVar.Properties.Delete(strPName)
End Sub
 
'SetObj() sets objVar to CurrentDb() if it's not already set.
Private Sub SetObj(ByRef objVar As Object)
    If objVar Is Nothing Then Set objVar = CurrentDb()
End Sub
 
'DBVal() returns the value of the Type that is used in DAO
'  from the VBA equivalent.
Private Function DBVal(intVBVal) As Integer
    Dim intX As Integer
 
    intX = InStr(1, conVBToDB, "\" & intVBVal & "|")
    DBVal = Val(Mid(conVBToDB, intX + Len(intVBVal) + 2))
End Function

[PhilOfWalton]

Thanks for that, I use something similar myself to add properties, but my understanding is that MDE property is something that is built into the Access database library.

Does it work for you, and which version of the Microsoft Office 14.0 Access Database engine object library are you using?
Mine is version 14.0.7188.5002 modified 4th September 2017, which is about the time my problems started.

It is in C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE14\MSO.DLL.

I would be pleased to hear if anyone else has this version and if so, if they print

Print CurrentDb.Properties("MDE")
 

what results they get. Even more interesting is if someone with an earlier version tries the code.


Many thanks,

Phil

[ADezii]

It is my understanding that if CurrentDB.Properties("MDE") does NOT return the Value of 'T' the DB does NOT contain editable Source Code, thus a *.mde, *.accde. If the Error of 3270 is generated (Property not found), the Database contains editable Source Code and is NOT a *.mde, *.accde.

Public Function fHasSourceCode()
On Error Resume Next
 
fHasSourceCode = (CurrentDb.Properties("MDE") <> "T")
 
'Property Not Found
If Err.Number = 3270 Then fHasSourceCode = True
 
On Error GoTo 0
End Function
 

[PhilOfWalton]

Thanks, ADezii, but I think you are missing the point.

On typing Print CurrentDb.Properties("MDE") in the debug window, I get Error 3270 Property not found, so I can't find it's value.

As I said this has worked for years until a few weeks ago when it appears that a new version of Microsoft Office 14.0 Access Database engine object library was installed automatically.

Thanks

Phil

[ADezii]

Sorry for missing the point, Phil. I was thinking along the lines that if a given DB is not a *.mde or *.accde then the *.MDE Property doesn't even exist and will always generate Error 3270.

[NeoPa]

There are some properties that always exist though ADezii. I have to say I was new to this one so I tried it on a 2003 install and that also reported "Property doesn't exist", as does my Access 2010 application.

[PhilOfWalton]

What version and date of MSO.DLL are you using?
Thanks

Phil

[NeoPa]

As I say, I tested it on two separate systems (One with Microsoft DAO 3.6 Object Library set and the other with Microsoft Office 14.0 Access database engine Object Library set). Having said that, how would I even go about checking which version of MSO.DLL is in use - if at all? You know the files that are associated with each of the libraries listed nearly all get truncated so you can't actually see what relates to what. I know this is fixed in the latest version (2016) but 2010 & 2003 are not that.

[PhilOfWalton]

Hi

Use File Explorer to Go to the location mentioned earlier, right click on the MSO.DLL and on the details tab, you see the version & date.

I read somewhere that the MDE property moved fro DAO 3.6 to the MSO.DLL. I had assumed this was correct, but Culpa Mia that was wrong information. The correct file is ACEDAO.DLL which was last modified in December 2015, so that doesn't sound as if it is the problem.

Sorry to have misled everybody.

I have tried to de-register the ACEDAO.Dll but get an error that it was loaded, but the entry point DllUnregisterServer was not found

Cheers

Phil

[NeoPa]

PhilOfWalton:
Use File Explorer to Go to the location mentioned earlier, right click on the MSO.DLL and on the details tab, you see the version & date.

That's fine for some versions, but doesn't exist on my 2003 setup. Even if it exists how can I be sure it's the one in use. Never mind. I guess it's moot now so not worth pursuing.

[PhilOfWalton]

I don't want you to all fall about laughing, but ... if you must.

The solution to the problem was that I had Break on All Errors set as the error trapping option in the editor options.
An Accdb doesn't have the property CurrentDb.Properties("MDE") so throws an Error 3270.

Really sorry for wasting so much of your time

Grovelling apologies

Phil

[NeoPa]

Hands up anyone who's never made such an error. No takers? No. I thought not.

[isladogs]

I realise this thread is 5 years old but an interesting thing happened today when I was testing a bug reported at another forum.

Bear with me whilst I explain:
The bug in Access 365 v2207 was that using a wizard (query/form or report) on a table with an MVF or attachment field caused Access to hang then crash. Creating any of those objects manually worked without issue

As I wrote in that thread I was tempted to say this is yet another good reason to avoid both MVFs and attachment fields,
However, more helpfully, I reported the issue to the Access team this afternoon.

The bug is actually fixed in v2208 (released a few hours later) ...though replaced by a new bug!

The point relevant to this thread is that testing the bug somehow created the MDE property & set it to T as in an ACCDE file
As a result I had typical ACCDE functionality (restricted menus ; unable to export / import objects etc)
However, it was still an ACCDB file & the code was still viewable

Luckily I was able to restore ACCDB functionality as the MDE property was NOT read only in this case

All very weird!

[isladogs]

Apologies for posting a link to another forum.
I've since written an article about this strange occurrence: Oops...how did that make an ACCDE file?
A member of the Access team has now offered to investigate the cause.

[NeoPa]

I've been noticing (some of) the work you've been doing Colin. It doesn't fail to impress :-) Keep driving forward as you are doing.