Trying again: Password needed for ADO connection

[David Portwood]

Trying to create a connection object to open various recordsets using ADO
methods. My database is secured and the cnn.open method wants the user's
password. I hardcoded my own password as a param and it works for me but I
have multiple users so hardcoding is not an option.

I'm talking about the password to enter a secured database. Each user has
his own password which is set by the user himself from the Access menu:
Tools\Security\User And Group Accounts\Change Logon Password tab. Apparently
when I create an ADO connection object it wants this password. Or am I doing
something wrong?

Does anybody know how to handle this situation?

 

[Albert D. Kallal]

Are talking about a adp project, or a mdb?

Are you using sql server?

You don't mention how you creating your connection object, but you have to
either specify he workgroup file used when you create your connection
object, or simply use the built in one...

If you talking about sql server, well, then the security workgroup stuff is
not used.....

(unless the font end is using security, but that security will NOT apply to
sql server.

The simple solution is to use the connection object...

Dim rs As ADODB.Recordset

Set rs = CurrentProject.Connection.Execute("select * from contacts")

Do While rs.EOF = False
Debug.Print rs!FirstName
rs.MoveNext
Loop
rs.Close
Set rs = Nothing


Are you using the built in connection object? Are you opening a table that
is linked, or are you actually using a qualified full path name in your
connection object (which means you also have to include a the full qualified
path name to the workgroup file also).

If you use currentProject.Connection, then it will use your current
workgroup and you should not be seeing a password prompt.

 

[David Portwood]

This is for an .mdb file. I'm changing my DAO recordset calls to use ADO
instead.

Following an example in the textbook I'm using, I was attempting to create a
persistent connection object (call it cnn) which I was going to keep open
and then re-use as I open, close, and reopen my various recordsets (which
all point to tables/queries in the same database). Instead of

rst.ActiveConnection = CurrentProject.Connection

(pardon me if I've forgotten the syntax)

I would be able to code

rst.ActiveConnection = cnn

re-using my cnn connection object each time. However, when I first create
that connection object, the system asks for my password. I can hardcode my
own password, but that only lets me in - not other users, who will also be
using my forms.

Today I went back and used rst.ActiveConnection = CurrentProject.Connection.
That syntax does not ask for a password but I think I'm rebuilding the same
connection each time I open a recordset and that seems inefficient.

I don't have the actual code in front of me. I was expecting the strategy
would be well known. As I said, it's taken from an example in my textbook
(written by Alison Balter) showing how the same connection object can be
applied for opening different recordsets.

 

[Albert D. Kallal]

I would be able to code

rst.ActiveConnection = cnn

Sure, by my example should not make a difference.

you can go


rst.ActiveConneciton = currentproject.Conneciton.

or

Dim mycon As ADODB.Connection

Set mycon = CurrentProject.Connection

So, how are you creating your connection object?

Also, you seem to be talking about a password, but what about a prompt for
user name?

It is possible that you have a linked database, but a password IN ADDITION
to work group security? I suggest you remove the password on the database if
you going to use workgroup security (it makes zero sense to have both).


Simply using, or creating the above connection via:

Dim mycon As ADODB.Connection

Set mycon = CurrentProject.Connection


The above code should not be trigging a password prompt (unless you meant to
say username + logon).

re-using my cnn connection object each time. However, when I first create
that connection object, the system asks for my password. I can hardcode my
own password, but that only lets me in - not other users, who will also be
using my forms.

as mentioned, don't confuse a database password with that of a user name
logon +password.

Today I went back and used rst.ActiveConnection =
CurrentProject.Connection. That syntax does not ask for a password but I
think I'm rebuilding the same connection each time I open a recordset and
that seems inefficient.

Well, it actually not that inefficient.

but, you can make a copy, and it only instances once, so, just go:

Dim mycon As ADODB.Connection

Set mycon = CurrentProject.Connection

if you make "mycon" global..then you can re-use it. For the most part, the
built connection object will be just as fast, but you can use the above, and
you have your ready made conneciton object...

 

[David Portwood]

Well, it actually not that inefficient.

but, you can make a copy, and it only instances once, so, just go:

Dim mycon As ADODB.Connection

Set mycon = CurrentProject.Connection

if you make "mycon" global..then you can re-use it. For the most part, the
built connection object will be just as fast, but you can use the above,
and you have your ready made conneciton object...

This is exactly what I'm trying to do, but it asks me for UserName +
Password when I first create the connection. I'm being asked twice: once
when I launch the app, and then again when I create the connection.

I'll get the code at work today.

 

[Albert D. Kallal]

Great....

I suspect the problem is that your not using the built in conneciton
object....

If you make a copy of the built in conenciton, you should not be
prompted.....


By the way, there is little, if any benefit to using ado in ms-acces over
that of dao. ADO is much on its way out, and is being replaced by
ado.net.....

 

[David Portwood]

I read somewhere that DAO has problems granting record level locking. I want
record level locking, so I thought I better switch over to ADO.

 

[Albert D. Kallal]

I read somewhere that DAO has problems granting record level locking. I
want record level locking, so I thought I better switch over to ADO.

I never heard that....

There is no difference, and performance of dao is better then ADO.....

 

[Tom Wickerqueer]

bull crap dude

performance is dao is not better than ADO

where do you come up with this crap?!?!?

 

[Jamie Collins]

I never heard that....

http://support.microsoft.com/kb/306435/
PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60

Jamie.

--