Editing and Debugging MS ACCESS Queries RRS feed

  • Question

  • Hi,

    I'm faced with 100s and queries calling each other and tables. When we face an issue, it's very hard to find causation. For example, when a Parameter dialogue comes up, since a column doesn't exist or mistyped, it is very difficult to find the cause,

    I'm trying to take any of the below approach to make our life easier,

    1) If there is a debugger to use with the MS ACCESS, use the Debugger.

    2) If there is a way to migrate the MS Access to a different product that includes debugging and error checking capability, migrate over.

    We do have Power BI available to use but I'm not sure if Power BI understands MS Access Queries?

    Thanks for your time.

    Monday, October 28, 2019 2:42 PM

All replies

  • If you get a parameter prompt, you can run code like this:

    public sub DumpParameters(byref qd as dao.querydef)

    dim p as dao.parameter

    for each p in qd.parameters

      debug.print p.name, Nz(p.Value, "[NULL]")

    next p

    From the Immediate window, invoke this using:
    DumpParameters currentdb.querydefs("myQuery")

    -Tom. Microsoft Access MVP

    Monday, October 28, 2019 3:20 PM
  • interesting code from TvS - am going to have to check it out...

    I troubleshoot pragmatically; one does have to think in terms of the stack; where the presentation layer is on top (form/report) and the record source is below.

    When I get a parameter prompt in the presentation layer - I manually open the record source (query) and see if that is itself generating a parameter prompt.  It's a simple divide a conquer approach.

    One thing often missed when in the presentation layer - is that any call to the missing field needs to be dealt with, not just the field itself i.e. sorting grouping or any code in an event...

    Monday, October 28, 2019 3:35 PM
  • You might try FMSInc.com Total Access Analyzer. It does a good job of getting to the little details of a database. 

    Bill Mosca

    Monday, October 28, 2019 4:16 PM
  • You could also dump all the SQL of all the Queries to a table and just take a good look where you might have issues

    Public Function DumpSQL()

    Dim qdf As QueryDef

    For Each qdf In CurrentDb.QueryDefs

    Debug.Print qdf.SQL '<-- Instead of debug.print you could have save it to a table


    End Function

    Monday, October 28, 2019 7:21 PM
  • I have been confronted several times to that situation.

    1) One stupid but useful trick is to use the built-in database documentor to list SQL of all queries, and export that report to a PDF. You then have a searchable list of all queries SQL.

    2) I also wrote a report that I import into the database, that list all queries by type and displays their input tables/queries and their target (for action queries). That allows me to quickly trace dependencies. Again I send that to a PDF to have a fast access to it.

    3) You can also use the built in Object Dependencies tool. It's far from perfect but useful anyway. Too bad it ignores UNION queries and it misunderstand ALIASes.

    Wednesday, October 30, 2019 9:41 AM