Updatable Join Recordset Using ADO and SQL Server

Updatable Join Recordset Using ADO and SQL Server

Contrary to popular thought, you can add new records to an ADO 2.1 Recordset object that is the result of a Join operation executed on multiple-base tables. You must specify the UniqueTable, or the name of the base table upon which updates, insertions, and deletions are allowed. This example uses the SQLOLEDB.1 provider and a disconnected ADO recordset as a bonus:

 Private Sub Form_Load()' You could combine the following 5 steps in' the .Open methodobjRecordset.ActiveConnection = _objConnection 'An ADO connection objectobjRecordset.CursorLocation = adUseClient' Must use client-side server with this' property!objRecordset.CursorType = adOpenStatic' Must use this with client-sided server cursorobjRecordset.LockType = adLockBatchOptimistic' Hooking this up to a bound grid in a' disconnected mode using the Northwind' databaseobjRecordset.Open "SELECT * " & _"FROM Customers JOIN Orders ON " & _"Customers.CustomerID = " & _"Orders.CustomerID WHERE city = " & _"'London' ORDER BY CustomerID"objRecordset.Properties("Unique Table"). _Value = "Orders"objRecordset.Properties("Resync " & _Command").Value = "SELECT * FROM " & _"(SELECT * FROM Customers JOIN " & _"Orders ON Customers.CustomerID = " & _"Orders.CustomerID WHERE city = " & _"'London' ORDER BY CustomerID) " & _"WHERE Orders.OrderID = ?"objRecordset.ActiveConnection = Nothing' A disconnected ADO recordsetSet grdTest.Datasource = objRecordsetEnd SubPrivate Sub Save()objRecordset.ActiveConnection = _objConnection' Reconnect for the purpose of saving onlyobjRecordset.UpdateBatch' Don't forget to check the ADO errors' collection!objRecordset.ActiveConnection = Nothing' Disconnect againEnd Sub



Share the Post: