473,326 Members | 2,588 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,326 software developers and data experts.

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

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
4 2184
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: John | last post by:
Just a general question... I'm currently using a combobox that when updated, opens a form with its recordset based on a query using the combo box value as the criteria. I'm I correct in...
3
by: Ryan Ritten | last post by:
Hey, I just started working at this new company and they gave me a simple database project to start with. yet I am starting to look like an idiot cause I can't even seem to connect to the MS...
0
by: Freebase | last post by:
Something changed recently on our W2K SP4 machine when we installed INTERSOLV ODBC software... the following script just opens a connection to an MS-Access DB, gets a record then tears down the...
3
by: Richard Hollenbeck | last post by:
I have the following query in my form's code: Private Function Get_Data(fieldNum As Integer) Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset strSQL = "SELECT & "", "" & ...
15
by: Dave | last post by:
Has anyone encountered the error message "Can not open any more databases" and what did you do to solve it? Thanks, Dave
7
by: BillCo | last post by:
taking the following data: a w a n b r b y b p c a getting the following result from a query:
2
by: Robert | last post by:
I am trying to give the user dynamic search capabilities to select almost any record in the database from criteria they select. Everything seems to work except when I open the display form to...
4
by: zion4ever | last post by:
Hello good people, Please bear with me as this is my first post and I am relative new to ASP. I do have VB6 experience. I have a form which enables users within our company to do an intranet...
3
atksamy
by: atksamy | last post by:
hi, Is it possibel to get a report of the records that are updated using a update query. I mean without using recordset. suppose sqltext = update table employees set bonus = 0 where salary >...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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.