473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Make Access program run better vs. migrating to SQL

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
5 1380
nico5038
3,080 Expert 2GB
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
32,556 Expert Mod 16PB
...
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
8,834 Expert 8TB
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
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
8,834 Expert 8TB
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

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

Similar topics

2
by: lcifers | last post by:
First off, sorry if my cross posting offends anyone. I'm posting this in Access and SQL Server groups - not sure which one is appropriate. I have a relatively simple ASP.NET/VB.NET application...
6
by: ASP.Confused | last post by:
I have an ASP.NET page writtein in VB that uses ADODB. I just had to force-install MDAC 2.8 after I tried to rollback to 2.6 (my web host uses this, and I wanted to be compatible with them.) I...
0
by: ASP.Confused | last post by:
The old message looked a little stale, so I am re-posting it here. Anybody have any ideas of what I could do?!? The previous responses to this question are below. If you want to look at the...
3
by: Ron L | last post by:
I have an existing ASP/ MSSQL project that we are looking at migrating to VB.Net (standalone app, not ASP) which will still interact with a single central SQL server. Because we have a requirement...
6
by: scottyman | last post by:
I can't make this script work properly. I've gone as far as I can with it and the rest is out of my ability. I can do some html editing but I'm lost in the Java world. The script at the bottom of...
6
by: Nuti | last post by:
Hi, We are actually migrating the whole application(data and the forms,reports..everything) to access. I think migrating the data (.dbf files) in to access will not be a problem.
8
by: Shooter4Life8 | last post by:
I am trying to run a macro from my VB.NET program. Here is my code. Dim myAccess As Access.Application Dim allMacro As String = "ALL-Macros" myAccess.DoCmd.RunMacro(allMacro) I get the error....
26
by: codercode | last post by:
I'm working on a Visual Basic .NET using Access database. However, my client already have a 30MB database with Sybase ASA and Sybase is way too much for that. Is there anyway I can migrate from a...
64
by: John | last post by:
Hello there, Im cursing my place of employment...and its taken me a month to realise it... The scenario: Ive just stepped into a role to migrate an access database to VB.Net. The access...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.