[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How do I refresh an MS Access Form that uses an ADODB Recordset as its recordset?

Posted on 2011-04-23
Medium Priority
Last Modified: 2012-05-11
I have always been able to refresh a form that has a query as the Record Source from another form using the following code:

 If CurrentProject.AllForms("frmNeedsRefreshing").IsLoaded Then Form_frmNeedsRefreshing.Refresh

I use this method when I need to edit the underlying record from an edit form.

I'm trying to do the same thing with a new form, I left the Record Source blank and instead I populate it in the On Load event using an adodb recordset. The problem is that now I can't refresh the form from the edit form using the above method. Here is the code I use to populate the form with:

Dim oaNum As String
 oaNum = Nz(Form_frmEngineeringHomePage.cboOaNumbers, "%")

 Dim cmd As New ADODB.Command
 Dim rst As New ADODB.Recordset
 With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandText = "spSelectENG_OverAndAboveDetails"
    .CommandType = adCmdStoredProc
    .CommandTimeout = 60000
    .Parameters("@OA_Nr") = oaNum
    Set rst = .Execute()
 End With
 Set Me.Recordset = rst

Open in new window

There must be a way but I have been trying several different things and can not refresh the form, any ideas?
Question by:AkAlan
  • 5
  • 4
LVL 85
ID: 35454896
Just re-run that code - in other words, rebuild the recordset, and re-set the Form's recordset to the one you just built.

LVL 44

Accepted Solution

Leigh Purvis earned 2000 total points
ID: 35456161
Well... Can we examine your situation a bit more?

Fundamentally - wanting to update a recordset that is already open would be complete achieved by re-executing it.  It doesn't get any more refreshed than that. ;-)
But that's a full recordset command executed.
Can you achieve it by another means, well yes - though probably not as you have it.
ADO's Resync command is essentially for the purpose of updating the contents of a recordset's cache (updates usually made by other connections/users is the intent).
(Even if can cause a bit of a lag on unecessary large or complex recordsets.)

However your read only recordset most likely won't support that method.  If you allowed updating on the recordset then it would support the Resync method and your "Refresh" would effectively serviced.
To use a non forward only recordset you'd have to change your code though.  The Execute method inherently returns a Read Only, Forward Only recordset.  That might be what you want to an extent (this form is for viewing a list of records from which to choose to edit - therefore updatability isn't required or desired) but it's also hampering you.

rst.Open cmd, , , adLockOptimistic

