Connecting to Access via ADO

[Jeff Carlin]

I have an app that uses an Access database thru DAO. I want to upgrade it to ADO. I use both a Username and Password for each user. This is how far I've gotten:

Dim DSN As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim strPath$, sql$

With DSN
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .Properties("Jet OLEDB:System database") = Trim(PROJ_System_MDW)
    .Properties("Data Source") = Trim(PROJ_DB_PATH) & PROJ_DB_Name
    .Properties("Jet OLEDB:Database Password") = g_sPassword
'    .Properties("Jet OLEDB:Database Username") = g_sUserName
    .Open
End With

Notice the rem'd line. I cannot figure how to pass the username (this is not working). Can anyone help? If I remove the Password line as well, the database opens fine, but I've lost the security aspects. BTW, the "PROJ_" vars are pre-set.

[Corby]

Opening Access Database Using ADO
Dim DSN As New ADODB.Connection
Dim Rs As New ADODB.Recordset
Dim strPath$
Dim Uid$, PW$

'Access Version
UId = "sa"
PW = "Password"
strPath = "d:\temp\test.mdb"
With DSN
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open strPath, Uid, PW
End With
SQL = "Select * From Table_Name"
Rs.Open SQL, DSN, adOpenDynamic, adLockOptimistic

You can do anything you want with this. I usually
make DSN Global to the whole project, you can then
use it anywhere.

[Jeff Carlin]

Thanks, Corby. Works great.