[RESOLVED] Run query within a query using code [RESOLVED]

[greaseman]

I'm having a bit of a headache with trying to run a delete query using the results of a select query, in VB code. My first query runs OK, but when I try running the second query, I keep getting a message, "Object required."

I've enclosed my code that is causing the problem..... if y'all could take a look at it and tell me the erors of my ways, I would be very happy.

Dim rsMstrPN As New ADODB.Recordset
    Dim rsDuplicates As New ADODB.Recordset
    Dim strSQL As String
    Dim strErrMsg As String
    Dim lngRecCount As Long
    Dim lngTotalRec As Long
    Dim strPartId As String
 
'==========================================================================
' Variable Initialization Section
'==========================================================================
    DeletePNdata = False
'==========================================================================
' Code Section
'==========================================================================
 
    strSQL = ""
    strSQL = strSQL & "select DuplicatePartsBad.PNID from DuplicatePartsBad "
    strSQL = strSQL & "Order by DuplicatePartsBad.PNID"
    rsDuplicates.Open strSQL, conJetMasterDb, adOpenStatic,    adLockReadOnly, adCmdText
    If Not (rsDuplicates.EOF = True And rsDuplicates.BOF = True) Then
        rsDuplicates.MoveFirst
        Do While Not rsDuplicates.EOF
             strPartId = rsDuplicates!PNID
             strSQL = ""
             strSQL = strSQL & "Delete * from MasterPN where MasterPN.PNId    = " & strPartId & "; "
             Debug.Print strSQL
             rsMasterPN.Open strSQL, conJetMasterDb, adOpenStatic, adLockOptimistic, adCmdText
        Loop
    End If
    
    rsMstrPN.Close
    rsDestination.Close

Thaks to all of you who help out this myopic programmer!!

[RhinoBull]

Use connection object to delete records:

Do While Not rsDuplicates.EOF
    strPartId = rsDuplicates!PNID
    strSQL = "Delete * from MasterPN where PNId = " & strPartId
    [B]conJetMasterDb.Execute strSQL[/B]
    rsDuplicates.MoveNext
Loop

[si_the_geek]

Action queries (Delete/Insert/Update/...) do not return recordsets, so should be run like this instead:

conJetMasterDb.Execute strSQL

You didn't mention where the error occured, which would blatantly be useful for us to know. I can see where it is, and it is due to a typo in the variable name.. but as explained above you dont need the variable anyway.

[bushmobile]

Perhasp try this:

strSQL = ""
    strSQL = strSQL & "select DuplicatePartsBad.PNID from DuplicatePartsBad "
    strSQL = strSQL & "Order by DuplicatePartsBad.PNID"
    rsDuplicates.Open strSQL, conJetMasterDb, adOpenStatic, adLockReadOnly, adCmdText
    If Not (rsDuplicates.EOF = True And rsDuplicates.BOF = True) Then
        rsDuplicates.MoveFirst
        Do While Not rsDuplicates.EOF
             strPartId = rsDuplicates!PNID
             strSQL = "Delete * from MasterPN where MasterPN.PNId = " & strPartId & "; "
             Debug.Print strSQL
             conJetMasterDb.Execute strSQL
             rsDuplicates.MoveNext
        Loop
    End If
    rsDuplicates.Close

If you're not returning a recordset then there is no need to use rs.Open, just Conn.Execute sql.

[RhinoBull]

Action queries (Delete/Insert/Update/...) do not return recordsets, so should be run like this instead: ...

Not exactly - you can still use recordset object to execute any sql statement.
It's not conventional way but lots of people use it...

[si_the_geek]

Well yes you can use a recordset object to run them, but it is slower and wastes memory.. hence "should be".

[RhinoBull]

Well yes you can use a recordset object to run them, but it is slower and wastes memory.. hence "should be".

Please SI, don't even start that nonsense - this technic is very widely used and performance depends on many many many criterias.
Personally I prefer to use connection object but it doesn't mean it's better and/or someone should do the same.

[Frans C]

Both is possible.
The error is caused by a typo
The recordset is declared as rsMstrPN , but opened as rsMasterPN.

Dim rsMstrPN As New ADODB.Recordset
'.....
'.....
rsMasterPN.Open strSQL, conJetMasterDb, adOpenStatic, adLockOptimistic, adCmdText

[si_the_geek]

Yep, as I mentioned above



It certainly isn't nonsense RhinoBull.

It is slower, as the API's which ADO uses for opening a recordset interrogate the DBMS in a different way, which takes longer than the simpler method used by Execute. It may not be a big deal, but it does have an impact. For larger scale systems this can cause slowdown, but admittedly for most programs/databases the difference wont be noticable.

It wastes memory, as the recordset object uses a comparatively large amount of resources - which aren't needed at all.


Just because something is widely used does not mean it is the right way to do things. Using a recordset object works, but is not the optimal method.

[RhinoBull]

Oh, you have a wrong idea, man... Sql is evaluated first and if it is supposed to return something then recodset will be initialzed and populated, otherwise recordset object "is left along"... so stop here and agree that we disagree.

[si_the_geek]

It always make me laugh when you get like this. You have a great knowledge of VB, but when it gets into specifics like this your lack of in-depth knowledge shows itself - as does your will to disparage others.

You are right that the SQL is evaluated first, but are you aware of the two contrasting methods involved as part of each object? Are you aware of how the preparations are done, and how the returns of an SQL statement are evaluated by ADO?

If you don't want to have discussions about the issues and reasoning behind what people have said, then please don't start them.

[Frans C]

OK, now kiss and make up.

I dont think the overhead will be big, but in order to call the Open method of a recordset, the object has at least to be created.

[greaseman]

To all of you who replied...... thank you!! That certainly was a lot of interesting "discussion" (ahem), but the line of code (conJetMasterDb.Execute strSQL) suggested did the trick.

As far as the path the responses started following, it doesn't really matter, as long as what gets suggested does the trick.

Incidentally, thanks for the pickup on my typo..... however, even correcting the typo still did not work.... I had to put in the conJetMasterDb.Execute strSQL line of code to get things working.

I also had to put in a rsDuplicates.MoveNext line of code to keep my snippet from looping forever.

Thanks again for your good and fast responses! Love this forum!