How to Detect Encoded Status of an Older Access Database

[Bilbo_Baggins_Esq]

Hi All,

Hope every one is great!

I'm wondering if there is any code a person could use to programatically detect the encoded status of an earlier version of an Access database?

I've done some looking and some searching and not successful in finding anything specific.

Any suggestions would be most appreciated!

Thanks,
Bilbo

 

[JHB]

..... the encoded status of an earlier ....?

What are you think of here?

 

[Bilbo_Baggins_Esq]

is that SPAM above this post^?
NOt sure about that.

PLEASE ALL, I REALLY need some help here!
Maybe if I elaborate.

I am new to a team responsible to process migration of earlier Office format files to files formatted for Office 2010.
There is a third party tool they bought to perform bulk conversions but it does not always work very well.
My position is to understand and possibly manually remediate the issues with files on which it fails.

The team works largely from within Excel, or at least, Excel is the easiest tool for them to utilize so Ideally I’m looking at Add-In (XLAM) the users can launch to examine a few of the attributes the third party tool is failing to detect.

Without distracting too much, as an example, this third party tool tends to sometimes fail to detect if a VBA Project is locked.
I wrote a simple add-in that opens a file, checks the status of that attribute, and then reports it back to the user in a spread sheet.
Nothing fancy. nothing terribly complex, but useful.

One of the other, many, challenges has been to detect the encoded/decoded status of an earlier version of Access database.
So, now I’d like to tackle this encoded/decoded project.

I’ve naturally easily been able to open an access database from within Excel no problem.

From within Excel, this simple code is successfully opening an .MDB

Sub OpenAccessTest()
On Error GoTo OpenAccessTest_Err

Dim AccessObj As Object
Dim AccessPath As String

AccessPath = "C":\MMDemoDecoded.mdb"

Set AccessObj = CreateObject("Access.Application")

AccessObj.Application.OpenCurrentDatabase(AccessPath, True) = True

OpenAccessTest_Err:
MsgBox Error

End Sub

But that code also returns the following error:

Run-time error ‘451’:
Property let procedure not defined and property get procedure did not return an object

Of course, I’ve looked up this error:
You specified an operation or property that is exclusive to collections, but the object isn't a collection.
Check the spelling of the object or property name, or verify that the object is a Collection object. Also look at the Add method used to add the object to the collection to be sure the syntax is correct and that any identifiers were spelled correctly.

Please, does anybody have some help for me here?
Unfortunately, I’m not clear at all what a “collection” is or how the object I created is or is not part of a collection.

Alternatively, if you have any code you can help with that will open MDBs while suppressing the conversion dialog box, that would be tremendous.

Thanks,
bilbo

 

[JHB]

From within Excel, this simple code is successfully opening an .MDB

Sub OpenAccessTest()
On Error GoTo OpenAccessTest_Err

Dim AccessObj As Object
Dim AccessPath As String

AccessPath = "C":\MMDemoDecoded.mdb"

Set AccessObj = CreateObject("Access.Application")

AccessObj.Application.OpenCurrentDatabase(AccessPath, True) = True

OpenAccessTest_Err:
MsgBox Error
End Sub

The code above has never been able to run with the syntax shown here, there are 2 main errors in the code + one disadvantage of the code. Sorry

1. There is one " to much in here:
AccessPath = "C":\MMDemoDecoded.mdb"

2. You try to set AccessObj.Application.Ope... to True.
AccessObj.Application.OpenCurrentDatabase( AccessPath, True) = True

3. Your code is running into the error handle part each time the code run. You have to exit the procedure (Sub) before the error handle part if no error occurs else the message box will display.

So the code should be.

Sub OpenAccessTest
    On Error GoTo OpenAccessTest_Err
    Dim AccessObj As Object
    Dim AccessPath As String

    AccessPath = "C:\MMDemoDecoded.mdb"
    Set AccessObj = CreateObject("Access.Application")
    AccessObj.Application.OpenCurrentDatabase (AccessPath)
    Exit Sub

OpenAccessTest_Err:
    MsgBox Error
End Sub

Alternatively, if you have any code you can help with that will open MDBs while suppressing the conversion dialog box, that would be tremendous.

Thanks,
bilbo

is that SPAM above this post^?

Do you post SPAM?

 

[Bilbo_Baggins_Esq]

1. There is one " to much in here:
AccessPath = "C":\MMDemoDecoded.mdb"

Yes, I know.
That was an accidental typo from when I originally created the post here.
It (the extra qoute) does NOT exist in the actual code.

2. You try to set AccessObj.Application.Ope... to True.
AccessObj.Application.OpenCurrentDatabase( AccessPath, True) = True

I only added the "=True" because if I only use this:

AccessObj.Application.OpenCurrentDatabase(AccessPath, True)

I get the following result:

Compile error:
Expected: =
[COLOR="Red"]AccessObj.Application.OpenCurrentDatabase(AccessPath, True)[/COLOR]

3. Your code is running into the error handle part each time the code run. You have to exit the procedure (Sub) before the error handle part if no error occurs else the message box will display.

Yes, I know this is correct.
Even though yes, the message box will fire, if there is no error encountered, the message box is empty.
If there is an error, it will be in the message box.
My hope was to analyze the message box contents for various scenarios.
This hope was based on seeing messaging I receive from Access when I open earlier databases directly from an Explorer window.
However, for whatever reason, when I open a database via code within Excel (with the Access.Application object) apparently I get no opening version messages.
But I’ll discuss this below.
Thank you for your observations!

All this said, I do notice a potentially critical difference between the code I posted and the code you posted.

My line:

AccessObj.Application.OpenCurrentDatabase(AccessPath, True) = True

Your line:

AccessObj.Application.OpenCurrentDatabase(AccessPath)

Aside from the “=True” as discussed above, the real difference is this:

(AccessPath, True)

vs.

(AccessPath)

When I use your line, the database opens without error or message of any kind.

However, I get spanked in these forums sometimes for not reviewing the help and instruction manuals so in this case, I actually did look up “OpenCurrentDatabase” in the Object Browser, and it shows the following syntax:

expression.OpenCurrentDatabase(filepath, Exclusive, bstrPassword)

My thinking was, that I would need to open the databases exclusively, hence adding the “, True” to the line.
However, it appears that adding that attribute is actually what was firing the error I mentioned in item number 2 above.
When I remove it, I get no compile error and I also get no error when the database opens (the original 451 error).

So, for now I guess I’ll move forward with what I’ve got.
Thanks for your help.
I’m not sure why the syntax for opening exclusively fires the error, but I’m not sure if I even need to be concerned about opening exclusively.

As for the SPAM comments:
No, I’m not SPAM, but I couldn’t gather much from your first comment in the thread. I thought your post might have been SPAM.

 

[JHB]

If you want to open in exclusive mode then use, (remove "()"):

AccessObj.Application.OpenCurrentDatabase AccessPath, True