VB Forum / Database Access / December 2007

I strongly advise that you do not use the "As New" syntax. When used it
causes VB to insert the following hidden code everywhere 'CON' is used.
   If CON Is Nothing Then
         Set CON = New ADODB.Connection
   End If
This is not only wasteful, it can also disguise problems, simply because it
can never be Nothing or rather "invalid" - you will always get one. But
maybe not the one you think.
Change to the following...
       Dim CON As ADODB.Connection
       Set CON = New ADODB.Connection

Your problem is the same, the Interface you are using isn't the one you
think, but for a very different reason. It has to do with COM and the
details are too much to cover here. So what follows is just a quick
explanation to help you get pointed in the right direction

In the Dimension Statement
     Public CON As New ADODB.Connection
"ADODB.Connection" is a symbol name for the Interface the project has
included as a Reference.
In the CreateObject call "ADODB.Connection" is a ProgID or AppID for the
object you want to instance.
They look the same, but they are not. Note the quotes, the latter is a
string which the COM library uses to look-up the component/interface to use
in the Registry:
Take a look at HKEY_CLASSES_ROOT\... you will see them all listed.

[Different version of an Object are shown as an additional postfix as in
   ADODB.Connection.1, ADODB.Connection.2.5, etc.]

In this case since there is no version indicated. You get whatever the
Registry has set up as the independent version. This is usually the latest
'n greatest version loaded.
That is if ADO 2.5 is installed - "ADODB.Connection" will point to
"ADODB.Connection.2.5", if 2.8 it will be "ADODB.Connection.2.8", etc.

Now exactly what the problem is depends on what you are doing, but consider
this scenario as an illustration of what might be happening.
The project references ADO 2.8
You call the following ...
     Dim CON = As New ADODB.Connection
         ' vb has essentially read the typelib it is compiled with
         ' and set up a symbol table to work with the ADO 2.8 Interfaces
[Note: All ADO versions use the same component msado15.dll BUT that
component supplies multiple ADO interfaces.]
       Set CON = CreateObject("ADODB.Connection")
          ' the target box has ADO 2.6 installed
          ' a connection object is created and a reference to it is set
          ' to the Reference Variable - "CON"
          ' But this connection object manages the 2.6 Interface - NOT a
2.8 one.
Let's say you later create a Recordset using the following...
   Dim rs As ADODB.Recordset
           ' you have a reference variable to the ADO 2.8 Recordset
    rs.Open "database_source", CON
            ' Note: you just called a method an ADO 2.8 object and passed
it
            ' a parameter object which is using a ADO 2.6 Interface
            ' What may happen is simply 'undefined'

Now you can usually get away with this, because all the ADO interfaces are
built to be backward compatible. But it is a bad idea.

My suggestion is to remove all CreateObject calls and replace them with a ..
    Set CON = New ADODB.Connection
    CON.Open ...

Be sure to bracket with error handling code, I bet you will quickly discover
the specific problem on the errant machines.

hth
-ralph

I got so caught up in possible issues with CreateObject, I negleted to point
out where the "As New" construct can be directly responsible for this error:
adErrObjectOpen: "Cannot perform operation while object is open"

Example this air code with the hidden part inline:
    Dim CON As New ADODB.Connection
    ...
    If CON Is Nothing Then Set CON = New ADODB.Connection
    Set CON = CreateObject("ADODB.Connection")
    If CON Is Nothing Then Set CON = New ADODB.Connection
    CON.Open "..."
     ...         ' do stuff with CON
     ' CON goes out of scope or is set to nothing
     .... ' but you attempt to use it again
    If CON Is Nothing Then Set CON = New ADODB.Connection
     rs.Open "....", CON
     Error because you get a new valid CON but it isn't open.

-ralph