Query error "type mis-match"

[SusanV]

I have a bit of code to populate a query which I would then like to export
to excel. I'm stuck on one point though - i get a type mis-match error.
Using debug.print and copying the SQL into a new query the query runs
perfectly - but via VBA I get the error. Can anyone see what is wrong with
this code?

'''''''''''''''''''''''''''''''''
'code start
'''''''''''''''''''''''''''''''''
Dim db As DAO.Database
Dim Rs As Recordset
Dim sql As String
Dim tbl As String

tbl = Me.txttable
sql = "TRANSFORM Last(" & tbl & ".Freq) " _
& "AS LastOfFreq SELECT " & tbl & ".Equipment, " & tbl & ".OpStat "
_
& "FROM " & tbl & " GROUP BY " & tbl & ".Equipment, " & tbl &
".OpStat " _
& "ORDER BY " & tbl & ".OpStat PIVOT " & tbl & ".MCode;"
Debug.Print sql
Set db = CurrentDb()

Set Rs = db.OpenRecordset("TRANSFORM Last(" & tbl & ".Freq) " _
& "AS LastOfFreq SELECT " & tbl & ".Equipment, " & tbl & ".OpStat "
_
& "FROM " & tbl & " GROUP BY " & tbl & ".Equipment, " & tbl &
".OpStat " _
& "ORDER BY " & tbl & ".OpStat PIVOT " & tbl & ".MCode;")
'''''''''''''''''''''''''''''''''
'Code end
'''''''''''''''''''''''''''''''''

 

[Douglas J. Steele]

If you're able to use the SQL that's printed out, what happens when you use
that exact SQL in your code? In other words,

Set Rs = db.OpenRecordset(sql)

 

[SusanV]

Thanks Doug for replying,

the error is type mis-match.

I've gotten around it for this project by dumping the records into a temp
table and then using a stored query, but would welcome a solution for future
application.

I'm thinking it's because of the type of query perhaps? (transform, not
select, update, insert, or delete)


Susan

 

[Douglas J. Steele]

It shouldn't matter.

What's the actual SQL you're using?

 

[SusanV]

Doug,

Again, thanks for your time on this.

The actual line of code which calls the query is:

Set Rs = db.OpenRecordset("TRANSFORM Last(" & tbl & ".Freq) " _
& "AS LastOfFreq SELECT " & tbl & ".Equipment, " & tbl & ".OpStat " _
& "FROM " & tbl & " GROUP BY " & tbl & ".Equipment, " & tbl & ".OpStat " _
& "ORDER BY " & tbl & ".OpStat PIVOT " & tbl & ".MCode;")

tbl is a string populated by user input. I also have a string (sql) defined
in order to use the debug.print. The sql = is copied/pasted directly from
the set rs.

With the variable populated, and the linebreaks removed, it becomes:
"TRANSFORM Last(tblTemp.Freq) AS LastOfFreq SELECT tblTemp.Equipment,
tblTemp.OpStat FROM tblTemp GROUP BY tblTemp.Equipment, tblTemp.OpStat ORDER
BY tblTemp.OpStat PIVOT tblTemp.MCode;"

This is what is in the debug.print box, and it runs perfectly happily when
pasted into the SQL view of a new query. I've tried removing line breaks
etc, and nothing seems to make a difference. Different table names work just
fine in a query as well, but not via VBA.

Access flavor is 2000 if that makes a difference.

Thanks again,

Susan

 

[SusanV]

Oh, and it was originally setup as
Set rs = db.openrecordset (sql) I dumped in the actual sql as yet another
desperate attempt to get it to run.
;-)

 

[Douglas J. Steele]

Yeah, the fact that it's Access 2000 does make a difference!

You're using DAO code, which means that you've added a reference to DAO (if
you hadn't, you'd get an error on Dim db As DAO.Database statement). I'm
betting that when you added the reference to DAO, you didn't remove the
reference that Access already had to ADO.

When you have both references, you'll find that you'll need to
"disambiguate" certain declarations, because objects with the same names
exist in the 2 models. Recordset happens to be one of those objects in both
models. Since the ADO reference is higher up in the list, your declaration
of Dim Rs As Recordset will result in Rs being an ADO recordset, not a DAO
recordset. Hence the "type mismatch" error.

If you're uneasy about deleting the ADO reference, simply change your
declaration to Dim Rs As DAO.Recordset (to guarantee an ADO recordset, you'd
use Dim Rs As ADODB.Recordset)

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset

 

[Susan V]

Ah, ok, I always declare dbs and catalogs as dao or ado or adox, but not
recordsets - doh! I'll give that a try.

Thanks Doug!

 

[SusanV]

Doh! I totally didn't catch that mistake on my part - I have most of my
stuff in ADO or ADOX so I can't remove the reference - and know that I have
to declare explicitly. That silly little omission is all that was wrong.


Thanks again Doug - nice catch!

Susan