RecordSet update taking too long for listbox to requery new information

[baulrich]

I've been using the following code and it's always worked fine in testing. Recently I put the project in production and now more than one user is connected to the database. I have the project broken into front-end and back-end DBs... both are on a shared network drive. Since the move to production, the listbox requery does not show the new record(s) added.... at least not right away. If the user waits a few seconds and refreshes the form, the SQL update has taken place and everything displays correctly. I've already taken the recommended step of creating a persistent connection between the FE DB and BE BD by using the startup form to create an always-open connection to the backend DB.

Private Sub Add_Divsion_of_Work_Click()

Dim strSQL As String
Dim rs As Recordset

If IsNull(Me.combo_subdivision_lookup.Value) Then
MsgBox ("You must make a selection before adding")
Else

strSQL = "SELECT * from company_division where company_division.company_id = " & Me.ID.Value & _
" AND company_division.subdivision_number = '" & Me.combo_subdivision_lookup.Column(2) & "';"

Set rs = CurrentDb.OpenRecordset(strSQL)

If rs.EOF Then
    
    
    rs.AddNew
    'MsgBox ("Debug: Adding company ID: " & Me.ID.Value)
    rs!company_id = Me.ID.Value
    'MsgBox ("Debug: Adding division ID: " & Me.combo_subdivision_lookup.Column(2))
    rs!subdivision_number = Me.combo_subdivision_lookup.Column(2)
    rs.Update
Else
    MsgBox ("This division of work is already defined for this company")
End If

rs.Close
Set rs = Nothing

Me.List_of_subdivisions.Requery
End If
End Sub

[ssanfu]

You have unmatched IF... END IF elements.

Count of:
IF = 1
END = 2
END IF = 2

Here is the code indented:

Private Sub Add_Divsion_of_Work_Click()

   Dim strSQL As String
   Dim rs As Recordset

   If IsNull(Me.combo_subdivision_lookup.Value) Then
      MsgBox ("You must make a selection before adding")
   Else

      strSQL = "SELECT * from company_division where company_division.company_id = " & Me.ID.Value & _
               " AND company_division.subdivision_number = '" & Me.combo_subdivision_lookup.Column(2) & "';"

      Set rs = CurrentDb.OpenRecordset(strSQL)


      rs.AddNew
      'MsgBox ("Debug: Adding company ID: " & Me.ID.Value)
      rs!company_id = Me.ID.Value
      'MsgBox ("Debug: Adding division ID: " & Me.combo_subdivision_lookup.Column(2))
      rs!subdivision_number = Me.combo_subdivision_lookup.Column(2)
      rs.Update
   Else
      MsgBox ("This division of work is already defined for this company")
   End If

   rs.Close
   Set rs = Nothing

   Me.List_of_subdivisions.Requery
End If

End Sub

[baulrich]

Sorry about that. Had a problem with copy and paste. The code should be correct now.

thanks....

[ssanfu]

".Value" is the default property - not necessary to add it - but it doesn't hurt.
I've read that specifying the field names is faster than selecting all fields (*)
When I add records to a recordset I already have open, I create another recordset (append only) to add the records. I've had less problems...

I didn't test this code, but it should work.....

Private Sub Add_Divsion_of_Work_Click()

   Dim strSQL As String
   Dim rs As DAO.Recordset
   Dim n As DAO.Recordset

   If IsNull(Me.combo_subdivision_lookup) Then
      MsgBox ("You must make a selection before adding")
   Else
      strSQL = "SELECT company_id FROM company_division WHERE company_division.company_id = " & Me.ID & _
               " AND company_division.subdivision_number = '" & Me.combo_subdivision_lookup.Column(2) & "';"

      Set rs = CurrentDb.OpenRecordset(strSQL)

      If rs.EOF Then
         strSQL = "SELECT company_id, subdivision_number FROM company_division"
         Set n = CurrentDb.OpenRecordset(strSQL, , dbAppendOnly)

         n.AddNew
         'MsgBox ("Debug: Adding company ID: " & Me.ID.Value)
         n!company_id = Me.ID
         'MsgBox ("Debug: Adding division ID: " & Me.combo_subdivision_lookup.Column(2))
         n!subdivision_number = Me.combo_subdivision_lookup.Column(2)
         n.Update

         n.Close
         Set n = Nothing
      Else
         MsgBox ("This division of work is already defined for this company")
      End If

      rs.Close
      Set rs = Nothing

      Me.List_of_subdivisions.Requery
   End If
End Sub

Don't know if the above code mods will help....
Multi-users across a network seems to always slow the response time.

[RonL]

...........
When I add records to a recordset I already have open, I create another recordset (append only) to add the records. I've had less problems...

............

