Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2146
  • Last Modified:

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.
0
maucher
Asked:
maucher
  • 3
  • 3
2 Solutions
 
Anthony PerkinsCommented:
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
leonstrykerCommented:
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
 
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now