Until recently my clients' ancient legacy Access application has
worked fine.

However, their IT department installed Office 2010 and the legacy app
is broken (it was last developed in Access 2003).

I've managed to get most of it working, but there's one strange thing
that bugs me somewhat.

I have some code to create an ADODB.Connection.  Here's the code:

<code>
Private Function modOpenOLEDBConnection(ByRef rconSQL As
ADODB.Connection, _
                                        ByVal vstrOLEDBString As
String) As Boolean

On Error GoTo HandleErr

   ' Set the return value
   modOpenOLEDBConnection = False

   ' Open ADO connection
   rconSQL.ConnectionString = vstrOLEDBString
   rconSQL.Properties("Prompt") = adPromptComplete
   rconSQL.Open
   modOpenOLEDBConnection = True

</code>

At this point I know what the connection string is for the
ADODB.Connection object, and I store it for later use upon returning
from the function.

However, I notice that in the version of ADODB (6.1) that I'm using,
the connection string does NOT contain the Password component,

Provider=SQLOLEDB.1;User ID=USER1;Data Source=SERVER1;Use Procedure
for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=UNKNOWN;Use Encryption for Data=False;Tag with column collation
when possible=False;

 whereas the old version (2.8) did.

Provider=SQLOLEDB.1;Password=PASSWORD1;User ID=USER1;Data
Source=SERVER1;Current Language=British;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=UNKNOWN;Use Encryption for Data=False;Tag with column collation
when possible=False

This means that I can't store and reuse the connection string for
later ADO operations.

Does anyone know of a fix for this?

Thanks

Edward

Edward wrote:
> Until recently my clients' ancient legacy Access application has
> worked fine.
>
> However, their IT department installed Office 2010 and the legacy app
> is broken (it was last developed in Access 2003).
>
> I've managed to get most of it working, but there's one strange thing
> that bugs me somewhat.
>
> I have some code to create an ADODB.Connection.  Here's the code:
>
> At this point I know what the connection string is for the
> ADODB.Connection object, and I store it for later use upon returning
> from the function.
>
> However, I notice that in the version of ADODB (6.1) that I'm using,
> the connection string does NOT contain the Password component,

Of course it does. it has to. It doesn't have to display it to you.
The connection string is built by you, the developer. It is not dependant on 
ADODB

>
> Provider=SQLOLEDB.1;User ID=USER1;Data Source=SERVER1;Use Procedure
> for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
> ID=UNKNOWN;Use Encryption for Data=False;Tag with column collation
> when possible=False;
>
> whereas the old version (2.8) did.
>
> Provider=SQLOLEDB.1;Password=PASSWORD1;User ID=USER1;Data
> Source=SERVER1;Current Language=British;Use Procedure for
> Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
> ID=UNKNOWN;Use Encryption for Data=False;Tag with column collation
> when possible=False
>
> This means that I can't store and reuse the connection string for
> later ADO operations.
>
I don't think there is a fix required. I think what is happening is that 
Access is hiding the password for security reasons.
Don't you know what the password is? 

On Mar 25, 9:17=A0pm, "Bob Barrows" <reb01...@NOSPAMyahoo.com> wrote:
> Edward wrote:
> > Until recently my clients' ancient legacy Access application has
> > worked fine.
>
> > However, their IT department installed Office 2010 and the legacy app
> > is broken (it was last developed in Access 2003).
>
> > I've managed to get most of it working, but there's one strange thing
> > that bugs me somewhat.
>
> > I have some code to create an ADODB.Connection. =A0Here's the code:
>
> > At this point I know what the connection string is for the
> > ADODB.Connection object, and I store it for later use upon returning
> > from the function.
>
> > However, I notice that in the version of ADODB (6.1) that I'm using,
> > the connection string does NOT contain the Password component,
>
> Of course it does. it has to. It doesn't have to display it to you.
> The connection string is built by you, the developer. It is not dependant=
 on
> ADODB
>
>
>
>
>
>
>
> > Provider=3DSQLOLEDB.1;User ID=3DUSER1;Data Source=3DSERVER1;Use Procedu=
re
> > for Prepare=3D1;Auto Translate=3DTrue;Packet Size=3D4096;Workstation
> > ID=3DUNKNOWN;Use Encryption for Data=3DFalse;Tag with column collation
> > when possible=3DFalse;
>
> > whereas the old version (2.8) did.
>
> > Provider=3DSQLOLEDB.1;Password=3DPASSWORD1;User ID=3DUSER1;Data
> > Source=3DSERVER1;Current Language=3DBritish;Use Procedure for
> > Prepare=3D1;Auto Translate=3DTrue;Packet Size=3D4096;Workstation
> > ID=3DUNKNOWN;Use Encryption for Data=3DFalse;Tag with column collation
> > when possible=3DFalse
>
> > This means that I can't store and reuse the connection string for
> > later ADO operations.
>
> I don't think there is a fix required. I think what is happening is that
> Access is hiding the password for security reasons.
> Don't you know what the password is?- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

Here is how the application used to work.

If the application couldn't make a connection to the SQL Server
database using the stored connection string, it would pop up a SQL
Server Login dialog.  Once the user (in this instance me) had entered
the UserID and Password, the connection string, an attribute of the
rconSQL variable, is stored in a local table (and, for that matter, as
a property of an instantiated class used during the lifetime of the
current application session, not that that makes much odds) and is
used when creating a connection at any later point in the program.

The old way was useful because I could set up the connection once on a
client PC and the full connection string was then stored.  When they
later run the program they don't need to know the UserID and
Password.  Now they can't use the application because Access, in full-
on paranoid mode, has decided to hide the Password, so that the stored
connection string doesn't work.

Can you suggest a setting in Access that would stop Access hiding the
password for security reasons?

Thanks

Edward