VB6: Cannot change connection provider for Data Environment connection

VB6 program has a Data Environment with an ADO connection "conArchive".  In VB6 development environment, the provider is set to "Microsoft Jet 4.0 OLE DB Provider" along with an .mdb Data Source file.

When the program starts, it may need to change to a SQL database provider with this code:

    If DeApcoErs.conArchive.State <> adStateClosed Then
        DeApcoErs.conArchive.Close
    End If
    DeApcoErs.conArchive.Provider = "Microsoft OLE DB Provider for SQL Server;"

which causes this error: "Supplied provider is different from the one already in use."

Trying to set the connection string to a SQL provider, source, etc. also causes and error: "multiple step error...".

Regards.
maucherAsked:
Who is Participating?
 
leonstrykerConnect With a Mentor Commented:
This is one of the problems witht he Data Enviroment object.  I recently posted a question about DE which you may want to take a look at.

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20766815.html
0
 
Anthony PerkinsConnect With a Mentor Commented:
Try setting the Data Environment to nothing and re-creating prior to reopening with a new provider.

Anthony
0
 
maucherAuthor Commented:
I tried each of these in DataEnvironment_Initialize()

     Set DeApcoErs.conArchive = Nothing      ' returns "Invalid use of property"
     Set DeApcoErs.conArchive = New ADODB.Connection

     Set DeApcoErs = Nothing
     Set DeApcoErs = New DataEnvironment      
     ' returns "Class does not support automation or does not support expected interface"

DeApcoErs  has several connections and many commands in it.  I would like to do away with it, but that's a big change for later.

maucher
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
leonstrykerCommented:
maucher

In order to do switch the DE you will have to destroy all of the Command objects, set the new DE and recreate the Commands.
0
 
maucherAuthor Commented:
The Data Environment is statically defined in the project / Designers.  It contains 8 connections and the connections contain about 100 commands.  

Can I just work with the connection DeApcoErs.conArchive?  When I try to set the connection to nothing, "Invalid use of property" error occurs.

Still get "Supplied provider is different from the one already in use." for this statement:

    Private Sub DataEnvironment_Initialize()
        DeApcoErs.conArchive.Provider = "Microsoft OLE DB Provider for SQL Server;"
    end sub

Is there a way to change the provider that is in use without changing the whole data environment?

maucher
0
 
maucherAuthor Commented:
I agree that the Data Environment has some limitations as the posted question and linked article point out.  

My solution is this work-around which sets the connection string to a different valid Jet database, opens and closes, and then changes the provider to the desired SQL Server database.  It works, but I'm not sure why:

    DeApcoErs.conArchive.ConnectionString = DeApcoErs.ConCases.ConnectionString
    DeApcoErs.conArchive.Open
    DeApcoErs.conArchive.Close
    DeApcoErs.conArchive.Provider = "Microsoft OLE DB Provider for SQL Server;"

Thanks,
maucher
0
 
leonstrykerCommented:
I think it may work because the Commands are disengaged while opening a different Jet database.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.