By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,986 Members | 2,042 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,986 IT Pros & Developers. It's quick & easy.

Make Access program run better vs. migrating to SQL

P: 39
I know that when using the standard Access wizards your Forms(.RecordSource) and your Combo Boxes(.RowSource) end up constantly connected to your data. That is bad practice in any multi-user environment. So my first question is how do you fix that.
Forms are easy enough donít use .RecordSource and donít use bound text boxes, instead have the Form make an ADODB request during Form_Load and then fill in those text boxes as unbound text boxes instead, RIGHT?
What about the Combo Boxes? Instead of using a SQL statement in your .RowSource, shouldnít you use a query? And then in that query properties set the Recordset Type to Snapshot? Doesnít that completely disconnect your form from the data? Am I thinking correctly here? And isnít this also moving your application closer to a VB solution?
Aug 30 '08 #1
Share this Question
Share on Google+
5 Replies


nico5038
Expert 2.5K+
P: 3,072
Just puzzled by the statement: "That is bad practice in any multi-user environment."

As long as you're not using an Access database for over 20 users, I wouldn't care about this "bad practice". The bound forms (and comboboxes) in Access are a real nice feature and all issues regarding multiple users are "solved" automatically by Access.

The alternative (the unbound forms) will require that you don't handle the database I/O, but also the concurrent update problem yourself....

Nic;o)
Aug 30 '08 #2

NeoPa
Expert Mod 15k+
P: 31,347
...
What about the Combo Boxes? Instead of using a SQL statement in your .RowSource, shouldnít you use a query? And then in that query properties set the Recordset Type to Snapshot? Doesnít that completely disconnect your form from the data? Am I thinking correctly here? And isnít this also moving your application closer to a VB solution?
In essence yes.

It's using Access, but trying to work around all that Access does for you.

A VB solution would use Access simply as the back-end.
Aug 30 '08 #3

ADezii
Expert 5K+
P: 8,619
To the best of my knowledge, a Form does not need a persistent connection to its underlying data in order to be functional. One of the little known, and very flexible, features of ADO is that an ADO Recordset can be disconnected from its Data Source, modified, then reconnected again in order to Update the Data Source. This should all be possible because of the ability to assign an ADO Recordset directly to the Recordset Property of a Form. Even though the Recordset is not connected from its Data Source, you can make changes to the data and save them using the Update Method. All changes are stored in the Recordset until the UpdateBatch Method is invoked. The Cursor Location must also be set to the Client. I've never used this functionality in the past, but if you are interested, I'll demonstrate how it works.
Aug 31 '08 #4

P: 39
To the best of my knowledge, a Form does not need a persistent connection to its underlying data in order to be functional. One of the little known, and very flexible, features of ADO is that an ADO Recordset can be disconnected from its Data Source, modified, then reconnected again in order to Update the Data Source. This should all be possible because of the ability to assign an ADO Recordset directly to the Recordset Property of a Form. Even though the Recordset is not connected from its Data Source, you can make changes to the data and save them using the Update Method. All changes are stored in the Recordset until the UpdateBatch Method is invoked. The Cursor Location must also be set to the Client. I've never used this functionality in the past, but if you are interested, I'll demonstrate how it works.
I am interested in seeing that code for Forms. What do you have to say about the combo boxes? Thanks Ken
Aug 31 '08 #5

ADezii
Expert 5K+
P: 8,619
I am interested in seeing that code for Forms. What do you have to say about the combo boxes? Thanks Ken
I'll demonstrate the principles behind a Disconnected Recordset using a Local Database, but keep in mind that the proper context would be with a File-Server or Clien-Server architecture:
  1. Create Global Variables to represent the Connection and Recordset Objects:
    Expand|Select|Wrap|Line Numbers
    1. Public rstDisconnect As ADODB.Recordset
    2. Public cnn As ADODB.Connection
  2. Make sure that your Form is 'Unbound' and is not tied an any Data Source.
  3. In the Form's Open() Event: dynamically assign Control Sources to the Form's Controls, set up the Recordset, and assign it to the Recordset Property of the Form, essentially making it now 'Bound':
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Open(Cancel As Integer)
    2. Set rstDisconnect = New ADODB.Recordset
    3. Set cnn = CurrentProject.Connection
    4.  
    5. 'Dynamically assign Control Sources for the Form's Controls.
    6. 'This can be done manually, also, just make sure the Form is
    7. '"Unbound"
    8. Me![txtFirstName].ControlSource = "FirstName"
    9. Me![txtLastName].ControlSource = "LastName"
    10.  
    11. With rstDisconnect
    12.   .Source = "Select * From Employees;"
    13.   .ActiveConnection = cnn
    14.   .CursorType = adOpenKeyset
    15.   .CursorLocation = adUseClient               'Critical
    16.   .LockType = adLockBatchOptimistic           'Critical
    17.     .Open
    18. End With
    19.  
    20. 'Dynamically assign Data Source to the Form
    21. Set Me.Recordset = rstDisconnect
    22.  
    23. 'Retrieve the First and Last Name for trhe Current Record
    24. Debug.Print rstDisconnect![FirstName] & " " & rstDisconnect![LastName]
    25. End Sub
  4. Disconnect the Recordset, make several changes, Reconnect the Recordset, then write the changes back to the Data Source.
    Expand|Select|Wrap|Line Numbers
    1. 'Disconnect the Recordset by setting its ActiveConnection to Nothing, but
    2. 'do not Close the Connection since a Closed Connection will close all
    3. 'associated Recordsets
    4. Set cnn = Nothing
    5.  
    6. 'Modify the data via the Form Interface. Even though the Recordset is now
    7. 'disconnected from its Data Source, it is still fully functional. All
    8. 'changes will be stored in the Recordset until the UpdateBatch()
    9. 'Method is called
    10.  
    11. 'Let's Reconnect the Recordset by setting it to a valid Connect, then
    12. 'Update the Data Source by calling the UpdateBatch() Method of the
    13. 'Recordset Object. All changes made to the data contained in the From
    14. 'while the Recordset was disconnected will now be sent back to the Data Source
    15. Set cnn = CurrentProject.Connection
    16. rstDisconnect.UpdateBatch
  5. At some point in time, do your clean-up-chores:
    Expand|Select|Wrap|Line Numbers
    1. rstDisconnect.Close
    2. cnn.Close
    3. Set rstDisconnect = Nothing
    4. Set cnn = Nothing
Aug 31 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.