Data Type Conversion Error when trying to Set Rs = Qdf.OpenRecordset()

[RunTime91]

Howdy...



I am currently using the following code to try and create and run a Qdf but I'm getting a 3421: Data Type Conversion at the: Set Rs = Qdf.OpenRecordset("StrQry1")

Dim Qdf As DAO.QueryDef
Dim Dbs As DAO.Database
Set Dbs = CurrentDb
Dim Rs As DAO.RecordsetDbs.QueryDefs.Delete "StrQry1"
StrQry1 = "SELECT * From tri.TktEmailTmp"
Set Qdf = Dbs.CreateQueryDef("StrQry1")
Qdf.Connect = strConnection
Qdf.SQL = StrQry1
Qdf.ReturnsRecords = False

Set Rs = Qdf.OpenRecordset("StrQry1")   <----- Error is occurring on this line

    If Not (Rs.BOF And Rs.EOF) Then
        Do While Not Rs.EOF
            lCnt = lCnt + 1
            ReDim Preserve EmailBdy(1 To lCnt)
              TblRow(1) = Rs("EmpID")
                TblRow(2) = Rs("EmpName")
                TblRow(3) = Rs("TempAgency")
              strBodyPass = strBodyPass & "<tr><td>" & Join(TblRow, "</td><td>") & "</td></tr>"
            Rs.MoveNext
        Loop
    End If

I've researched and tried as many things as I can find and think of

[Minty]

You don't need the quotes - you have set StrQry1 as a string variable , so use the variable.

Set Rs = Qdf.OpenRecordset(StrQry1)

At the moment you are trying to open a recordset using a sql statement of "StrQry1"

[RunTime91]

Hey Minty ~

Thanks for chiming in on this - I removed the quotes - unfortunately, same error...

I also removed the quotes surrounding StrQry in the Set Qdf = Dbs.CreateQueryDef(StrQry1) but that only produces a different error.

[RunTime91]

I changed:

Set Rs = Qdf.OpenRecordset(StrQry1)

To:

Set Rs = Qdf.OpenRecordset

And the code flew right through and produced the desired results

So it would 'appear' removing the StrQry1 reference altogether was the answer?

Does this make sense that removing StrQry1 would be the solution?

Thanks

[Minty]

Doh

Yes it makes perfect sense. You set the query def to your SQL string earlier in the code, so of course it works.
I was confusing myself with the methods used, and sort of blindsided myself by ignoring the Qdf. prefix.

[June7]

The string variable is not need at all, especially with such a short query statement.

Set Qdf = Dbs.CreateQueryDef("SELECT * FROM tri.TktEmailTmp")

If you were building a long query statement with multi-line concatenation, would probably want a variable.

I have never used QueryDefs just to establish a recordset object. Simplified:

Dim Dbs As DAO.Database
Dim Rs As DAO.Recordset
Set Dbs = CurrentDb
Set Rs = Dbs.OpenRecordset("SELECT * FROM tri.TktEmailTmp")

Whether or not the Dbs object variable is needed can be another discussion - some will argue strongly for it:

Set Rs = CurrentDb.OpenRecordset("SELECT * FROM tri.TktEmailTmp")

Line in posted code: Dim Rs As DAO.RecordsetDbs.QueryDefs.Delete "StrQry1", should be split to 2 lines. Have to be careful, sometimes forum post doesn't get a line feed when pasting code. Why are you trying to delete a QueryDef?

Also wonder what is advantage of using an array?

[RunTime91]

Hey June...

I'm new to QDefs as well but my understanding is, it is good to delete your QDef's to prevent errors (I also use error traps) and to free up memory.
As for the Array - I'm not crazy about array's but the code works so I'm kinda of leaving it alone for now - if you have a suggestion - I'm listening
And finally, declaring the Dbs object - yeah, I think that is just habit...

Thanks June

[June7]

I am confused by your original code. You ReDim array EmailBody, yet the array filled is TblRow. Code does not show either declared. I have used QueryDefs only when I wanted to create or modify an Access query object, never just to open a recordset. What is your backend - what is 'tri'?)

Join() is new to me but I have used Array() function:

Dim Dbs As DAO.Database
Dim Rs As DAO.Recordset
Set Dbs = CurrentDb
Set Rs = Dbs.OpenRecordset("SELECT * FROM tri.TktEmailTmp")

    If Not (Rs.BOF And Rs.EOF) Then
        Do While Not Rs.EOF
              strBodyPass = strBodyPass & "<tr><td>" & Join(Array(Rs!EmpID, Rs!EmpName, Rs!TempAgency), "</td><td>") & "</td></tr>"
              Rs.MoveNext
        Loop
    End If