We help IT Professionals succeed at work.

Problem MDB not Functioning the same on different machines

mlcktmguy
mlcktmguy asked
on
Medium Priority
222 Views
Last Modified: 2012-05-12
I work with a business partner in developing Access application.  We have worked together for over 15 years and always operated in the same manner, which is exchanging MDB's back and forth between machines to make enhancements or changes.
Usual workflow:  I work on the MDB, complete my changes send it to him.  He makes his changes and sends it back to me.  This has worked seamlessly, without issue, for a very long time thru several different versions of Access.

Recently I purchased a new Win 7 Ultimate system.  Now there are issues every time I send him an MDB.  I don't have issues with the revised MDB's that he sends to me.

If I revert back to developing on my old WIn XP machine there is no issue.  However on the new machine iIt happens every time and we have seen a pattern with the error and solution but have no idea how to resolve the problem permanently.

I have also had the same issue sending MDB's to client running Win XP/Access 2003.  I serviced these clients on my old XP machine for years without issue.

The error always occurs on a  "Set rs = New ADODB.Recordset" statement.  The message is something along the lines of "does not support automation".  

The foolproof way (so far)  of resolving the issue is for him to go into the "Tools","References" option.  He then clicks/checks on the first non clicked reference and then clicks/unchecks the reference.  The end result is no change to the reference list.  However, for some reason the problem goes away  .

The reference list:
Visual Basic For Applications
Microsoft Access 11.0 Object LIbrary
Ole Automation
Microsoft Active X Data Objects 2.5 Library
Microsoft DAO 3.6 Object LIbrary


My machine: Win 7 Ultimate, Access 2003 SP3

His machine XP Professional, Access 2003 SP3

Any ideas on what the issue is or how to resolve this ongoing problem?
Comment
Watch Question

President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017
Commented:
<<Any ideas on what the issue is or how to resolve this ongoing problem? >>

  Microsoft broke ADO:

An ADO application does not run on down-level operating systems after you recompile it on a computer that is running Windows 7 SP 1 or Windows Server 2008 R2 SP 1 or that has KB983246 installed

http://support.microsoft.com/kb/2517589

Jim.

Author

Commented:
Thank your response and the link to steps on how to resolve the issue.

I am hesitant to take the steps to resolve the issue.  My concern is that it may have a negative impact on other MS development tools on the same machine.  I am planning to install and use Visual Studio .Net on this same machine and wonder if the steps to resolve the MS Access issue migh cause problems there.

I realize that you may have no idea if there would be an impact, just thought I'd ask.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

 Well basically you only have two choices in regards to Access:

1. Use late binding with ADO.
2. Always compile and create a MDE on a Pre Win 7 SP1 computer or VM.

As far as the fix, you'll notice that it broke ADO in .Net as well, but there you can use method 1 as outlined in the article.

 But if you do nothing, you'll have the same problem with .Net as you do with Access.

Jim.

Author

Commented:
Once again, thanks for your quick response.  

For some clients we distribute the actual MDB's   For others we package and MDE for distribution.

I don't understand option "1. Use late binding with ADO.".  How do I accomplish that?  

Does this mean not to 'Compile' the code using the "Debug', Compile option.  Or even perhaps running a 'Decompile' on the MDB prior to distribution.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<I don't understand option "1. Use late binding with ADO.".  How do I accomplish that? >>

  It means you don't set a reference to it in the references and by not doing that, you declare things as a generic object in your code rather then as something specific like this:

Dim rst As Object
Set rst = CreateObject("ADODB.Recordset")
 
rathen then doing this:

  Dim rst As ADODB.Recordset

  However using late binding means two things:

1. You loose intellisense when coding.
2. You get a 10-15% performance hit when executing the code.

So some developers will do this:

Const EarlyBind = 1

#If EarlyBind Then
Public Function GetMyRecordset(CurrentConnection As ADODB.Connection) As ADODB.Recordset

Private rs As ADODB.Recordset
#Else
Public Function GetMyRecordset(CurrentConnection As Object) As Object

Private rs As Object
Const adUseClient As Long = 3
Const adOpenStatic As Long = 3
#End If

Set rs = CreateObject("ADODB.Recordset")
With rs
  Set .ActiveConnection = CurrentConnection
  .CursorLocation = adUseClient
  .CursorType = adOpenStatic
  .Open "SELECT * FROM myTable;"
End with

Set GetMyRecordset = rs

End Function


  Setting EarlyBind to true/false to get the best of both worlds.

Jim.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Does this mean not to 'Compile' the code using the "Debug', Compile option.  Or even perhaps running a 'Decompile' on the MDB prior to distribution.>>

  Yes, compile using the Debug option, which must be done before you create a MDE and should do before distributing a MDB.

Jim.

Author

Commented:
Thanks for the answer(s), exactly what I needed.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.