I still don't get DAO

[twgonder]

I've got some code running to read records from a somewhat complicated SQL.

I created the SQL in a Query, and then moved it over to the VBA.


If I copy the ?wSQL result from VBA immediate pane back to the Query builder, I get the result I want.

I'm guessing I've got the syntax wrong for the fields in the debug.print line (where the RTE occurs).
Got any ideas?



All the training videos seem to use a simple SQL against a single table.
Can I do joined SQL to do what I want here, which is get the four fields from two tables?
This is the training video I watched: https://youtu.be/5SVOxZCCPWw?si=BjWSwJJHFRYYCt5v

[twgonder]

In case it's not clear, or you want to see the SQL from the VBA code working:



and the run

[June7]

Please provide code and SQL statements as text between CODE tags, not image. Also, error messages as text.

Debug.Print looks fine expect it is not working for me either. Interesting, never ran into this before. Don't know if can reference recordset field with table name prefix. Use an alias name in query and reference that. Alternative is to reference field by index. The output values will run together - concatenate space and/or punctuation between the fields.

Before trying to get RecordCount, do:

daRsR.MoveLast
daRsR.MoveFirst

Instead of checking for RecordCount, check for EOF and/or BOF.

http://allenbrowne.com/ser-29.html#R...thout_MoveLast

[Minty]

Use the field name only in the recordset expression, it won't recognise it with the table name.

@June - checking for Recordcount = 0 is 100% reliable for checking for no returned records, as you know it's not to return an actual record count.

[June7]

Thanks, Minty. I see that now. I have usually used EOF approach.

I thought the query was pulling duplicate field names because it is using a related table twice. I see now that it does not. So just removing the table prefix should work.

However, I think it may not be pulling correct fields from the clones. There should be duplication of field names - pulling same fields from both instances of related table.

[twgonder]

@Minty & June7, posts 3-5

Minty? "as you know it's not to return an actual record count." Why not? (never mind, I just read the Allen Browne post)

It's quite possible that another query could pull duplicate field names. That's why, I suppose, that the query builder uses the table names. So why would VBA have problems with the full name (including the table name)? That doesn't make a lot of sense.

In this case, using just the field names worked because there wasn't a duplicate. However, if...?

Thanks June7 for the Allen Browne link, I've got some clean up now going on based on points 1 & 2 in that blog.

[Minty]

@Minty & June7, posts 3-5

Minty? "as you know it's not to return an actual record count." Why not? (never mind, I just read the Allen Browne post)

It's quite possible that another query could pull duplicate field names. That's why, I suppose, that the query builder uses the table names. So why would VBA have problems with the full name (including the table name)? That doesn't make a lot of sense.

In this case, using just the field names worked because there wasn't a duplicate. However, if...?

It will need an field alias set - e.g. table1.amount as t1Amount, table2.Amount as t2Amount
If you select the same field name twice in the query designer, it automatically assigns Expr1,Expr2 etc as field names.

RecordCount - As you have read, it's a weird one that's a DAO speed of loading issue.
ADO recordsets don't suffer the same issue, due to how they retrieve the data.