I am a beginner with database designing (this is my first one), and I recently used ADO for the first time, adapting from examples to fit my needs (could have been DAO but I wanted to learn a bit about how ADO works). My function works fine till now (I didn t test it in a multi-user environment) but there are still things I don t understand about the recordset I used for the purpose:
When I open a recordset that way:
Dim Cn As ADODB.Connection
Dim RcProv As New ADODB.Recordset
Set Cn = CurrentProject.Connection
Rst.Open "HereComesAnSQLquery", Cn, adOpenKeyset, adLockOptimistic
-Is Rst part of the table the query is based on, or is it a clone selection (= do I work directly on the table or on a clone?). I am pretty sure that I work on the table directly but what confuses me is that the changes I make are not commited until I apply Rst.update
-If this makes me work directly on the table, how could I work with a clone recordset?
- Still assuming that it makes me work directly on the table, if I open Rst using AdLockOptimistic, what happens if when Rst.update is applied, someone is at the same time editing a record of Rst?
In my function, I opened other recordsets the same way, but using AdLockReadOnly, since I only needed to read values from them.
What happens if when I open such a recordset, someone is editing one of it s records?
- In my case, Rst is always made of one record (the SQL defining the recordset is based on the criteria of the kind: PrimKeyInTable = ControlOnFormValue)
Is there a more efficient way to select and work with an (always existing) unique record?
-Finally, after closing my recordsets, do I need to Set them to nothing? If a recordset I am working on includes the record currently edited by the user, how do I close it?
Thank in advance for any insight.
When I open a recordset that way:
Dim Cn As ADODB.Connection
Dim RcProv As New ADODB.Recordset
Set Cn = CurrentProject.Connection
Rst.Open "HereComesAnSQLquery", Cn, adOpenKeyset, adLockOptimistic
-Is Rst part of the table the query is based on, or is it a clone selection (= do I work directly on the table or on a clone?). I am pretty sure that I work on the table directly but what confuses me is that the changes I make are not commited until I apply Rst.update
-If this makes me work directly on the table, how could I work with a clone recordset?
- Still assuming that it makes me work directly on the table, if I open Rst using AdLockOptimistic, what happens if when Rst.update is applied, someone is at the same time editing a record of Rst?
In my function, I opened other recordsets the same way, but using AdLockReadOnly, since I only needed to read values from them.
What happens if when I open such a recordset, someone is editing one of it s records?
- In my case, Rst is always made of one record (the SQL defining the recordset is based on the criteria of the kind: PrimKeyInTable = ControlOnFormValue)
Is there a more efficient way to select and work with an (always existing) unique record?
-Finally, after closing my recordsets, do I need to Set them to nothing? If a recordset I am working on includes the record currently edited by the user, how do I close it?
Thank in advance for any insight.
