Binding forms to ADODB recordsets

[Scott Lichtenberg]

I have been using the technique shown below to create forms bound to SQL
server tables and views using ADODB recordsets. It works so well that it
seems almost too good to be true.

My major concern is whether using ADODB recordsets will lead to deadlock
problems on the SQL server? Even though I have closed the connection object
and the original recordset object, the recordset, which is now bound to the
form's recordset property, remains open as long as the form is.

What happens if a user opens a form, thereby loading data into the form's
recordset property, then decides to go to lunch, leaving the form open? My
company has about 100 users, so it is likely that one or more of them would
try to query, or even edit records in the out-to-lunch user's open form.
I'm led to believe that ADODB creates a disconnected recordset, so there
shouldn't be any issues. However, I would really like to hear this from
someone who actually knows how this works.

Thanks in advance for any help.
Scott



==========
KB Article ID Q281998
How to bind Microsoft Access forms to ADO recordsets

Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

'Create a new ADO Connection object
Set cn = New ADODB.Connection

'Use the Access 10 and SQL Server OLEDB providers to
'open the Connection
'You will need to replace MySQLServer with the name
'of a valid SQL Server
With cn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "MySQLServer"
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.Properties("Initial Catalog").Value = "NorthwindCS"
.Open
End With

'Create an instance of the ADO Recordset class, and
'set its properties
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM Customers"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
End With

'Set the form's Recordset property to the ADO recordset
Set Me.Recordset = rs
Set rs = Nothing
Set cn = Nothing
End Sub

 

[Dave Patrick]

It shouldn't unless you're doing something that might cause record locking
to escalate into table locking. Just curios, why don't you just use ODBC
linked tables?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
|I have been using the technique shown below to create forms bound to SQL
| server tables and views using ADODB recordsets. It works so well that it
| seems almost too good to be true.
|
| My major concern is whether using ADODB recordsets will lead to deadlock
| problems on the SQL server? Even though I have closed the connection
object
| and the original recordset object, the recordset, which is now bound to
the
| form's recordset property, remains open as long as the form is.
|
| What happens if a user opens a form, thereby loading data into the form's
| recordset property, then decides to go to lunch, leaving the form open?
My
| company has about 100 users, so it is likely that one or more of them
would
| try to query, or even edit records in the out-to-lunch user's open form.
| I'm led to believe that ADODB creates a disconnected recordset, so there
| shouldn't be any issues. However, I would really like to hear this from
| someone who actually knows how this works.
|
| Thanks in advance for any help.
| Scott
|
|
|
| ==========
| KB Article ID Q281998
| How to bind Microsoft Access forms to ADO recordsets
|
| Private Sub Form_Open(Cancel As Integer)
| Dim cn As ADODB.Connection
| Dim rs As ADODB.Recordset
|
| 'Create a new ADO Connection object
| Set cn = New ADODB.Connection
|
| 'Use the Access 10 and SQL Server OLEDB providers to
| 'open the Connection
| 'You will need to replace MySQLServer with the name
| 'of a valid SQL Server
| With cn
| .Provider = "Microsoft.Access.OLEDB.10.0"
| .Properties("Data Provider").Value = "SQLOLEDB"
| .Properties("Data Source").Value = "MySQLServer"
| .Properties("User ID").Value = "sa"
| .Properties("Password").Value = ""
| .Properties("Initial Catalog").Value = "NorthwindCS"
| .Open
| End With
|
| 'Create an instance of the ADO Recordset class, and
| 'set its properties
| Set rs = New ADODB.Recordset
| With rs
| Set .ActiveConnection = cn
| .Source = "SELECT * FROM Customers"
| .LockType = adLockOptimistic
| .CursorType = adOpenKeyset
| .Open
| End With
|
| 'Set the form's Recordset property to the ADO recordset
| Set Me.Recordset = rs
| Set rs = Nothing
| Set cn = Nothing
| End Sub
|
|

 

[Robert Morley]

As Dave said, their shouldn't be any locks maintained over the network, just
as long as you're closing the connection. That should create a disconnected
recordset, and you're fine after that.

The one thing to make sure of, though, is that you're aware of any potential
errors to multiple records when you re-connect the recordset. These would
happen if, as you said, one user makes changes to several records and goes
to lunch. While that person is away, another person goes in and makes
conflicting changes to some of the same records and saves them properly.
When the first person comes back from lunch and eventually remembers to save
his changes, there will be conflicts involving multiple records, and you'll
need to handle all of them.

The above presumes that conflicts are even possible...if none of your users
can edit the same records as any other user, then you're fine. Similarly,
if you don't care about conflicts, then you're also fine, though I'm not
entirely sure who "wins" in a conflict situation...obviously, the first
person's changes will be saved with no conflict being flagged, I'm just not
sure whether ADODB keeps or discards conflicting records if you ignore the
errors when the second person tries to save their changes...I would highly
suspect they'd be discarded. It's entirely possible it would discard the
whole batch, but I don't think so.

Look around the ADODB help and try things out, and if you need further
confirmation, let us know (and send us some code so we know we're
re-creating the same conditions as you're using).



Rob