Bind ADO RS to continuous form

[jadams0173]

Using SQL server 2000 and Access 2000.

I've read

http://support.microsoft.com/kb/281998

and am still unable to update my form. Can anyone offer an suggestions.

Function OpenConnection()
Dim constr As String


'Dim conn As ADODB.Connection
On Error GoTo ERROR_HANDLER_CON
'Set conn = New ADODB.Connection

If conn Is Nothing Then
    Set conn = New ADODB.Connection
End If
'*******************
With conn
.Provider = "MSDataShape"
         
      'The code below uses a sample server name, user ID, and password.
      'Be sure to use your actual server name, user ID, and password.
      .ConnectionString = "DATA PROVIDER=SQLOLEDB;DATA " _
         & "SOURCE=STAT1;Initial Catalog=SCHED;Trusted_Connection=Yes"
      .CursorLocation = adUseServer
      .Open
   End With
Exit Function

And in my form load. I have also tried form open.

Dim rsTemp As ADODB.Recordset

Set rsTemp = New ADODB.Recordset

With New ADODB.Command
    .ActiveConnection = conn
    .CommandText = "USP_TempRS"
    .CommandType = adCmdStoredProc
    

    .Parameters.Append .CreateParameter("@USERNAME", adChar, , 8, Environ("USERNAME"))
    Set rsTemp = .Execute

End With

Set Me.Recordset = rsTemp
 
'conn.Close
Forms!FRMMAIN_SQL.Visible = True
DoCmd.Maximize
End Sub

 

[Remou]

You do not mention a cursor type. It is best to specify a cursor type:

http://msdn.microsoft.com/en-us/library/aa140098(office.10).aspx

http://lessthandot.com

 

[jadams0173]

Hey Remou,

Thanks for the reply. I tried adding the cursor type and still can not update my form. Do you have anymore suggestions please.

Dim rsTemp As ADODB.Recordset

Set rsTemp = New ADODB.Recordset

With New ADODB.Command
    .ActiveConnection = conn
    .CommandText = "USP_TempRS"
    .CommandType = adCmdStoredProc


    .Parameters.Append .CreateParameter("@USERNAME", adChar, , 8, Environ("USERNAME"))
    [red]rsTemp.CursorType = adOpenKeyset[/red]
    Set rsTemp = .Execute()

End With

 

[jadams0173]

Seems no matter what I do I get back a static cursor as shown in the immediate window.

?rstemp.CursorType 
 3

 

[jadams0173]

I've found that if I don't use a stored procedure I can get an updateable RS returned.

rsTemp.Open "Select * from Scheduler_form", conn, adOpenKeyset, adLockOptimistic, adCmdText

Is there anyway to do it with the stored procedure?

 

[Remou]

Not that I know of. However, you can build your own sql string that only includes the user records you want.

http://lessthandot.com

 

[PHV]

Perhaps some info here ?

http://support.microsoft.com/kb/148581

In fact, seems that your SP isn't updatable ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

PHV
When testing, I found that using a stored procedure to create the recordset gives:

Error 7965 "The object you entered is not a valid recordset property"

When trying to bind the recordset (Set Me.Recordset = rs)

http://lessthandot.com

 

[PHV]

Have you read this ?

http://support.microsoft.com/kb/281998

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

The thing is, that link refers to binding a recordset based on sql text, not a recordset based on a command object, which would typically be used with a stored parameter query.

http://lessthandot.com

 

[PHV]

Oh, I see.
Sorry I can't test anything as I don't use MS-SQL at all.

 

[jadams0173]

When testing, I found that using a stored procedure to create the recordset gives:

Error 7965 "The object you entered is not a valid recordset property"

When trying to bind the recordset (Set Me.Recordset = rs)

I got the same error until I changed the connection string to my SQL DB to use a Provider of MSDATASHAPE as I show in my first post. I found that reference here

http://support.microsoft.com/kb/227053/EN-US/

I have the form bound to the recordset now and I'll post the code when I get back to work tomorrow as I have encounterd another challenge.

 

[danvlas]

What does the stored proc look like?
Stored procs can return data sets, parameters or nothing.
If you post it, we may find out what's happening.



Daniel Vlas
Systems Consultant