[dba-SQLServer] SPs are read-only

Thu Jan 26 13:39:10 CST 2006

The next version of Access contains full ADP functionality using SQL Server 2000. SQL Server 2005 no because thats not the direction they are taking Access from what I am seeing. Think SharePoint in a big way.

Martin

Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast

tel: 02890 974477

________________________________

From: dba-sqlserver-bounces at databaseadvisors.com on behalf of Jim Lawrence
Sent: Thu 26/01/2006 19:17
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SPs are read-only

Hi Doris:

I thought the whole idea was to get away from ADP, which is basically dead.
The only trouble was ADP's passing is that there has to be another way to
connect recordsets to the data objects. Hence the much more complex set of
coding.  

Nice coding!!... too bad about ADP :-(.

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Mike &
Doris Manning
Sent: January 26, 2006 5:36 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SPs are read-only

John,

I'm amazed at the amount of "going around the bush to get to the barn"
information that you are getting from this list.  So far, the responses are
far more complicated than they need to be.

When I was first starting out, I found an ADO Primer website
(http://authors.aspalliance.com/aspxtreme/ado/index.aspx) that really helped
me understand the basics behind what I was trying to do.

There are also several really good books out there for working with SQL
Server within Access.  My favorite is "Professional SQL Server Development
with Access 2000" by Rick Dobson (ISBN 1861004834).

Filling a combo box or listbox is as simple as creating a stored procedure
or view and putting the name of that stored procedure or view in the row
source property of the control.  It gets little bit more complicated when
you need to pass a parameter through but not as hard as you guys make it
out.

Here is the function I use to fill the work order listbox on my ADP's
startup screen.  I have four different stored procedures based on the sort
the user chose.  I pass in the division code and filtering parameters based
on textboxes the users enter data into.

*****************

Public Function GetWOList(intSort As Integer, _
        strDivision As String, strShow As String, _
        strClient As String, strWO As String, _
        strProj As String)

    On Error GoTo ErrorHandler

    Dim strSproc As String

    Select Case intSort
        Case Is = 1 'Work Order Number
            strSproc = "ap_WOS_StartupWONum"
        Case Is = 2 'Project Name
            strSproc = "ap_WOS_StartupWOProj"
        Case Is = 3 'Show Name
            strSproc = "ap_WOS_StartupWOShow"
        Case Is = 4 'Client Name
            strSproc = "ap_WOS_StartupWOClient"
    End Select

    Set cmd = New ADODB.Command
    With cmd
        .CommandType = adCmdStoredProc
        .CommandText = strSproc
        .Parameters.Append .CreateParameter("@Division", adVarChar,
adParamInput, 50)
        .Parameters.Append .CreateParameter("@Status", adBoolean,
adParamInput, 1)
        .Parameters.Append .CreateParameter("@Show", adVarChar,
adParamInput, 50)
        .Parameters.Append .CreateParameter("@Client", adVarChar,
adParamInput, 75)
        .Parameters.Append .CreateParameter("@WO", adVarChar, adParamInput,
5)
        .Parameters.Append .CreateParameter("@Proj", adVarChar,
adParamInput, 50)
        .ActiveConnection = CurrentProject.Connection

        .Parameters("@Division") = strDivision
        If bolArch = True Then
            .Parameters("@Status") = 1
        Else
            .Parameters("@Status") = 0
        End If

        If Len(strShow) > 0 Then .Parameters("@Show") = "%" & strShow & "%"
        If Len(strClient) > 0 Then .Parameters("@Client") = "%" & strClient
& "%"
        If Len(strWO) > 0 Then
            If Len(strWO) = 5 Then
                .Parameters("@WO") = strWO
            Else
                .Parameters("@WO") = strWO & "%"
            End If
        End If
        If Len(strProj) > 0 Then .Parameters("@Proj") = "%" & strProj & "%"

        Set Forms!frmStartup!lstWO.Recordset = .Execute
        .ActiveConnection = Nothing
    End With
    Set cmd = Nothing

    Exit Function

ErrorHandler:
    If Err = 3421 Then
        MsgBox "Work Order Number filter can only be 5 characters!",
vbOKOnly, "Filter Too Long"
    Else
        Call HandleErrors(Err, "basStartup", "GetWOList")
    End If

End Function

Doris Manning
mikedorism at verizon.net

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of
paul.hartland at fsmail.net
Sent: Thursday, January 26, 2006 2:40 AM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] SPs are read-only

John,

As far as I am aware when you pull back data into an appliication using an
SP it is read only, if you want to pull back data and make it editible then
I have found you have to use a view (do as much filtering as possible here
(well I do anyway)) and then use some code with a select statement to bring
it into an editible recordset.
If anyone knows different about SP's then please tell me as well cause OH
how I would love to have an editible store procedure.
Paul

Message Received: Jan 26 2006, 07:28 AM
From: "John Colby"
To: dba-sqlserver at databaseadvisors.com
Cc:
Subject: [dba-SQLServer] SPs are read-only

Is there a magic incantation to make my SP editable? My tables are
read/write but a simple SP that pulls the same data (filtered on a couple of
ID fields) is read-only. Any thoughts on why this is?

TIA,

John W. Colby
www.ColbyConsulting.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com