Automation Error using mde made from another pc

[FuzMic]

Hi gurus

I created an mde using access02 in a pc with win7.ultimate.64bit It runs ok in this machine. When this mde is placed on another similar pc with win7.ultimate.64bit when it flag an 430 error

"Class does not support Automation or does not support expected interface."

If a fresh mde is made from the 2nd pc using access02, this error does not occur when run within the 2nd pc.

I remember it may have something to do with Activex Data Objects 2.8 Library as it referenced to it.

Appreciate comments.

 

[ByteMyzer]

Hello, FuzMic,

If one of the machines has Service Pack 1 and the other machine does not, they will have different IID's.

When an MDE is compiled, any VBA code references to ADODB objects are converted to their IID equivalents. Consequently, if that MDE is compiled on a Windows 7 SP1 machine, it will not run on a Windows 7 machine without SP1, and vice versa.

Solution: Use Late-Binding object code.

In your code, you will need to change any references to ADODB object types to use late-binding code, like the following example:

Old Code:

[COLOR="Navy"]Dim[/COLOR] cn [COLOR="Navy"]As[/COLOR] ADODB.Connection
[COLOR="Navy"]Dim[/COLOR] rs [COLOR="Navy"]As[/COLOR] ADODB.Recordset

[COLOR="Navy"]Set[/COLOR] cn = [COLOR="Navy"]New[/COLOR] ADODB.Connection

cn.Open "[your connection string here]"
[COLOR="DarkGreen"]' ...[/COLOR]

[COLOR="Navy"]Set[/COLOR] rs = [COLOR="Navy"]New[/COLOR] ADODB.Recordset

rs.Open "SELECT * FROM MyTable;", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
[COLOR="DarkGreen"]' ...[/COLOR]

New Code:

[COLOR="Navy"]Const[/COLOR] adOpenForwardOnly = 0, adLockReadOnly = 1, adCmdText = 1

[COLOR="Navy"]Dim[/COLOR] cn [COLOR="Navy"]As Object
Dim[/COLOR] rs [COLOR="Navy"]As Object

Set[/COLOR] cn = CreateObject("ADODB.Connection")

cn.Open "[your connection string here]"
[COLOR="DarkGreen"]' ...[/COLOR]

[COLOR="Navy"]Set[/COLOR] rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM MyTable;", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
[COLOR="DarkGreen"]' ...[/COLOR]

By redefining the objects in the Dim statements as generic objects, and setting them with the CreateObject method, the program gets the correct IID's for the object types at run-time, whether or not you have SP1 on the machine. This is known as late-binding. (Note, also, the need to define the AD constants within your code.)

To be on the safe side, once you have converted your code and internally defined the AD constants, you should also remove any project reference to the Microsoft ActiveX Data Objects x.x Library prior to compiling the MDE.

 

[FuzMic]

Thank you Myzer for the effort to clarify this mental hiccup on this phenomenon.

Do indulge me with the following pointers on the same issue.

1. What if i reference to msado60_Backcompat_i386.tlb instead the usual ActiveX DataObject 2.8 Library. I found that different version of windows referenced to different file eg in xpwin.sp3 it is msado15.dll while win7 use .tib.
2. I remember the 2 pc has the same win7 version and same sp but i will check and revert.
3. What if i create the .mde in an xpwin.sp3 using the default active 2.8, will this flip flop occur. I will try and revert too.

 

[FuzMic]

Just FOUND
mde created in xpwin.sp3 has no automation error in win7.ultimate without any updated SP.