Happy Codings - Programming Code Examples
Html Css Web Design Sample Codes CPlusPlus Programming Sample Codes JavaScript Programming Sample Codes C Programming Sample Codes CSharp Programming Sample Codes Java Programming Sample Codes Php Programming Sample Codes Visual Basic Programming Sample Codes


Visual Basic Programming Code Examples

Visual Basic > Database SQL Stuff Code Examples

Updating a database using a disconnected recordset

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
Updating a database using a disconnected recordset The following code shows a demonstration of how to update a database using a disconnected recordset. Option Explicit 'Purpose : Open a disconnected recordset. 'Inputs : oCon The connection to open the recordset on. ' sSQL The SQL to open the recordset with. ' oRS The resulting recordset. 'Outputs : Returns True if the recordset was opened and contained results. Public Function RSOpenDisconnected(oCon As ADODB.Connection, sSQL As String, oRS As ADODB.Recordset, Optional eLocking As LockTypeEnum = adLockBatchOptimistic) As Boolean On Error GoTo ErrFailed If oCon.State = adStateOpen Then 'Connection is open Set oRS = New ADODB.Recordset 'Set cursor to client (i.e. Local Machine) oRS.CursorLocation = adUseClient 'Open Recordset (NB. The CursorType is always "adOpenStatic" for client-side cursors) oRS.Open sSQL, oCon, adOpenStatic, eLocking 'Set connection to nothing (disconnect recordset) Set oRS.ActiveConnection = Nothing If oRS.EOF = False Then 'Return results RSOpenDisconnected = True Else 'Empty recorset RSOpenDisconnected = False End If End If Exit Function ErrFailed: Debug.Print "Failed to open recordset: " & Err.Description Debug.Assert False 'Error occurred RSOpenDisconnected = False On Error GoTo 0 End Function 'Demonstrates how to open a disconnected recordset and then update a value in 'a field in the database. Sub Test() Dim oRS As ADODB.Recordset Dim oConn As ADODB.Connection Const clRecordUpdated As Long = -2147217864 On Error GoTo ErrUpdateFailed 'Open a connection to a database Set oConn = New ADODB.Connection oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyPath\MyDatabase.mdb; Persist Security Info=False" 'Open a disconnected recordset RSOpenDisconnected oConn, "Select * from tblHoliday", oRS 'Update a field in the recordset oRS.Fields("MyField").Value = "My New Value" 'Reconnect to database Set oRS.ActiveConnection = oConn 'Update table oRS.UpdateBatch 'Close the connection oRS.Close Set oRS = Nothing oConn.Close Set oConn = Nothing Exit Sub ErrUpdateFailed: If Err.Number = clRecordUpdated Then MsgBox "The record you altered has been altered by another user... " & vbNewLine & Err.Description, vbCritical Else MsgBox "Error in disconnected update routine... " & vbNewLine & Err.Description, vbCritical End If End Sub