ADODC & Access User-Level Security...? 
Author Message
 ADODC & Access User-Level Security...?

Hi VB Users

(I am using VB 6 and Access 2000.)

I have developed an application using forms containing ADO
data controls and text boxes bound to data fields. I did
this basically as I was being slack and wanted to develop
the application quickly and with as limited amount of code
as possible.

Now I am trying to implement security. I have created user
groups and passwords using Access 2000 User Level Security
Wizard, and these are working when I open Access itself
and logon. However, I am having problems opening the data
in the forms as I can't seem to get the data control to
recognise the username and password before trying to

My question is - can this be done with a data control and
data bound controls at all? I have read a few other
notices that refer to username and password problems, but
these seem to not use data controls. I am very keen to
keep my data controls, otherwise I will need to recode a
significant number of forms to use a direct access method.

If anyone could provide any pointers I would be highly

Thanks in advance

Fri, 03 Sep 2004 07:00:47 GMT  
 ADODC & Access User-Level Security...?
Hi Nikki,

I'm having similar problems myself. So far I've established that if you
specify an ODBC Datasource (DSN=), then you need to specify the
Workgroup Information File (*.mdw) as the "System Database". The
username and password can then be specified in the usual way as part of
the DSN.

My problem has been specifying the Username and Password for the DSN at
runtime to be used by the ADODC controls. I have a multi-user system
with access control specified at the database (where it belongs), but to
do that I need to login to the database according to the logged in user

*** Sent via Developersdex ***
Don't just participate in USENET...get rewarded for it!

Fri, 01 Oct 2004 19:00:06 GMT  
 ADODC & Access User-Level Security...?
Hi Derek

I have mostly worked my way through this problem now and
will let you know how I've gotten around it.  Like you, I
discovered that I would need to specify the System
Database under the ODBC Datasource to link the security to
the database.  Then of course you have the problem of all
your data controls trying to connect without the username
and password (and like me, I'm sure you don't want to
delete them all and code everything!).  So this is the way
I worked around it (btw this does take some time as I
still had to add code to each form in my project, but not
*as much* code as it would have taken to redo the entire
thing without the data controls):

Firstly on load of the project I prompt the user for a
username and password which I then store in global
variables.  I use them to connect to the datasource itself
(which I do only once, but you could do it multiple times
depending on whether you want to open and close it all the

In my module:
Public cn As New ADODB.Connection
    frmLogon.Show vbModal

    cn.Open "DSN=OLCDBAPP;" & _
       "Uid=" & stUserName & ";" & _
       "Pwd=" & stPassword & ";"


    If Err.Number = -2147217843 Then
        MsgBox "Invalid Logon.", vbCritical, "OLCDBAPP"
    End If

Then for each form which has a data control and bound
controls I have had to do the following:
1) Remove the RecordSource property of the ADO Data Control
2) Delete the DataSource and DataField properties of all
the bound controls
3) If there are Data Combo boxes or List boxes, delete the
RowSource and ListField properties of these
4) In the Form_Load event connect to the database using an
ADODB.Recordset object and then set the datacontrol to be
that object:

    Dim rs As New ADODB.Recordset
    Dim stSelect As String

    stSelect = "SELECT * FROM AWARDS"

    With rs
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        .CursorLocation = adUseClient
        .Open stSelect, cn, , , adCmdText
    End With

    Set Adodc1.Recordset = rs

    Call Bind_Controls

5) Have a "Bind_Controls" procedure (as is called above)
or similar that binds each control to the database (ie,
set DataSource, DataField, RowSource and ListField
properties as per what was deleted in steps 2 & 3):

Private Sub Bind_Controls()

    Set txtAwardID.DataSource = Adodc1
    txtAwardID.DataField = "AWARD_ID"

    Set txtDescr.DataSource = Adodc1
    txtDescr.DataField = "DESCR"

End Sub

And that works!  Hope this helps you out - it took some
time to get it right.  The only problem I'm having now is
with some forms with DataGrids on them, but I'm slowly
resolving this too, and it shouldn't affect you if you
don't have any grids.

If you need any further explanation of this, let me know.


Sat, 02 Oct 2004 08:03:30 GMT  
 [ 3 post ] 

 Relevant Pages 

1. User-level security & OpenDataSource name argument

2. Access User Level Security HELP!!

3. VB6, ADO, and writing to an Access 2000 db with user level security

4. Opening an Access file with the User Level security feature

5. Access - User-Level Security?

6. OpenDataSource name argument and user-level security

7. Security - User Level

8. User-Level Security problem

9. VB5, Crystal 4.6.1 -User Level Security password

10. Crystal and User Level Security passwords

11. Stucked on client's machine in user level security

12. User Level security Novice


Powered by phpBB® Forum Software