MS Access: The utility for issuing request data to the debug console

Working with Acces every day, several times a day I encounter the problem that somewhere in the VBA code I need to see what one or another SQL query will return to me. To do this, I have to switch from the VBA-editor to the main part of the program, and either create a temporary query (which is then left-willy-nilly), or quickly change the SQL in some temporary query. Since a lot of time is always spent on this, I decided to write a simple function that returns the results of the query to the debug console.

This utility is called q (so that it is convenient to call, and a hint of a Query query), it takes the query itself as parameters (either entirely as SQL, or just the name of the query or table, as long as it can be opened via CurrentDB.OpenRecordset ), the maximum width of the field when issuing (if the field is larger - it is cut off, the default is 10 characters) and the maximum number of records (the default is 100 records), and returns to the console the contents of the results of this query in text form, it looks like this:

 ? q ("qryStatBestVertriebler")
 Running qryStatBestVertriebler ...
 Query returned 9 entries.
 -------------------------------------------------- -----------------------------------
 |  Nr |  VMB |  FName |  Einkauf |  Verkauf |  Gewinn |  Num | 
 -------------------------------------------------- -----------------------------------
 |  1 |  757 |  Sönke Doba |  492661.52 |  718774.8 |  226113.28 |  231 | 
 |  2 |  877 |  Johannes W |  10464.99 |  59677.25 |  49212.26 |  39 | 
 |  3 |  1098 |  Marco Müll |  8233.18 |  12244.77 |  4011.59 |  36 | 
 |  4 |  5527 |  Torben Jas |  12974.64 |  24642,42 |  11667.78 |  16 | 
 |  5 |  6214 |  Thiemo Wol |  5932.17 |  12175.97 |  6243.8 |  23 | 
 |  6 |  7833 |  Florian Mi |  207384.93 |  293553.82 |  86168.8900 |  254 | 
 |  7 |  8310 |  Daniel Sch |  3525.56 |  4338.92 |  813.36 |  4 | 
 |  8 |  8917 |  Daniela He |  187881.29 |  638726.06 |  450844.77 |  559 | 
 |  9 |  9330 |  Konrad Cyw |  94142.67 |  133056,71 |  38914.04 |  139 | 
 -------------------------------------------------- -----------------------------------
 Falsch

And here is its code:

1. Public Function q ( Optional strSQL As String = "" , Optional intWidth As Integer = 10, Optional intMax As Integer = 100) As Boolean
3. Dim tmpRCDSet As Recordset, tmpFeld As Field, tmpString As String , I As Integer , intTemplen As Integer
4. Dim intNr As Integer
6. On Error GoTo Err_SQL
7. Debug.Print "Running„ & strSQL & "..."
8. Set tmpRCDSet = CurrentDb.OpenRecordset (strSQL)
9. tmpRCDSet.MoveLast
10. Debug.Print "Query returned„ & tmpRCDSet.RecordCount & “entries."
11. tmpRCDSet.MoveFirst
12. tmpString = "| Nr |"
13. For Each tmpFeld In tmpRCDSet.Fields
14. tmpString = tmpString & padleft (tmpFeld.Name, intWidth) & "|"
15. Next
17. Debug.Print String (Len (tmpString) - 1, "-" )
18. Debug.Print tmpString
19. Debug.Print String (Len (tmpString) - 1, "-" )
21. intNr = 1
22. While ( Not (tmpRCDSet.EOF)) And (intNr <= intMax)
23. tmpString = "|" & padleft (Str (intNr), 4) & "|"
24. For Each tmpFeld In tmpRCDSet.Fields
25. tmpString = tmpString & padleft (Nz (tmpFeld.Value, "" ), intWidth) & "|"
26. Next
27. Debug.Print tmpString
28. intNr = intNr + 1
29. tmpRCDSet.MoveNext
30. Wend
31. Debug.Print String (Len (tmpString) - 1, "-" )
33. Exit function
36. Err_SQL:
37. Debug.Print Err.Number & "" & Err.Description
38. Debug.Print "Bad SQL string"
41. End function
43. Function padleft (strLineIn As String , intWidth As Integer ) As String
45. If Len (strLineIn) = intWidth Then
46. padleft = strLineIn
47. ElseIf Len (strLineIn)> intWidth Then
48. padleft = Mid (strLineIn, 1, intWidth)
49. Else
50. padleft = String (intWidth - Len (strLineIn), "" ) & strLineIn
51. End if
53. End function

* This source code was highlighted with Source Code Highlighter .