SQL Permission Denied 1

[MattSTech]

Good Day.

I have limited experience with SQL and quite a bit of experience with Access. I have been connecting to Access with ADO for some time now.
I recently ran into trouble when connected to a SQL database and attempting a write. What works against an access database isn't working here. I suspect it is a server side permissions problem, but my IT guy has been trying everything up to and including giving me owner rights and admin access.
Here is the connection string:

    strMainConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist" & _
                    "Security Info=False;User Id=Matprogram;Password=Mpg8743;Data Source=CP03"

then the code

    Set objRS = New ADODB.Recordset
    Set objCon = New ADODB.Connection
    
    objCon.Open strMainConn
       strSQL = "SELECT * FROM prempl WHERE fendate = 1/1/1900 ORDER BY fname"
        objRS.Open strSQL, objCon, adOpenKeyset, adLockOptimistic
            Do While Not objRS.EOF
                'RIGHT HERE IS MY PROBLEM
                objRS("fname").Value = UCase(Trim(objRS.Fields("fname").Value))
                objRS.Update
                'IF I COMMENT THE ABOVE CODE EVERYTHING WORKS FINE TO READ THE DATABASE
                frmM2M.cboEmployee.AddItem Trim(objRS.Fields("fname").Value) & ", " & Trim(objRS.Fields("ffname").Value) & " " & Trim(objRS.Fields("fmi").Value)
                frmM2M.lstEmployee.AddItem objRS.Fields("fempno").Value

                objRS.MoveNext
            Loop
    objRS.Close
    Set objRS = Nothing
    objCon.Close
    Set objCon = Nothing

As mentioned in the notes above I can connect to the DB just fine. It only errors out when I try to update the recordset. The error is: RTE -2147217911 UPDATE permission denied on object 'prempl'
Am I simply missing something silly or ?

 

[Andrzejek]

I am just guessing here, but you may have ReadOnly recordset here.

If you want to Update, why not use Update statement:

[green]
'RIGHT HERE IS MY PROBLEM
'objRS("fname").Value = UCase(Trim(objRS.Fields("fname").Value))
'objRS.Update[/green]

strSQL = "Update prempl SET fname = '" & UCase(Trim(objRS.Fields("fname").Value)) & "'" _
" WHERE ....."

objCon.Execute strSQL

Have fun.

---- Andy

 

[gmmastros]

strMainConn = "Provider=SQLOLEDB.1;[!]Integrated Security=SSPI[/!];Persist" & _
                    "Security Info=False;[!]User Id=Matprogram;Password=XXXXX[/!];Data Source=CP03"

Integrated security = SSPI means.... use windows authentication. Therefore, there is no need to supply a user name and password either.

In this case, I don't know which authentication method is used.

my IT guy has been trying everything up to and including giving me owner rights and admin access.

Did he give that to you based on your SQL login (Matprogram) or based on your Windows login?

If you're not sure what login you are using, you can run this:

Select suser_sname() As MyLoginName

This will return the user that is logged in to the database. If it's SQL Authentication, it should return Matprogam. If you are using Windows Authentication, it should return something like: (YourDomainName\YourLoginName)



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MattSTech]

Where do I pull the return information on the Select statement?

 

[gmmastros]

Just for troubleshooting this issue:

    Set objRS = New ADODB.Recordset
    Set objCon = New ADODB.Connection
    
    objCon.Open strMainConn
       strSQL = "Select suser_sname() As MyLoginName"
        objRS.Open strSQL, objCon, adOpenKeyset, adLockOptimistic
            msgbox RS("MyLoginName")
    objRS.Close
    Set objRS = Nothing
    objCon.Close
    Set objCon = Nothing

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MattSTech]

Hazahh!

That was it. Removing the line:

Integrated Security=SSPI

Fixed the issue. It was defaulting to the windows auth.

Thank you soooo much.

Matt