Managing and Debugging SQL Queries in Ms Access

Need to debug Left Join in SQL

You need parentheses when having multiple joins (1 for each)

SQL multiple join statement

SELECT      Billings.[CustomerNumber], 
            Billings.[Customer Name], 
            Billings.[SOP Number], 
            Billings.[Item Number], 
            Sum (Billings.[Extended Price]) AS ['Revenue'], 
            Sum (Billings.QTY) AS ['Quantity']
FROM        (([Product Codes]         AS Codes
LEFT JOIN   Billings                
ON Codes.[Product Code] = Billings.[Item Number])
LEFT JOIN   [All Rep Assignments]   
ON Billings.CustomerNumber = [All Rep Assignments].[Account Number])
GROUP BY    Billings.[CustomerNumber], 
            Billings.[Customer Name], 
            Billings.[SOP Number], 
            Billings.[Item Number]

Running SQL Query In Access VBA

You have several issues.

One is that SQL server and Access SQL are not the same. Access SQL is a lot more limited, so just because an SQL query runs on the SQL server does not mean it will run in Access. To run SQL Server queries that are not Access SQL compatible you have to use a pass-through query.

The other issue is that Access table names and SQL server table names are no necessarily the same.

Now, assuming you have taken all that into account and your query is actually Access SQL compatible, you can run it like this:

Dim sql as String
sql = "Query part A"
sql = sql & "Query Part B"
... repeat as necessary
DoCmd.RunSQL sql

Debugging Access VBA Currentdb.Execute

As a start, provide valid string expressions for the values - not everything is text - and respect reserved words:

CurrentDb.Execute "insert into invoices (Stateid, companyid, PaymentYear, InvoiceDate, [Type], MethodofPayment, [date], amount) " & _
"Values('" & Me.StateID & "','cOOKIE'," & Me.PaymentYear & ", #" & Format(Me.InvoiceDate, "yyyy\/mm\/dd") & "#, " & _
"'" & Me.Type & "','" & Me.MethodofPayment & "',#" & Format(Me.Date, "yyyy\/mm\/dd") & "#," & Str(Me.Amount) & ");"

Split database: where are queries located and how to view SQL?

Since you are unable to open the Navigation Pane, you could create a new db file and import queries from the front end into the new db (see the Import section of the External Data ribbon). Without the linked tables, those queries will be non-functional, but that effort will allow you to identify which queries exist and you may examine their SQL if that is helpful.

You could even create links to the back end tables and import anything else you may need from the front end db.

Debugging a SQL Query

SQL Server Management Studio can debug stored procedures in SQL Server 2008. Open your stored procedure, and instead of hitting the "execute" button (the red exclamation mark) hit the "debug" button (the green "play" arrow).

http://www.mssqltips.com/tip.asp?tip=1695

Access 2016 Relationships

Whenever you create or change a query and it does not work as expected, there are several steps you can take to analyze the issue and find a solution. The two most frequently encountered issues are either 'selection criteria' or 'table joins'. The more complex the query, the more difficult it will be to spot the issue.

Join Issues:
IF you encounter an issue when you add a new table or join on an additional field, simplify the query by keeping only the new table and the table the join is on, then test. If necessary, insure your tables actually contain matching data! If you had also added selection criteria, then remove it.

Selection Criteria:
If your change/add results in no or incorrect results, simplify the query by getting rid of all other selections, then test to find cause. Remove other tables if necessary.

Complex Queries:
Sometimes you may have 20 or more joined tables with 20 or more selection criteria. Since it would be too time consuming to test each possible scenario, I like to delete half of the selection criteria, then test. If it still doesn't work, delete half of the remaining criteria and repeat the test until it works. If you delete half and it now works, you know the issue is with the item(s) you deleted. The same testing method applies to the joined tables. First delete any tables that have no dependent tables, and test that. Keep deleting tables / criteria until it works.

Some other debugging tips:

Managing and debugging SQL queries in MS Access

http://www.dataversity.net/debugging-complex-sql-queries/

MS Access running SQL Update Error 3464

Consider a parameterized SQL query with a conditional IIF to avoid any looping, quote enclosures, and string concatenation.

SQL (save as a saved query)

PARAMETERS NewRankParam LONG, PriorityLevelParam LONG, UserGroupParam TEXT;
UPDATE Task_List  t
SET [Task_Rank]= IIF(t.[Task_Rank] >= NewRankParam, t.[Task_Rank] + 1, t.[Task_Rank])
WHERE ((t.[Task_Priority] = PriorityLevelParam) AND (t.[Task_UserGroup] = UserGroupParam));

VBA

Function ChangeTaskRank(NewRank As Integer, PriorityLevel As Integer, UserGroup As String)

    Dim db As DAO.Database
    Dim qdef As QueryDef

    Set db = CurrentDb
    Set qdef = db.QueryDefs("mySavedQuery")

    qdef!NewRankParam = NewRank
    qdef!PriorityLevelParam = PriorityLevel
    qdef!UserGroupParam = UserGroup

    qdef.Execute dbFailOnError

    Set db = Nothing
    Set qdef = Nothing

End Function

---

Related Topics