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:
Share on facebook
Share on twitter
Share on linkedin


The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS