Windows XP Pro
Access 2002
SQL Server 2000

Hi folks,

I have a couple of ADP issues I was hoping someone could help me 
understand. I'm trying to implement some transaction processing behind a 
form bound to an ADO recordset, so I'm opening an ADO connection object. I 
notice, however, that when I open a recordset using this connection, the 
authentication method I have active effects the type of cursor I get, and 
I don't understand that.

If I use Windows authentication with the following connection string:

 Set cnn = New ADODB.Connection
     cnn.Open _
         ConnectionString:=CurrentProject.Connection

to open the following ADO recordset:

rst.Open _
    Source:="SELECT * FROM Table1", _
    ActiveConnection:=cnn, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockOptimistic

All goes well. I get an updateable recordset and static cursor.

SQL Server authentication, however, won't let me use 
CurrentProject.Connection. The connection object's Open method wants the 
password again. But...

If I use SQL Server authentication with the following connection string:

 Set cnn = New ADODB.Connection
    strCnn = "Provider=sqloledb;" & _
             "Data Source=(my server);" & _
             "Initial Catalog=(my db);" & _
             "User Id=sa;" & _
             "Password=(my password);"
    cnn.Open ConnectionString:=strCnn

to open the SAME recordset:

rst.Open _
    Source:="SELECT * FROM Table1", _
    ActiveConnection:=cnn, _
    CursorType:=adOpenKeyset, _
    LockType:=adLockOptimistic

All doesn't go well. I get a non-updateable recordset and keyset cursor.

Why does the authentication method change the cursor type this way when 
the Provider (SQLOLEDB) is the same, and I have administrator priviledges 
on both my Windows and SQL Server log-ons? Can I get an updateable 
recordset using SQL Server authentication? 

Thanks for any thoughts.

Bob

Hi Bob

The project I work on is used on Win95/Win98 machines, so uses SQL Server 
authentication throughout. I've had no problem using 
CurrentProject.Connection or getting read/write recordsets.

Comparing connection strings the only difference is that I've included the 
following line between the Provider and Data Source:

";Persist Security Info=True" & _

I believe this corresponds to the 'Allow saving password' tickbox in the 
Connection dialog.

Let me know if it makes a difference.

Brian

> Hi folks,

> I have a couple of ADP issues I was hoping someone could help me 
u> nderstand. I'm trying to implement some transaction processing behind a 
f> orm bound to an ADO recordset, so I'm opening an ADO connection object. 
I 
n> otice, however, that when I open a recordset using this connection, the 
a> uthentication method I have active effects the type of cursor I get, 
and 
I>  don't understand that.

> If I use Windows authentication with the following connection string:

>  Set cnn = New ADODB.Connection
 >     cnn.Open _
 >         ConnectionString:=CurrentProject.Connection

> to open the following ADO recordset:

> rst.Open _
 >    Source:="SELECT * FROM Table1", _
 >    ActiveConnection:=cnn, _
 >    CursorType:=adOpenKeyset, _
 >    LockType:=adLockOptimistic

> All goes well. I get an updateable recordset and static cursor.

> SQL Server authentication, however, won't let me use 
C> urrentProject.Connection. The connection object's Open method wants the 
p> assword again. But...

> If I use SQL Server authentication with the following connection string:

>  Set cnn = New ADODB.Connection
 >    strCnn = "Provider=sqloledb;" & _
 >             "Data Source=(my server);" & _
 >             "Initial Catalog=(my db);" & _
 >             "User Id=sa;" & _
 >             "Password=(my password);"
 >    cnn.Open ConnectionString:=strCnn

> to open the SAME recordset:

> rst.Open _
 >    Source:="SELECT * FROM Table1", _
 >    ActiveConnection:=cnn, _
 >    CursorType:=adOpenKeyset, _
 >    LockType:=adLockOptimistic

> All doesn't go well. I get a non-updateable recordset and keyset cursor.

> Why does the authentication method change the cursor type this way when 
t> he Provider (SQLOLEDB) is the same, and I have administrator 
priviledges 
o> n both my Windows and SQL Server log-ons? Can I get an updateable 
r> ecordset using SQL Server authentication? 

