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 SubIt
