Access form that calls a stored procedure from SQL Server using this VBA

• DaveBriCam

  SSCarpal Tunnel

  Points: 4294

  More actions

  March 20, 2019 at 1:35 pm

  #383170

  I have an Access form that calls a stored procedure from SQL Server using this VBA:

  Private Sub Form_Load()
  Dim cmd As ADODB.Command
  Set cmd = New ADODB.Command
  cmd.ActiveConnection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=GCDF_DB;Data Source=BADLANDS"
  cmd.CommandType = adCmdStoredProc
  cmd.CommandText = "UpdateStatus"
  cmd.Execute
  End Sub

  And this is my code for SQL Server:

  UPDATE p
SET p.certstatusid = c.statusid
FROM gcdf_DB..people p
INNER JOIN
gcdf_DB..certs c
ON
p.peopleID = c.peopleID

  This returns 16250 as a count of the number of records updated. What I need to do is to have a way on the Access side to see if the SP is working... any ideas?

• pietlinden

  SSC Guru

  Points: 63421

  More actions

  March 20, 2019 at 3:41 pm

  #2025073

  Looks like this is the answer:
  https://stackoverflow.com/questions/12676747/how-to-get-the-affected-rows-in-vba-ado-execute