A theoretical followup: Does that code actually create another recordset? From my (novice) reading, I'd have thought it's setting a second variable to the *original* recordset. No? If not, then how does Access transpose the changes from the new recordset to the original? (And what's the purpose of the "NEW" keyword in a Set statement?)

-Ron (...who's just trying to wrap head around recordset objects.)

[baulrich]

Steve,

Thanks. I tried (something very similar to) what you suggested. The problem remains the same. I also have some new info about the problem. I've been able to test the project without other users connect and the problem persists even when no other users are connected. Unfortunately, I think the problem was related to a compact routine that I ran just before putting the file into use.

Here's the code I put in place that's more like what you suggested. It seems your second query (after the If rs.EOF was redundant) so this should be even faster than what you suggested.

If I go back to an old backup file (before compaction, running the exact same code) it runs fine. But the new file is slow, even with only one user connected. Unfortunately I have made many changes since that backup.

Any ideas on how to pinpoint the problem caused by the compaction routine???

thanks,

baulrich

If IsNull(Me.combo_subdivision_lookup.Value) Then
MsgBox ("You must make a selection before adding")
Else

strSQL  = "SELECT company_id, subdivision_number from company_division where  company_division.company_id = " & Me.ID.Value & _
" AND company_division.subdivision_number = '" & Me.combo_subdivision_lookup.Column(2) & "';"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbAppendOnly)

If rs.EOF Then

    rs.AddNew
    MsgBox ("Debug: Adding company ID: " & Me.ID.Value)
    rs!company_id = Me.ID
    MsgBox ("Debug: Adding division ID: " & Me.combo_subdivision_lookup.Column(2))
    rs!subdivision_number = Me.combo_subdivision_lookup.Column(2)
    rs.Update

    
   
Else
    MsgBox ("This division of work is already defined for this company")
End If

rs.Close
Set rs = Nothing

Me.List_of_subdivisions.Requery
End If
End Sub

[ssanfu]

Does that code actually create another recordset?

Yes.

I'd have thought it's setting a second variable to the *original* recordset. No?

No, not in this case. The first recordset (rs) is filtering the table to find a specific combination of values.
the second recordset (n) is on the whole (unfiltered) table, but in append only mode.

The majority of the time I use

strSQL = "INSERT INTO company_division (company_id, subdivision_number) VALUES (" & Me.ID & ", '" & Me.combo_subdivision_lookup.Column(2) & "');"

CurrentDb.Execute strSQL, dbFailOnError

I use the strSQL string so I can use "Debug.Print strSQL" to see if the string is configured correctly with the correct values.

(And what's the purpose of the "NEW" keyword in a Set statement?)

I don't use it, but HELP says

Generally, when you use Set to assign an object reference to a variable, no copy of the object is created for that variable. Instead, a reference to the object is created. More than one object variable can refer to the same object. Because such variables are references to the object rather than copies of the object, any change in the object is reflected in all variables that refer to it. However, when you use the New keyword in the Set statement, you are actually creating an instance of the object.

[ssanfu]

Baulrich,

I don't think you can use a append only recordset to see if a value or values are in a table. It is append only. (I think rs.EOF would always be true)
You would need two recordsets.... One to read (filtered), and one to write (append) to. Or use your original code.

As far as the compact problem, you could delete the code, then import all objects into a fresh database. That would (should) get rid of any corruption. Then re-add the code (your original code or mine, BUT not the last code you posted).

[ssanfu]

I should have checked.... You can use "Set rs = CurrentDb.OpenRecordset(strSQL, , dbAppendOnly)"

But I still think using two recordsets would be better.

[RonL]

ssanfu: Thanks for the response. Yes, I'd seen the help file. Unfortunately it adds to my confusion, because it implies that without the "NEW" keyword, there is no new object. Perhaps I'm confusing the concept of a new recordset, say a filtered version of the original, with a newly instantiated object. I guess they're not necessarily the same thing. Perhaps the assignment of the filtered recordset in a Set statement (without NEW) is really just a pointer to a subset of the original records in their original memory space?

Sorry to detract from the OPs thread. -Ron

[Rod]

Concerning that 'New' keyword:

1. 'New' forces VBA to create a new instance of the object and must be used in an assignment if the source is a class. For example: Set frmMyName = New Form_frmForm1 or Set colNames = New VBA.Collection. (Unless of course you wish to reference the class itself!)
2. 'New' is not required (indeed I thought it created a compilation error) when assigning an additional reference to an existing instance. For example Set frmAltRef = frmMyName or Set colNames1 = colNames.
3. Some functions return objects. 'OpenRecordset' is such a function. When using an object-returning function as the source for an assignment, no 'New' keyword is required.

You may use the 'New' keyword in a dimensioning declaration such as Dim colNames as New VBA.Collection. In such cases VBA creates a new instance the moment the object variable is first encountered in code execution. I personally don't like this option as I feel I have less control.

Hope I'm not teaching Grannie to suck eggs.

PS RonL: The internals of JET are not immediately available to mere mortals such as us. What you describe is my understanding of what occurs for a recordset clone, a new pointer (or cursor) for the original recordset. What OpenRecordset does is to create a new recordset and pass back the object reference to that new recordset. You will get effectively a new independent recordset for each OpenRecordset. How JET achieves this in detail is something else.

[RonL]

Concerning that 'New' keyword:

1. 'New' forces VBA to create a new instance of the object and must be used in an assignment if the source is a class. For example: Set frmMyName = New Form_frmForm1 or Set colNames = New VBA.Collection. (Unless of course you wish to reference the class itself!)
2. 'New' is not required (indeed I thought it created a compilation error) when assigning an additional reference to an existing instance. For example Set frmAltRef = frmMyName or Set colNames1 = colNames.
3. Some functions return objects. 'OpenRecordset' is such a function. When using an object-returning function as the source for an assignment, no 'New' keyword is required.

You may use the 'New' keyword in a dimensioning declaration such as Dim colNames as New VBA.Collection. In such cases VBA creates a new instance the moment the object variable is first encountered in code execution. I personally don't like this option as I feel I have less control.

Hope I'm not teaching Grannie to suck eggs.

PS RonL: The internals of JET are not immediately available to mere mortals such as us. What you describe is my understanding of what occurs for a recordset clone, a new pointer (or cursor) for the original recordset. What OpenRecordset does is to create a new recordset and pass back the object reference to that new recordset. You will get effectively a new independent recordset for each OpenRecordset. How JET achieves this in detail is something else.

Thanks Rod. Ok, in for a penny.... Let me outline a very simple, specific scenario, the likes of which I anticipate dealing with.

First let me say I (think I) understand the recordsetclone property and how it's used (for bookmarking etc.). Here's a good reference:

http://sourcedaddy.com/ms-access/boo...et-clones.html

Now consider a database in which I have a bound form whose record source is a stored query, named myQuery.

The form exposes the record source in datasheet view. The user scrolls through records. There must be an underlyling recordset through whose internal manipulation the actual scrolling is accomplished, right? The only interaction available with that recordset (so far) is through the GUI via the default code embedded in the object classes. Now, we create some code in one of the events associated with that form. For example:

Dim dbs As DAO.Database     
Dim rst As DAO.Recordset           
Set dbs = CurrentDb     
Set rst = dbs.OpenRecordset(myQuery)
rst.MoveNext

Now from what you guys say, rst is a new, instantiated object. That would make it stand apart in memory from the original underlying recordset to which the form is bound. No? Yet, if I'm understanding things, if the user has the form open and pointing to a specific record, then triggers the event associated with the above method (which may need to include a form refresh or requery), the user will see the record cursor move to the next record. How did this happen? Remember, the code refers to a new, independent recordset object. How does this new object convey the intended action to the original recordset?

This is why I was thinking rst - at least in this specific scenario - is not actually a new object but a new object variable pointing to the same object, the original recordset. (The help files confirm that an object may be assigned to any number of variables and that operations on any of those variables will be reflected in that recordset. But help also seems to say what you guys say, that Set creates a new object.)

If as you say Rod, this is just one of those mysteries internal to the Access engine, so be it. But if I'm still missing something fundamental, I'd sure appreciate clarification.

Thanks - Ron

[ssanfu]

Now from what you guys say, rst is a new, instantiated object. That would make it stand apart in memory from the original underlying recordset to which the form is bound. No? Yet, if I'm understanding things, if the user has the form open and pointing to a specific record, then triggers the event associated with the above method (which may need to include a form refresh or requery), the user will see the record cursor move to the next record. How did this happen? Remember, the code refers to a new, independent recordset object. How does this new object convey the intended action to the original recordset?

But the recordset for the form and the recordset created in VBA has to be separate; the VBA recordset cannot change records in the form recordset just by issuing a move command for the VBA recordset.
That is why you have to use Me.BookMark = rs.BookmMark to be able to match records.

Or try this: create a query ("Query_Table") where the record source it "Table1".
Now create another query ("Query_Query") where the record source is "Query_Table".

Open both queries. Change records on one query... does the record change is the other query??
Seems to me that the form record set and the VBA record set work the same way.. they are independent, but point to the same records

At least that is how I have been using VBA recordsets.....

[RonL]

........... the VBA recordset cannot change records in the form recordset just by issuing a move command for the VBA recordset.
..............

I was using the move command to symbolize any processing that changes the form's recordset, but serves me right for making that assertion without testing it myself. I learned the hard way you can't declare an object variable in the immediate window. (Is there a way to test recordset-related code short of running it directly in the module?)

Will keep studying. Thanks for the response.

-Ron

[Rod]

RonL,

There's no difference - as far as the workings of Access' forms - between tables and queries. Actually opening a table is simply a very all-encompassing query. The bound result set - Access uses the term 'Recordset' - is that list of rows (records) that is the source for the data displayed on the form. The only way of obtaining a second reference to this bound recordset is to assign another object reference variable to the original object variable. Then, of course, any pointer movement or data change made using the second variable will be reflected on the form. (Not true actually! Try changing a field value of a bound control in the recordset directly via VBA and the change is not reflected on the form and the change gets overridden when the form is updated. The safe way is to change the value of the bound control.)

A recordset created by any other means (i.e. OpenRecordset) is independent of the bound recordset. It may have exactly the same SQL specification but it is different and independent.

Now it depends upon the type of recordset that is created whether Access/Jet attempts to synchronise the data across the implementation for all affected recordsets.