[AccessD] Need ADO ADP help

Hi all,
 
I hope someone on this list will be able to help me get on my way again..
I want to make use of an application role for security reasons and not
having to grant permissions for every user on SQL Server 2000.
 
My environment:
Access 2003
SQL Server 2000
MDAC 2.8
JET 4 latest version/update
 
Busy playing with ADO and unbound forms/datasheets still being updatable...
I was doing pretty well using an Access MDB until I found out that I would
not be able to use ADO as a recordset source for reports unless I'd make use
of an ADP instead. So ohwell let's build an ADP, imported all the objects
from the MDB and before even trying to get the reports to work I stumbled
into another problem... the code used in my MDB enabling updatable recordset
etc... worked but in the ADP the sets were no longer updatable?!
I have been playing around with a connected ADP and a disconnected ADP,
making use of the project.connection property etc. however it seems that I
can't figure out what conditions to meet when binding a form/datasheet to an
ADO recordset and still be updatable.
 
The code I have been using in my MDB:
 
(References:
- ADO 2.8
- DAO 3.6)
 
Connecting and authorising
Set oConn = New ADODB.Connection
oConn.Open "Provider=SQLOLEDB;Data Source=PC1;Initial
Catalog=EBNinfo;Integrated Security=SSPI"
oConn.Execute "EXEC sp_setapprole 'AppRoleName', {Encrypt N 'mypassword'},
'ODBC' "

Binding the form
    Dim rs As New ADODB.Recordset
    With rs
        Set .ActiveConnection = oConn
        .Source = "SELECT * FROM stamCluster"
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .CursorLocation = adUseClient
        .Open
    End With
    Set Me.Recordset = rs
    rs.Close

So, this worked fine in my MDB but the ADP refuses to have my dataset
updatable.... sigh
 
I hope someone ran into this behaviour before or at least knows either a
work-around or another syntax which would do the trick. I know there are
other way than using an application role but as for know I prefer this
solution... Once I am able to get my forms/reports/comboboxes to work using
ADO recordsets all's well:-)
 
Ofcourse, if someone knows a work-around for the report ADO 'problem' in my
MDB that would also be just fine though I'd rather do not want to use ODBC
DNS.
 
 
Regards and TIA,
 
Eric Starkenburg
Starkenburg Office Solutions


  _____  


avast! Antivirus <http://www.avast.com> : Uitgaande bericht is niet besmet. 


Virus Gegevensbestand (VPS): 0428-1, 09-07-2004
Getest op: 11-7-2004 18:14:03
avast! auteursrecht (c) 2000-2004 ALWIL Software.