BUG: Distributed SQL Update, Jet Provider may not Return Errors (201905)

MORE INFORMATION

Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four part names including a linked-server name.

You can perform a SQL Server distributed update through the Jet OLEDB provider. For example:

UPDATE Access...shippers
SET CompanyName='Speedy Express'
WHERE CompanyName='Speedy Expres'

				

'Access' is a linked server that points to the Microsoft Access sample NWind.mdb file. However, the Jet provider may not return details on the cause of the error when an Update query fails.

Steps to Reproduce Behavior

Set-up a linked server and run the following query in the SQL Query Analyzer:

EXEC sp_addlinkedserver

   'Access',
   '',
   'Microsoft.Jet.OLEDB.4.0',
   'e:\VS98\VB98\nwind.mdb',
   NULL,
   NULL

go
sp_addlinkedsrvlogin 'Access', 'FALSE', NULL, 'Admin', Null
go
update Access...orders set shipvia=10 where shipvia=1

				

The Update statement fails with the following error: If you run the same update statement directly through the Jet OLEDB provider in an ActiveX Data Objects ADO application, it will give the precise error message regarding why the update failed:
Following is a Visual Basic snippet of code that will produce error details:

  Dim cn As ADODB.Connection, rs As ADODB.Recordset, I As Long
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset
  cn.Provider = "Microsoft.Jet.OLEDB.4.0"
  cn.Open "e:\vs98\vb98\nwind.mdb"

  ' The following gives an error "You cannot add or change a record
  ' because a related record is required in shippers table:

   cn.Execute "Update orders set shipvia=10 where shipvia=1"