Database connection

I'm using this code to open database using ADO.
-------------->><<<<-----------------------------
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";"

Set rs = New Recordset
rs.Open "SELECT * FROM Client", db, adOpenStatic, adLockOptimistic

rs.MoveFirst
rs.MoveLast
MsgBox rs.RecordCount
-------------------->>>>>>>>>>>><<<<<<<<------------------
the above works fine (when the DB is without a password). However, the problem that I'm encountering is that I need the database password protected. when add user and password to the connection string such as:
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";", "admin", "client"

gives me this error: "Cannot Start your application. the workgroup information file is missing or opened exclusively by another user.

Anybody knows what is wrong with my code.

I'd appreciate your inputs.
Thanks a million
Roger

"Great Ideas need Landing Gears as well as Wings"

Instead of using the username and password parts of the connection string try setting your .mdw, username, and password with properties before you do your connection object's Open like this:
<PRE> conYourConnectionObject.Properties("Jet OLEDB:System database") = "c:\pathto\your.mdw"
conYourConnectionObject.Properties("User ID") = "YourUsername"
conYourConnectionObject.Properties("Password") = "YourPassword"
conYourConnectionObject.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";"</PRE>Paul

Paul,
I tried the below code and I got this error: "Item cannot be found in the collection corresponding to the requested name or ordinal."

Can you tell me what I'm doing wrong. I would really appreciate it.

Roger

--------><----------------
Set db = New adodb.Connection
db.CursorLocation = adUseClient
db.Properties("Jet OLEDB:System Database") = "C:\windows\system\system.mdw"
db.Properties("User ID") = "Admin"
db.Properties("Password") = "client"
db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";"



"Great Ideas need Landing Gears as well as Wings"

Ok...the syntax I posted for you isn't exactly what I've used successfully in the past. I didn't think it made a difference but I just tested it and it does. Apparently you need to use this instead:
<PRE> conYourConnectionObject.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51"
conYourConnectionObject.Properties("Jet OLEDB:System database") = "c:\pathto\your.mdw"
conYourConnectionObject.Properties("User ID") = "YourUsername"
conYourConnectionObject.Properties("Password") = "YourPassword"
conYourConnectionObject.Open "Data Source=c:\pathto\your.mdb"
</PRE>I guess that letting ADO know what your 'provider' is affects the Properties collection. Normally when I use ADO I combine the 'provider' with the 'data source' in the Open method...but I guess you can't do this when you're dealing with Access security!

Sorry for the screwup!

Good luck,
Paul

Paul,
Well, your code looks right. But for some reason, it still doesn't work. I suspect that the reason isn't the code.
Let me ask you a question that I think it might be the cause of these problems. Please bear with me, I'm new at the secured database realm.
How do I create a secured Access database? I have access 2000. I think that the problem is here not in the code.

Again, you've been a great help. I appreciate it.
Thanks
Roger

"Great Ideas need Landing Gears as well as Wings"

I'm afraid that I haven't touched Access 2000 and I have no idea if it does security the same way that Access97 does it. And...doing security in Accss97 is semi-painful. I could give a rough explanation but I'd rather not - you'd be much better off reading about it. If you happen to have an Office97 CDROM laying around you can look at 'Building Applications' in the \VALUPACK\ACCESS\BLDAPPS\ folder. Start with default.htm. There's a chapter on Access97 security. I have no idea if the Office 2000 CDROM includes similar documentation. I haven't dealt with Windows 2000 either. I'd imagine that if you're running Access 2000 on Win 2000 it might use an entirely different security scheme.

You might try making another post about doing security with Access 2000 to get some more replies from more experienced folks.

Good luck,
Paul

Paul,
I just wanted to let you know that I was doing everything right but the only thing that i overlooked was the OLEDB version in the connection string. it should 4.0. I tried it and it works fine for now.
I thought you'd like to know.

Again, I appreciate your help.
You and Guys like you make the world a better place.

Thanks
Roger

"Great Ideas need Landing Gears as well as Wings"

Cool, I'm glad it worked out for ya.

Paul