How to debug.print a query result from the query design in VBA /Access

1
votes

I want to execute a predefined query from access via VBA and print it to the debug output. The name of the query design is in a variable named report.

I was expecting that it would work with:

debug.print db.Execute report

But everytime vba autocorrects it to:

debug.print db.Execute; report

If I understand it correctly, the ; stands for a new line and makes therefore no sense in my case. But in both cases, with and without the new line I get an error. I assume that the simple problem is, that this is not the right syntax.

I could find a lot of information about how to debug print a query that is created as a string in VBA, but I can't find any hints how to output a query that is predefined in Access via a query design.

2
vbams-access

2 Answers

3
votes

Try either:

'// You need the parentheses because the result has to be evaluated before it can be passed to the .Print() method
Debug.Print db.Execute(result)

or

Dim myResult As String
myResult = db.Execute(result)

Debug.Print myResult

In VBA you can pass arguments to a procedure/method without using parentheses, as long as the result isn't being being assigned to anything.

'// Not assigning anything
MyExampleFunction arg1, arg2, arg3

'// assigning the result, which needs to be evaluated before it can be passed to a variable.
MyResult = MyExampleFunction(arg1, arg2, arg3)

In the same way, when you call Debug.Print it assumes that db.Execute and result are actually separate arguments (it has no way of knowing that you want result to be passed to db.Execute first because there's nothing in your syntax to state that). So you have to use parentheses to let it know.

0
votes

It seems as the problem was that it is only possible to call updates with db.Execute and not queries. There is no good solution to print a whole table from a query with debug.print but using a RecordSet as seen in the following code is a possible way.

Dim rs As DAO.RecordSet
Set rs = db.OpenRecordset(Bericht)

Do Until rs.EOF = True
    Debug.Print rs("Matrikelnummer"), rs("Bewertung"), rs("Termin (Datum)"), rs("Maximale Bewertung"), rs("Bestehensgrenze"), rs("Versuch"), rs("Äquivalenzleistung")
    rs.MoveNext
Loop

rs.Close
Set rs = Nothing