> Thanks for any thoughts.

> Bob

Hi Brian,

Thanks for the thought. I'm going to have to play around with this one for a 
bit when I get some time. Still read-only. Here's the code, just in case 
anything jumps out at you.

Private Sub Form_Open(Cancel As Integer)

    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCnn As String

    ' Open the connection object using SQL Server Authentication

    Set cnn = New ADODB.Connection
    strCnn = "Provider=sqloledb;" & _
                 "Data Source=(my server);" & _
                 "Initial Catalog=(my database);" & _
                 "User Id=sa;" & _
                 "Password=(my password);"
    cnn.Open ConnectionString:=strCnn

    ' Open a recordset
    Set rst = New ADODB.Recordset
    rst.Open _
      Source:="SELECT * from Products", _
      ActiveConnection:=cnn, _
      CursorType:=adOpenKeyset, _
      LockType:=adLockOptimistic

    ' Set the recordset to the form
    Set Me.Recordset = rst
End Sub


>From: "Brian Skelton" <braxis@b...>
>Reply-To: "Access" <access@p...>
>To: "Access" <access@p...>
>Subject: [access] Re: ADP authentication/cursors
>Date: Sat, 25 Jan 2003 12:43:10
>
>Hi Bob
>
>The project I work on is used on Win95/Win98 machines, so uses SQL Server
>authentication throughout. I've had no problem using
>CurrentProject.Connection or getting read/write recordsets.
>
>Comparing connection strings the only difference is that I've included the
>following line between the Provider and Data Source:
>
>";Persist Security Info=True" & _
>
>I believe this corresponds to the 'Allow saving password' tickbox in the
>Connection dialog.
>
>Let me know if it makes a difference.
>
>Brian
>
> > Hi folks,
>
> > I have a couple of ADP issues I was hoping someone could help me
>u> nderstand. I'm trying to implement some transaction processing behind a
>f> orm bound to an ADO recordset, so I'm opening an ADO connection object.
>I
>n> otice, however, that when I open a recordset using this connection, the
>a> uthentication method I have active effects the type of cursor I get,
>and
>I>  don't understand that.
>
> > If I use Windows authentication with the following connection string:
>
> >  Set cnn = New ADODB.Connection
>  >     cnn.Open _
>  >         ConnectionString:=CurrentProject.Connection
>
> > to open the following ADO recordset:
>
> > rst.Open _
>  >    Source:="SELECT * FROM Table1", _
>  >    ActiveConnection:=cnn, _
>  >    CursorType:=adOpenKeyset, _
>  >    LockType:=adLockOptimistic
>
> > All goes well. I get an updateable recordset and static cursor.
>
> > SQL Server authentication, however, won't let me use
>C> urrentProject.Connection. The connection object's Open method wants the
>p> assword again. But...
>
> > If I use SQL Server authentication with the following connection string:
>
> >  Set cnn = New ADODB.Connection
>  >    strCnn = "Provider=sqloledb;" & _
>  >             "Data Source=(my server);" & _
>  >             "Initial Catalog=(my db);" & _
>  >             "User Id=sa;" & _
>  >             "Password=(my password);"
>  >    cnn.Open ConnectionString:=strCnn
>
> > to open the SAME recordset:
>
> > rst.Open _
>  >    Source:="SELECT * FROM Table1", _
>  >    ActiveConnection:=cnn, _
>  >    CursorType:=adOpenKeyset, _
>  >    LockType:=adLockOptimistic
>
> > All doesn't go well. I get a non-updateable recordset and keyset cursor.
>
> > Why does the authentication method change the cursor type this way when
>t> he Provider (SQLOLEDB) is the same, and I have administrator
>priviledges
>o> n both my Windows and SQL Server log-ons? Can I get an updateable
>r> ecordset using SQL Server authentication?
>
> > Thanks for any thoughts.
>
> > Bob


_________________________________________________________________
The new MSN 8: smart spam protection and 2 months FREE*  
http://join.msn.com/?page=features/junkmail