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:
data observability

Data Observability Explained

Data is the lifeblood of any successful business, as it is the driving force behind critical decision-making, insight generation, and strategic development. However, due to its intricate nature, ensuring the

Heading photo, Metadata.

What is Metadata?

What is metadata? Well, It’s an odd concept to wrap your head around. Metadata is essentially the secondary layer of data that tracks details about the “regular” data. The regular