Very Computer

Background:
    VB6 and SQL Server 7.0

I have an app that I've created a data environment.  The forms are using
adodc and are set to the data         environment recordsets.  So far no
problem (or not too many).

I've deleted the connection string and record source from all of the forms
and it is using the Data Environment via the adodc.

I've deleted the server, userid and password items from my Data Environment
connection object and want to set these items in code.

I have a login form where these items will be supplied by the user and
placed in a connection string.  Seems so simple. . .

My code is placed on the OK button of the login form.

        sADOConnect = "PROVIDER=MSDASQL;Driver=SQL Server;Persist Security
Info=True;"
        sADOConnect = sADOConnect & "Server=" & txtipaddress & ";"  '(
lookup fieldon the form)
        sADOConnect = sADOConnect & "UID=" & txtUserName & ";"
'(txtUserName is a field on the form)
        sADOConnect = sADOConnect & "PWD=" & txtPassword & ";"
'(txtPassword is a field on the form)

        Unload Me

Among the things I have tried are:

attempt 1:
        DataEnvironment1.HDConnect.ConnectionString = sADOConnect
        DataEnvironment1.HDConnect.Open

attempt 2:
        'DataEnvironment1.Connections(1).Open sADOConnect

attempt 3:
        DataEnvironment1.HDConnect.Open sADOConnect

Then I call the splash screen and the DataEnvironment sends everything
needed to the app.
        Call frmStartupSplash.Show

There must be a way to do this.  I've seen all kinds of examples done in
code, just that nothing seems to work in this situation.  I've even left the
server, userid and password in the DE.connection object and tried to set it
and still get errors about the "supplied provider is different from one
already in use."

There aren't too many examples tieing the DE together with the adodc (which
works very nicely) but I'm developing it on one server with one ipaddress,
testing remotely with another ipaddress and the user is behind the firewall
and uses another ipaddress.  It's imperative that I can change that quickly
and easily without having to touch too many objects.  It may also come to
pass that the users will eventually be using it remotely from their office
and need to put in the ipaddress of the firewall instead of the local one.

Please don't talk DSNs if it's possible to do it the other way.  We are
looking at rolling this app out all over the country and building DSNs for
everyone is not the best senario, plus the local/remote situation still
requires changing the connection string of the DE.

Any clues as to why it's erroring and what can I do about it?  I've looked
through all of my resources and the Microsoft site; the error doesn't seem
to have much explanation or fixes.

N. Schmidt

Well, I tried one more combination and lucked out:

    I placed code in the DataEnvironment_Initialize()
            DataEnvironment1.HDConnect_connectionString = sADOConnect
(sADOConnect is a global var, we
won't talk naming conventions)
    Took out code in the HDConnect_WillConnect
            previously had ConnectionString = sADOConnect

    Used >         DataEnvironment1.HDConnect.Open sADOConnect (in the OK
button of the Login Form)

Connection worked for all but one form (master/details); tracked it to a
cursor problem with the E_FAIL message.  Checked the cursor on the
DataEnvironment1 command and the form, they didn't agree, made them the same
and how about that!! It works!!

Hope this can help someone else!

N.

Quote:> Background:
>     VB6 and SQL Server 7.0

> I have an app that I've created a data environment.  The forms are using
> adodc and are set to the data         environment recordsets.  So far no
> problem (or not too many).

> I've deleted the connection string and record source from all of the forms
> and it is using the Data Environment via the adodc.

> I've deleted the server, userid and password items from my Data
Environment
> connection object and want to set these items in code.

> I have a login form where these items will be supplied by the user and
> placed in a connection string.  Seems so simple. . .

> My code is placed on the OK button of the login form.

>         sADOConnect = "PROVIDER=MSDASQL;Driver=SQL Server;Persist Security
> Info=True;"
>         sADOConnect = sADOConnect & "Server=" & txtipaddress & ";"  '(
> lookup fieldon the form)
>         sADOConnect = sADOConnect & "UID=" & txtUserName & ";"
> '(txtUserName is a field on the form)
>         sADOConnect = sADOConnect & "PWD=" & txtPassword & ";"
> '(txtPassword is a field on the form)

>         Unload Me

> Among the things I have tried are:

> attempt 1:
>         DataEnvironment1.HDConnect.ConnectionString = sADOConnect
>         DataEnvironment1.HDConnect.Open

> attempt 2:
>         'DataEnvironment1.Connections(1).Open sADOConnect

> attempt 3:
>         DataEnvironment1.HDConnect.Open sADOConnect

> Then I call the splash screen and the DataEnvironment sends everything
> needed to the app.
>         Call frmStartupSplash.Show

> There must be a way to do this.  I've seen all kinds of examples done in
> code, just that nothing seems to work in this situation.  I've even left
the
> server, userid and password in the DE.connection object and tried to set
it
> and still get errors about the "supplied provider is different from one
> already in use."

> There aren't too many examples tieing the DE together with the adodc
(which
> works very nicely) but I'm developing it on one server with one ipaddress,
> testing remotely with another ipaddress and the user is behind the
firewall
> and uses another ipaddress.  It's imperative that I can change that
quickly
> and easily without having to touch too many objects.  It may also come to
> pass that the users will eventually be using it remotely from their office
> and need to put in the ipaddress of the firewall instead of the local one.

> Please don't talk DSNs if it's possible to do it the other way.  We are
> looking at rolling this app out all over the country and building DSNs for
> everyone is not the best senario, plus the local/remote situation still
> requires changing the connection string of the DE.

> Any clues as to why it's erroring and what can I do about it?  I've looked
> through all of my resources and the Microsoft site; the error doesn't seem
> to have much explanation or fixes.

> N. Schmidt

The following, from
http://www.inwa.net/~m3047/vb/dow/data-environment.html, works for me:

' This allows us to override connection information at runtime, while
' still using the design-time connection at design-time.
Public RuntimeConnectionString As String

Private Sub DataEnvironment_Initialize()
    RuntimeConnectionString = ""
End Sub

Private Sub IOS_ACC_WillConnect(ConnectionString As String, UserID As
String, Password As String, Options As Long, adStatus As
ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
    ' Here we set the connection string with the override value if there
    ' is one.
    If (RuntimeConnectionString <> "") Then
        ConnectionString = RuntimeConnectionString
    End If
End Sub

There are other ways of doing it.

--

Fred Morris