| {\rtf1\ansi\ansicpg1252\deff0\deflang1044{\fonttbl{\f0\fnil\fcharset0 MS Sans Serif;}} \viewkind4\uc1\pard\f0\fs17 Option Explicit \par \par 'Purpose : Open a disconnected recordset. \par 'Inputs : oCon The connection to open the recordset on. \par ' sSQL The SQL to open the recordset with. \par ' oRS The resulting recordset. \par 'Outputs : Returns True if the recordset was opened and contained results. \par 'Author : Andrew Baker \par 'Date : 23/07/2000 18:52 \par \par \par Public Function RSOpenDisconnected(oCon As ADODB.Connection, sSQL As String, oRS As ADODB.Recordset, Optional eLocking As LockTypeEnum = adLockBatchOptimistic) As Boolean \par On Error GoTo ErrFailed \par If oCon.State = adStateOpen Then \par 'Connection is open \par Set oRS = New ADODB.Recordset \par 'Set cursor to client (i.e. Local Machine) \par oRS.CursorLocation = adUseClient \par 'Open Recordset \par oRS.Open sSQL, oCon, adOpenStatic, eLocking \par 'Set connection to nothing (disconnect recordset) \par Set oRS.ActiveConnection = Nothing \par If oRS.EOF = False Then \par 'Return results \par RSOpenDisconnected = True \par Else \par 'Empty recorset \par RSOpenDisconnected = False \par End If \par End If \par Exit Function \par \par ErrFailed: \par Debug.Print "Failed to open recordset: " & Err.Description \par Debug.Assert False 'Error occurred \par RSOpenDisconnected = False \par On Error GoTo 0 \par End Function \par \par \par 'Demonstrates how to open a disconnected recordset and then update a value in \par 'a field in the database. \par Sub Test() \par Dim oRS As ADODB.Recordset \par Dim oConn As ADODB.Connection \par Const clRecordUpdated As Long = -2147217864 \par \par On Error GoTo ErrUpdateFailed \par 'Open a connection to a database \par Set oConn = New ADODB.Connection \par oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\MyPath\\MyDatabase.mdb; Persist Security Info=False" \par 'Open a disconnected recordset \par RSOpenDisconnected oConn, "Select * from tblHoliday", oRS \par 'Update a field in the recordset \par oRS.Fields("MyField").Value = "My New Value" \par 'Reconnect to database \par Set oRS.ActiveConnection = oConn \par \par 'Update table \par oRS.UpdateBatch \par \par 'Close the connection \par oRS.Close \par Set oRS = Nothing \par oConn.Close \par Set oConn = Nothing \par \par Exit Sub \par \par ErrUpdateFailed: \par If Err.Number = clRecordUpdated Then \par MsgBox "The record you altered has been altered by another user... " & vbNewLine & Err.Description, vbCritical \par Else \par MsgBox "Error in disconnected update routine... " & vbNewLine & Err.Description, vbCritical \par End If \par End Sub \par } |
Updating a database using a disconnected recordset |
India web developer web development India | India web development company India ecommerce web developer