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

Can't open query on updated recordset without exiting/re-entering macro

P: n/a
My macro is writing to a table(T) and reading a query (Q) based on that
table (VBA/Visual Basic 6.3). I cannot write T and read Q in the same
execution of the macro, however, if I skip the writing part I can read
Q just fine.

How does closing the macro reset the state of the system?

The SQL for Q is--
SELECT Relatesto.contains, Relatesto.iscontained
FROM Relatesto GROUP BY Relatesto.contains, Relatesto.iscontained;
Thanks

Sub relater()
'
' Create a a file of conjunctive conditions on adjacent keywords
(conjunctive
' meaning found in the same document) The following rules are
created --
' 1. auto related - keyword -> keyword (itself)
' 2. forward - prime ref -> keyword
' 3. backward - keyword -> prime ref
' 4. secondary - if keyword1 -> keyword2 and keyword2 ->
keyword3 then
' keyword1 -> keyword3
'
Dim dbs As Database
Dim rstTags As Recordset, strquery As String
Dim rstRelatesto As Recordset, rstURelatesto As Recordset
Dim process1 As Integer, process2 As Integer
On Error GoTo Err_passTags
strquery = "SELECT * FROM tags WHERE tagType=1"
Set dbs = CurrentDb
Set rstTags = dbs.OpenRecordset(strquery, dbOpenDynaset)
MsgBox ("opened rstTags")
strquery = "SELECT * from Relatesto"
Set rstRelatesto = dbs.OpenRecordset(strquery, dbOpenDynaset)
MsgBox ("opened rstRelatesto")
'GoTo fubar
process1 = 0
rstTags.MoveFirst
While Not rstTags.EOF
rstTags.MoveNext
'
rstRelatesto.AddNew
rstRelatesto![contains] = rstTags![primeRef]
rstRelatesto![iscontained] = rstTags![primeRef]
rstRelatesto.Update
'
process1 = process1 + 1
Wend
fubar:
strquery = "SELECT * from UniqueRelatesto"
Set rstURelatesto = dbs.OpenRecordset(strquery, dbOpenSnapshot)
MsgBox ("opened rstURelatesto")
process2 = 0
rstURelatesto.MoveFirst
While Not rstURelatesto.EOF
rstURelatesto.MoveNext
process2 = process2 + 1
Wend
Exit_passTags:
rstRelatesto.Close
rstTags.Close
rstURelatesto.Close
MsgBox ("Created " & process1 & " primary records and " & process2
& " secondary records")
Exit Sub
Err_passTags:
MsgBox Err.Description
Exit Sub
End Sub

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
After you're done adding records to rstRelatesto, I would first try
closing it before setting rstURelatesto (Q?). Your conflict might only
be due to waiting too long to close the dynaset. The records you add
using .AddNew and .Update will be written to disk immediately. In a
multiuser environment the added records will be available to others
when they Requery their recordset or use OpenRecordset.

James A. Fortune

Nov 13 '05 #2

P: n/a
I re-tried this alternative with different but still puzzling results.
The routine still produces an error and does not open the query
(rstURelatesto), however after taking out the error re-direction, I
find the error (3021-no current record) is at the line containing ---"
rstRelatesto![contains]=" i.e. a line that had been previously executed
(because there is output). Steps seemed to be performed asynchronously
--- what can I do to force synchronization without exiting the macro?
Thanks

Nov 13 '05 #3

P: n/a
I don't use EOF when stepping through recordsets often so I didn't have
a frame of reference for checking the EOF logic. I noticed that the
'While Not rstTags.EOF' would allow a recordset that is almost at an
EOF to reach EOF when the MoveNext is executed. Perhaps put an 'If Not
rstTags.EOF Then' around the .AddNew, .Update lines so that
rstTags![primeRef] will be defined.

James A. Fortune

Nov 13 '05 #4

P: n/a
Bri
Greetings,

I can't see how your symptoms could be occuring with your code unless
the data is in linked ODBC tables and there is a refresh delay.

However, I do see several places where you could make this process a lot
more efficient, which might remove the delay in refresh.
strquery = "SELECT * FROM tags WHERE tagType=1"
Set dbs = CurrentDb
Set rstTags = dbs.OpenRecordset(strquery, dbOpenDynaset)
If, instead of using the dbOpenDynaset Type, you used the
dbOpenForwardOnly Type (or at least the dbOpenSnapshot Type) this record
set would open faster. Since you are not editing these records and you
are moving forward through them this is the most efficient Cursor.
strquery = "SELECT * from Relatesto"
Set rstRelatesto = dbs.OpenRecordset(strquery, dbOpenDynaset)
If you used the dbAppendOnly Option, this would open a lot faster as it
would not load the existing records into the recordset. Since you do not
have a WHERE clause, it is currently reading EVERY record into the
recordset before you begin adding your new ones.
strquery = "SELECT * from UniqueRelatesto"
Set rstURelatesto = dbs.OpenRecordset(strquery, dbOpenSnapshot)
MsgBox ("opened rstURelatesto")
process2 = 0
rstURelatesto.MoveFirst
While Not rstURelatesto.EOF
rstURelatesto.MoveNext
process2 = process2 + 1
Wend
This is just aquiring a Count. It would be much faster if you used this
code:

strquery = "SELECT Count(*) AS Process2 FROM UniqueRelatesto"
Set rstURelatesto = dbs.OpenRecordset(strquery, dbOpenSnapshot)
process2 = rstURelatesto!process2
Exit_passTags:
rstRelatesto.Close
rstTags.Close
rstURelatesto.Close


You should also set these Recordsets to Nothing, eg
Set rstRelatesto = Nothing
Set rstTags = Nothing
Set rstURelatesto = Nothing

Try these changes and see if it works better for you.

--
Bri

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.