Updating A SQL View From Access VBA Script

[Mtoney76]

Howdy, I've been wondering how a SQL view can be updated via an update passthrough quesry ran from a VBA Script in Access. I've found this code online:
Public Sub UpdateSqlServer()
Dim cdb As DAO.Database, rst As DAO.Recordset
Dim con As Object ' ADODB.Connection
Dim cmd As Object ' ADODB.Command
Const adParamInput = 1
Const adInteger = 3
Const adVarWChar = 202

Set cdb = CurrentDb
Set rst = cdb.OpenRecordset( _
"SELECT " & _
"[dbo_vw_RPT_ServersAllActive_US].Server_Name," & _
"[dbo_vw_RPT_ServersAllActive_US].CPU," & _
"[dbo_vw_RPT_ServersAllActive_US].Memory," & _
"[dbo_vw_RPT_ServersAllActive_US].Status," & _
"[dbo_vw_RPT_ServersAllActive_US].Site," & _
"[dbo_vw_RPT_ServersAllActive_US].VM," & _
"[dbo_vw_RPT_ServersAllActive_US].CI_Number," & _
"[dbo_vw_RPT_ServersAllActive_US].OS," & _
"[dbo_vw_RPT_ServersAllActive_US].OS_EOL," & _
"[dbo_vw_RPT_ServersAllActive_US].Business_Unit," & _
"[dbo_vw_RPT_ServersAllActive_US].Contacts " & _
"FROM " & _
"[qryTestest query] " & _
"INNER JOIN " & _
"[dbo_vw_RPT_ServersAllActive_US] " & _
"ON [qryTestest].Server_Name = [dbo_vw_RPT_ServersAllActive_US].Server_Name", _
dbOpenSnapshot)

Set con = CreateObject("ADODB.Connection")
con.Open "DSN=Source1;"
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = _
"UPDATE qryTestest SET " & _
"[Business Unit]=? " & _
"WHERE [Contacts]=?"
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255) ' Business Unit
cmd.Parameters.Append cmd.CreateParameter("?", adVarWChar, adParamInput, 255) ' Contacts
cmd.Prepared = True

Do Until rst.EOF
cmd.Parameters(0).Value = rst![Business Unit]
cmd.Parameters(1).Value = rst![Contacts]
cmd.Execute
rst.MoveNext
Loop
Set cmd = Nothing
con.Close
Set con = Nothing
rst.Close
Set rst = Nothing
Set cdb = Nothing
End SUb
I'm worried that since the information being updated through this query is from a View, it will be seen as read only and will not update. Can Views be updated through pass-through queries? f not, how can i find the data sources of this view. Thank you all for y'all help beforehand, y'all have significantly improved my ability in this and have made my life better

 

[AnthonyGerrard]

Well does it work?

I think a view can be updated as long as its updating just one of its base tables. THere could obviously be other underlying issues why data may not update.

 

[SQL_Hell]

Do you have access to the SQL server?

If not ask whoever does have access to send you the view definition.

As AnthonyGerrard righly points out views can only be updated if they have one table.

Also, what is [qryTestest query]? it seems that in your code you are trying update "[qryTestest query] and not the view anyway? So I don't really understand your question.