should get you a recordset supporting Resync.
So... then have you achieved all you want?
You can protect that recordset data easily enough by disallowing Edits and Additions at the form level. (Assuming that the recordset even returns updatable, once bound to a form - there's no guarantee of that).
If you opened a Keyset or Static cursor type then that method would be supported - and you'd have other editing options too.

However... I notice two other things about your code...
.CommandText = "spSelectENG_OverAndAboveDetails"
.ActiveConnection = CurrentProject.Connection
The former suggests that this is a SQL (or other server based) database.  Unless you've taken to naming your Jet queries with a prefix such as server objects (there's nothing inherently wrong with that - but it's just not clear which you have).
If this is a server object - then the fact that you're using the current connection to execute it implies that this is an ADP you're using (which would have really been better mentioned in the question - and included as a zone!)
If it is an ADP, then you're using a client side cursor - and there's then the reasonable assumption that this isn't a Forward Only recordset but a Static every time.

Consequently - it's unclear where we're at right now.
But it seems, one way or another, you're calling an "Edit" form, making changes, closing it and then wanting the original selection form's recordset to reflect those changes.
If you make (or already have) a recordset which supports editing and convenient binding - then have you considered just using your existing recordset to pass to the "Edit" form, making the changes in that and therefore not requiring a refresh of any kind (and certainly not a re-execution of the entire recordset - a fairly heavy option.)

For example if your list form's recordset is editable (and not forward only) then launch your edit form - take a clone of the loaded recordset, filter it to the one record you want, assign it to the called Edit form, close that form when ready and then your list form's recordset will be updated by the shared scope of that edit.
That's both efficient and fast, without the need for Resyncing or reloading.
But it depends on the type of recordset you open the form with.

Oh... one final thing.
That's generally unnecessary in code for Jet queries - so I'm thinking even more that this is a SQL SP (the clue is in also the name "@OA_Nr" ;-)
However in server data requests, that's the slightly lazy option - as it requires a round trip to the server merely to determine what parameters are available for the requested command object.
Creating and appending the parameters to the command in code without refreshing (implicitly or otherwise) is a cleaner alternative.


Author Comment

ID: 35456673

@LPurvis, You certainly have a good understanding of my situation. I am using an adp against a SQL Server, I really didn't think to mention that fact, nor did I know there was a specific zone for it.
The reason I'm using a recordset for a couple of reasons but mostly because the stored procedure can take up to 45 seconds to load depending on the user selection and by using a recordset I can design a lot faster by being able to get in and out of the form design mode. I don't allow updating on the forms recordset because then I would have to give permissions directly to the tables and I don't want to go that route (unless I am wrong on that, but have fought that battle and lost).

 I am able to solve my immediate issue from your post by commenting out the .execute() line and adding your suggestion:

 '''Set rst = .Execute()
 rst.Open cmd, , , adLockOptimistic
I also changed the code on the Update button to this:

If CurrentProject.AllForms("frmNeedsRefreshing").IsLoaded Then Form_frmNeedsRefreshing.Recordset.Resync
I will

As for the .Parameters.Refresh, I don't know why I put that in there, guess I saw it used for another purpose somewhere else and didn't realize it's purpose. I have removed it and everything still works so I'll leave it out. I didn't have to make any changes like creating or appending the parameters. All I need to do is disallow Edits and Additions at the form level as you stated.

I like the option you mentioned about using a clone and I would like to give that a try when I have time to play with it, not something I have done before and I have to get this product out today. You certainly helped me achieve that goal, Thanks for the very detailed post, not only solved my issue but tought me a couple of things as well.

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 44

Expert Comment

by:Leigh Purvis
ID: 35456785

I appreciate the decision not to expose permissions to the underlying tables (part of the requirements to support such updatability in forms bound to SPs in an ADP).
I presume your update/edit dialog form is bound to something else or instead is unbound?
This would make the clone of the recordset alternative less helpful - i.e. the recordset itself is inherently non-updatable without permission on the underlying table(s) selected from the SP.  You'll need to stick with your current mechanism (another SP purely for updating a specific record perhaps?)

I know that there's no requirement for the explicit .Parameters.Refresh command.
What I was saying is that by using it, or even by just refering to a member of the parameters collection you're performing that implicitly - and therefore creating another server trip.

Instead creating the parameter is a preferable alternative.
i.e. instead of:
    .Parameters("@OA_Nr") = oaNum
    .Parameters.Append .CreateParameter("@OA_Nr", adVarChar, adParamInput, 50, oaNum)
or whatever appropriately matches your parameter's definition.

Executing that means you're only hitting the server to perform the actual execution. Not once before to fetch the SP's defintion too.

Glad you're sorted.


Author Comment

ID: 35456824
Thanks for sharing thoughts.
Yes my update/insert form is unbound, I go through the pain of populating every field with the value from the original form if it is used for editing. A pain but I don't see a cleaner solution. I then have a sproc that performs the update/insert.

I never understood the difference in the way you showed how to define the parameters until now, makes sense to me. I'll have to start using your method even though our LAN is pretty fast and I don't notice a lag time due to my inefficient method, I do like to do things the right way if I can.

All that having been said, I am making the move to WPF for my local applications very soon. I can develope faster in Access now but it just isn't as robust as I would like it to be.

Thanks again, very good stuff to know.

BTW, I was reading your bio and it made me think of Mark Knopffler and all the songs he sings about Northumberland and the Tyne river. Good stuff.
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35456850
That's where I'm from originally.  Born and bred.  (Only a little displaced these days.)

Loading the values into your unbound form can still be relatively painless, if you name your controls analogously to the data fields and use the recordset from the display form to fill those values (by iterating through its fields collection without having to specifically assign controls by name).  
Then it's just a question of executing your SP passing the values from the controls as they then stand (as you already do.)

All the best.

Author Comment

ID: 35456891
Great,  not sure exactly how to go about doing that (the iteration part), you wouldn't have a small example already done would you? I did see your example of an unbound form but you pretty much do like I do except that I am grabbing the filed value from the original display form rather than a recordset.
LVL 44

Expert Comment

by:Leigh Purvis
ID: 35457283
Yeah that example is pretty simplistic.
This is the kind of thing - still simplistic of course.

Author Comment

ID: 35457306
Got it, pretty cool, should really speed things up for me. Thanks a bunch.

Author Comment

ID: 35471298
Leigh, just so you know, I posted another follow up question to this in case you are bored.


Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question