Solved

SQL and deleted recordset makes error ....

Posted on 2002-07-13
18
205 Views
Last Modified: 2013-11-25
Hi!

I am using following code:

Dim Datenbank As Database
Dim DatenRecordSet As Recordset
Dim SQLDef As QueryDef
Dim Temp$

Set Datenbank4 = OpenDatabase(App.Path & "\data.mdb")
Temp$ = "SELECT * FROM [Test] "
Set SQLDef = PDatenbank.CreateQueryDef("", Temp$)
Set DatenRecordSet = SQLDef.OpenRecordset()


'*****

if i use it twice at the same time and one of the 2 delete one recordset. How can i know if a recordset is deleted? Is there a easy if then or can i only look for an error (not so good)?

Falke
0
Comment
Question by:Falke
  • 7
  • 5
  • 4
  • +2
18 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7151302
Please maintain your open questions:
terminate a programm and send vbreturn? Date: 09/23/2001 03:14AM PST  
http://www.experts-exchange.com/visualbasic/Q_20186489.html

The following should be deleted as no solutions have been contributed:
Resize Taskbar and set the desktop font smaller Date: 04/14/2002 02:27AM PST
http://www.experts-exchange.com/visualbasic/Q_20288984.html
DHTMLeditor and helpfile available? Date: 03/20/2002 01:33AM PST
http://www.experts-exchange.com/visualbasic/Q_20279191.html
Embedding Internet Explorer Component into a frame -> how can i retrieve links? Date: 03/11/2002 11:53PM PST
http://www.experts-exchange.com/visualbasic/Q_20275703.html
mediaplayer and disable hardware-acceleration? Date: 10/26/2001 01:51PM PST
http://www.experts-exchange.com/visualbasic/Q_20218046.html

Thanks,
Anthony
0
 

Author Comment

by:Falke
ID: 7151569
@acperkins
I like your Answers! But thats no answer to my question! To your, how can i delete a question?

Falke
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7151592
Post a message to that effect in Community Support (http://www.experts-exchange.com/commspt/).

In general, you will find Experts more receptive to your questions when you take the time to maintain them.

Anthony
0
 

Author Comment

by:Falke
ID: 7151599
ok done
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7152196
This should be:

Set Datenbank4 = OpenDatabase(App.Path & "\data.mdb")
Temp$ = "SELECT * FROM [Test] "
Set DatenRecordSet = Datenbank4.OpenRecordset(Temp$)
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 7152203
> How can i know if a recordset is deleted?

Are you mean a recordset object (example DAO.recordset) or a Query object in database? Do you want to check the existence of a Query in database?

regards
0
 

Author Comment

by:Falke
ID: 7152621
@ aikimark
you are right .. but that's not the problem
and to correct all it must be:
Set Datenbank = OpenDatabase(App.Path & "\data.mdb")


@ryancys
DAO.Recordset (datenrecordset is a dao.recordset (Dao 3.6)

Falke
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7152653
Falke ,

My example code does not use a querydef object.  The recordset object is created directly from the database object.  I appologize for the typo.  I had copied code from your question entry.  The Datenbank was spelled Datenbank4 on one of the lines.
0
 

Author Comment

by:Falke
ID: 7152667
thanks for the improve ;)

but my problem is, if i use 2 routines at the same time (same SQL) and i delete in one a recordset, in the other i get an error. How can i ask if a recordset has been deleted? I would like to have something like if datenrecordset.isdeleted = True or so.

Falke
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 45

Expert Comment

by:aikimark
ID: 7152691
do the recordsets have the same name?  If so, they can't be simultaneously addressed by some code.

If DatenRecordSet Is Nothing Then

End If
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7152713
You cannot delete a recordset.  You can close it and set it to Nothing (In which case you can can conpare to Nothing).  But I do not believe this is what you mean.  I think you are opening two recordsets with the same SQL and one of them is deleting a record.  Your question is then how do you "alert" the other recordset that the record is deleted.

The short answer is you cannot, without requerying the database.  You may want to do one of the following:
1. Check fr this possibility with Error Handling
2. Re-query.
3. Re-think your strategy.

Anthony
0
 

Author Comment

by:Falke
ID: 7152973
@acperkins
you are right

i am using it for a login procedure. I give at the first time a "rang order" to the customer, on which queue rang he is. If he terminate before he enter, the datarecordset SQL has been deleted. Every time one has beend entered the other will be informed about the new "rang in the queue", that's the idea.

@acperkins
i do it with an error Handling but i would like to know, if there is a "better way" to know, if this recordset has been deleted.

Falke
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 7153139
>>i do it with an error Handling but i would like to know, if there is a "better way" to know, if this recordset has been deleted.<<

I think that may be your best bet.

Anthony
0
 

Author Comment

by:Falke
ID: 7153765
your best bet?
0
 
LVL 4

Expert Comment

by:TigerZhao
ID: 7153854
why not use SAME recordset object between tow form(or somewhere you operate data)?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 7154099
Falke,

After re-reading your question and all the comments, it appears that you ARE NOT deleting the recordset.  Rather, you are rebuilding the recordset when you execute the VB code the second time.

Since you are using the same SQL to build the recordset, this should not cause a problem, other than flicker on bound list, combobox and grid controls.

===========================================
We (both you and us experts) need to step back from the code a moment and understand what you WANT your code to do.  In other words, we experts need to understand the context in which this code is run and why you are running it twice.
0
 

Author Comment

by:Falke
ID: 7224444
@aikimark
you are right but i whill show you a partial of my code :

...
Temp2$ = "SELECT * FROM [Login]"
Set SQLDef2 = PDatenbank.CreateQueryDef("", Temp2$)
Set DatenRecordSet2 = SQLDef2.OpenRecordset()
   
Dim bOKNeu As Boolean
While Not DatenRecordSet2.EOF
   Temp$ = "SELECT * FROM [Login]
   Set SQLDef = PDatenbank.CreateQueryDef("", Temp$)
   Set DatenRecordSet = SQLDef.OpenRecordset()
       
        while not datenrecrdset.eof
          'gives the user the actuall queue number
          'if user has gone before entering into theprogramm datenrecordset must be deleted but
            DatenRecordSet.MoveNext
            Wend
        End If

 .....

Falke
0
 
LVL 45

Accepted Solution

by:
aikimark earned 50 total points
ID: 7224674
Falke,

I have two recommendations for you:
1. Stop using the temporary querydefs to open your recordsets.  Instead, use the Openrecordset method of the database object. (see the 07/14/2002 04:59AM PST example)

2. If you want to see the position of the user's row in the LOGIN table, then position to their row and display that row's AbsolutePosition property.  If you want to display the relative position then display AbsolutPosition/RecordCount.   Please be aware that the Absoluteposition property is zero based.  Also be aware that you might have to MoveLast and MoveFirst in order to set the RecordCount property to accurately reflect the number of rows in the recordset.  You don't need a second recordset.

Hint: A .FindFirst method for the user id is faster than a .MoveNext inside a loop.

Note: The While...Wend construct is going away.  In VB.Net, this isn't supported.  There is a While...End While construct.  I don't know if it is still true, but the While...Wend used to be less efficient than the Do...Loop construct.  Two other advantages of the Do...Loop are the ability to use UNTIL or WHILE and the ability to check the condition after the first (and subsequent) iterations.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now