none
SQL Statement not working in VBA for Access

    Question

  • Hello all,

    This query worked in GUI as a Select query but not in code as an Append...

    GUI:

    SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Contact.ContactTypeLookup, FacilityAddress.FacilityAddressTypeLookup, Machine.MachineID, Machine.InstallDate, MachineInspection.MachineInspectionID, MachineInspection.IDInspection, MachineInspection.InspectionDate, MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus
    FROM (((Facility INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID
    WHERE (((Contact.ContactTypeLookup) Like "*"+"Radiation Safety Officer (RSO)"+"*") AND ((FacilityAddress.FacilityAddressTypeLookup)="Mailing") AND ((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)="") AND ((MachineInspection.InspectionStatus) Like "Open")) OR (((Contact.ContactTypeLookup) Like "*"+"Radiation Safety Officer (RSO)"+"*") AND ((FacilityAddress.FacilityAddressTypeLookup)="Mailing") AND ((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)="") AND ((MachineInspection.InspectionStatus) Not Like "Closed")) OR (((Contact.ContactTypeLookup) Like "*"+"Radiation Safety Officer (RSO)"+"*") AND ((FacilityAddress.FacilityAddressTypeLookup)="Mailing") AND ((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)="") AND ((MachineInspection.InspectionStatus) Is Null)) OR (((Contact.ContactTypeLookup) Like "*"+"Radiation Safety Officer (RSO)"+"*") AND ((FacilityAddress.FacilityAddressTypeLookup)="Mailing") AND ((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)="") AND ((MachineInspection.InspectionStatus)=""));

    Code:

    Dim strSQL As String
    Dim strTable As String
    strTable = "tblTempOpenInspection"
    DoCmd.DeleteObject acTable, strTable

    strSQL = "SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Contact.ContactTypeLookup, " & _
    "FacilityAddress.FacilityAddressTypeLookup, Machine.MachineID, Machine.InstallDate, " & _
    "MachineInspection.MachineInspectionID, MachineInspection.IDInspection, MachineInspection.InspectionDate, " & _
    "MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus " & _
    "INTO " & strTable & " FROM (((Facility INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) " & _
    "INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) " & _
    "INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) " & _
    "INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID  " & _
    "WHERE (((Contact.ContactTypeLookup) Like '*'+'Radiation Safety Officer (RSO)'+'*') AND " & _
    "((FacilityAddress.FacilityAddressTypeLookup)='Mailing') AND " & _
    "((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)='') AND " & _
    "((MachineInspection.InspectionStatus) Like 'Open' Or " & _
    "(MachineInspection.InspectionStatus) Not Like 'Closed' Or " & _
    "(MachineInspection.InspectionStatus) Is Null Or " & _
    "(MachineInspection.InspectionStatus)=''))"

    CurrentDb.Execute strSQL

    Couple of things:

    1. I handle the delete error.

    2.  I exit the sub at the appropriate time.  

    3. For strings I replaced all " with ' (This may be my problem!??).

    3. I just used INTO before the FROM clause instead of INSERT INTO before the SELECT clause.  INTO should work though!

    Any help would be much appreciated,

    Thanks in advance!


    Robert Bruce

    Tuesday, August 06, 2013 7:22 PM

Answers

  • The problem was two auto number fields.  Here's the code that works.

    Public Sub CreateLetterStep1()
    On Error GoTo CreateLetterStep1_Err
    Dim strSQL As String
    Dim tblTemp As String
    tblTemp = "tblTempTable"
    DoCmd.DeleteObject acTable, tblTemp

    strSQL = "SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Contact.ContactTypeLookup, " & _
    "FacilityAddress.FacilityAddressTypeLookup, Machine.MachineID, Machine.InstallDate, " & _
    "MachineInspection.IDInspection, MachineInspection.InspectionDate, " & _
    "MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus " & _
    "INTO " & tblTemp & " FROM (((Facility INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) " & _
    "INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) " & _
    "INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) " & _
    "INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID  " & _
    "WHERE (((Contact.ContactTypeLookup) Like '*'+'Radiation Safety Officer (RSO)'+'*') AND " & _
    "((FacilityAddress.FacilityAddressTypeLookup)='Mailing') AND " & _
    "((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)=' ') AND " & _
    "((MachineInspection.InspectionStatus) Like 'Open' Or " & _
    "(MachineInspection.InspectionStatus) Not Like 'Closed' Or " & _
    "(MachineInspection.InspectionStatus) Is Null Or " & _
    "(MachineInspection.InspectionStatus)=''))"


    CurrentDb.Execute strSQL

    Exit Sub

    CreateLetterStep1_Exit:
    Exit Sub

    CreateLetterStep1_Err:
    If Err.Number = 7874 Then
        Resume Next
    Else
    MsgBox Error$
    Resume CreateLetterStep1_Exit
    End If
    End Sub


    Robert Bruce

    Wednesday, August 07, 2013 11:47 PM

All replies

  • If you aren't getting a syntax error message then the problem is that the query has too many characters for the sql string.  The sql string is too long for VBA to process.  I have had this problem before.  My workaround was to break the query into a bunch of smaller queries and temp tables.   You may find that the whole process will run much faster this way too. 

    Rich P

    Tuesday, August 06, 2013 8:35 PM
  • Please post the error you are getting.

    If you debug.print strSql, then copy/paste into a GUI query window, the cause of the problem may become more apparent.

    Tuesday, August 06, 2013 8:47 PM
  • Hi!  Thanks for responding.  I think I understand, but it is only a query made up of 5 tables.  So it's hard to believe that the string is too big.  Would breaking it up like this:

    strSQL = strSQL &,,,,,,

    be another way to go?

     

    Robert Bruce

    Tuesday, August 06, 2013 8:47 PM
  • OK, I'll try that.  But right now I'm not getting an error message.  The rest of the code after I execute the string is:

        

    Exit Sub

    CreateLetters_Err:

        If Err.Number = 7874 Then
            Resume Next

        End If

    End Sub

    When I step into it, it steps over the Exit Sub line when, If the temp table really is created, it should step into the Exit Sub line, then over the error handling and into the end sub.


    Robert Bruce

    Tuesday, August 06, 2013 8:52 PM
  • here's the error message:

    Syntax error (missing operator) in query expression’(((Contact.ContactTypeLookup) Like ‘*’+’Radiaton Safety Officer (RSO)’+’*’) AND ((FacilityAddress.FacilityAddressTypeLookup)=’Mailing’) AND ((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)=”) AND((MachineIns’.

    The " after (MachineInspection.InspectionClosedDate)= could be the issue.  But I don't think the compiler would've accepted the syntax if that was the case!  

    I'm going to change it and see what happens.


    Robert Bruce

    Tuesday, August 06, 2013 9:02 PM
  • You need to explain what 'not working' means.

    What is error 7874 and why are you ignoring it?

    You sql is for a make table query, if the table already exists, you need to delete it first, not ignore the error.

    If Exit Sub is being stepped over, it seems like there is an error generated. Need to know what that error is.

    Tuesday, August 06, 2013 9:03 PM
  • It depends on just what the actual character is after MachineInspection.InspectionClosedDate)=

    In your earlier post, it seems like two apostrophes, which should be fine, but in your latest post, it is a quote character, which would definitely throw the error.

    A couple of suggestions:

    Don't allow empty strings in text fields. Then you don't need to worry about testing for them. Makes life much simpler.

    Build your entire query in design view of a query window, then copy the SQL to your code and replace constants with your variables. Much simpler than the reverse.

    Tuesday, August 06, 2013 9:20 PM
  • Not working just means that the temp table isn't being created.  The 7874 is produced when a cmd is run to delete a table that doesn't exist.  And so you're absolutely correct, except that for the inverse, an error does need to be ignored if the table does not exist.  And I also believed that an error existed if Exit Sub is being stepped over but none is created.  Also, in earlier smaller tests of simple SQL statements, Exit Sub is only stepped over when the temp table is not being created.  So that should also be the reason here too.

    Robert Bruce

    Tuesday, August 06, 2013 9:24 PM
  • Yes, you are on it.  The character was an empty string using two single quotes i.e. ' '.  In the error msg it reads it as a double...weird.  I changed it and I'm still getting the same err msg but now Access reads it like two single quotes.  

    Good suggestion on keeping text fields Null but this db is too big and we're way too far into it to go back and make those changes.  Easy enough to change a text field to required and set a default.  But I foresee some action query issues due to the parameters and If Then issues in functions and subs passing values.  A big to do.  Not sure it is feasible.  5k lines of code, 100 tables, 150 queries, + forms, reports, linked tables and linked RDBSs??  Any suggestions on a tactic to get that done?

    And this may be a stupid question but replace my constants with variables?  Explain please!  Thanks!


    Robert Bruce

    Tuesday, August 06, 2013 9:43 PM
  • 5K lines of code is nothing. You could write DAO code to modify all of the tables/fields to set all empty strings to null and then disallow the empty string. That wouldn't take too long.

    Fixing and testing all of the queries & forms would take time. You would need to make the decision as to whether it is worth it. Rick Fisher's Find and Replace utility http://www.rickworld.com/ is a big help with things like that, but you would still need to test.

    Executing SQL like you have is often done so that criteria values can be replaced with those derived from variables.

    If you are not doing that (the SQL never changes), then I don't see a reason to build the SQL in code. Simply build a make table query, save it and execute the query.

    CurrentDb.Execute <yourqueryname>

    Tuesday, August 06, 2013 9:55 PM
  • Hey, thanks for the help today AG.  I'm heading out for the day.  Maybe we can continue this tomorrow?

    Thanks!


    Robert Bruce

    Tuesday, August 06, 2013 10:27 PM
  • Not sure if the problem is actually in your database/you code or simply in the posting but in the error message posted, the single-quote right after the first "Like" looks different from the rest (it slopes downwards from left to right and all other single-quotes slopes upwards from left to right).

    If this is actually in your SQL, you will certainly have syntax error since you would have unmatched single-quotes in your SQL String.

    Tru adding the statement:

    Debug.Print strSQL

    just after the SQL String construction, run the code, check the Immediate window for the actual SQL String being processed by the database engine.  If you need further help, please copy the String from the Immediate window and paste it into you post.

    There are some other possible improvements but I'll leave it until your main problem is resolved...

     


    Van Dinh

    Wednesday, August 07, 2013 3:52 AM
  • I see AG.

    In code the only variable is strTable.  I'm executing the SQL statement in this way because writing an append query in design view requires that there be some destination fields and the table has to already exist.  I just need a complete dump into the temp table.

    JasonM from Baldyweb.com explains it succinctly that SQL syntax, VBA syntax and referencing controls makes writing SQL in VBA tricky.  That SQL and VBA syntax is different enough so that when creating in design view guides you in writing an append, it is different enough that copying that SQL to code and replacing even the one constant with strTable won't work.  Anyway, after the append, I'm going cycle through the data set with a Select Case.  I can just jump right into the Select Case if the SQL string is execute directly prior.  


    Robert Bruce

    Wednesday, August 07, 2013 4:34 PM
  • You might be correct Rich.  I've stripped this statement to it's bare bones and it seems it's still too complicated/too many characters or something.  Here's the the less active SQL statement:

    Code:

    Dim strSQL As String
    Dim strTable As String
    strTable = "tblTempOpenInspection"
    DoCmd.DeleteObject acTable, strTable

    strSQL = "SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Machine.MachineID, Machine.InstallDate, MachineInspection.MachineInspectionID, MachineInspection.IDInspection, MachineInspection.InspectionDate, MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus INTO " & strTable & " FROM (((Facility INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID "

    CurrentDb.Execute strSQL

    Exit Sub

    CreateLetters_Err:
        
        If Err.Number = 7874 Then
            Resume Next

        End If
    Debug.Print strSQL
    End Sub

    Immediate window:

    SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Machine.MachineID, Machine.InstallDate, MachineInspection.MachineInspectionID, MachineInspection.IDInspection, MachineInspection.InspectionDate, MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus INTO tblTempOpenInspection FROM (((Facility INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID 

    I just left it one long string this time and it's still not running, skipping the Exit Sub and going straight to the error handling.  Debug.print is the suggestion of Alphonse G originally and then Vin Dinh later in the conversation string.  So it's identifying the variable, it's just not executing the string.


    Robert Bruce

    Wednesday, August 07, 2013 4:45 PM
  • Hey, thanks for responding.

    That single quote after the first like looks fine on this end.  It's not actually in the SQL.  

    I reduced the SQL to what I think are bare minimums at this point.  No empty string, nulls or other parameters and I removed some fields. 

    Immediate window:

    SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Machine.MachineID, Machine.InstallDate, MachineInspection.MachineInspectionID, MachineInspection.IDInspection, MachineInspection.InspectionDate, MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus INTO tblTempOpenInspection FROM (((Facility INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID 

    I just left it one long string this time and it's still not running, skipping the Exit Sub and going straight to the error handling.  

    Actual code:

    Dim strSQL As String
    Dim strTable As String
    strTable = "tblTempOpenInspection"
    DoCmd.DeleteObject acTable, strTable

    strSQL = "SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Machine.MachineID, Machine.InstallDate, MachineInspection.MachineInspectionID, MachineInspection.IDInspection, MachineInspection.InspectionDate, MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus INTO " & strTable & " FROM (((Facility INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID "

    CurrentDb.Execute strSQL

    Exit Sub

    CreateLetters_Err:
        
        If Err.Number = 7874 Then
            Resume Next

        End If
    Debug.Print strSQL
    End Sub

    It's identifying the variable, it's just not executing the string.


    Robert Bruce

    Wednesday, August 07, 2013 4:54 PM
  • I do'nt see how this is correct:

    Like ‘*’+’Radiaton Safety Officer (RSO)’

    Wednesday, August 07, 2013 4:54 PM
  • Wildcards were on both sides of Radiation Safety Officer (RSO).  So it was Like "*" + "Radiation Safety Officer (RSO)" + "*".

    I'm executing the above in a SQL statement in VBA...

    Dim strSQL As String
    Dim strTable As String
    strTable = "tblTempOpenInspection"
    DoCmd.DeleteObject acTable, strTable

    strSQL = " [the SQL statement] "

    so the compiler won't allow double quotes within double quotes when declaring the variable.  Chr$34 won't work throughout.  SO I thought single quotes allow the compiler to parse the string in this case.




    Wednesday, August 07, 2013 5:04 PM
  • What you are trying to do is create a 'make table' query, not an append query which would require the table to already exist.

    All you need do is create a select query, then click on the 'Make Table' button on the Design tab of the ribbon. You will be prompted for the name of the new table. Then save the query and you can just execute it via code.

    CurrentDb.Execute <yourqueryname>

    If you do need to change the destination table, then take the SQL from the SQL view of the query and replace the table name with your variable. The length of the sql should not be a problem. I have worked with much longer sql.

    Wednesday, August 07, 2013 5:37 PM
  • Oh AG you are so right, it's a make table.  I'm sorry.  

    Robert Bruce

    Wednesday, August 07, 2013 5:41 PM
  • Robert, the main point, though, is that the destination table does not need to exist and by building the query the way I mentioned, can be done in a few minutes.
    Wednesday, August 07, 2013 5:46 PM
  • Right.  I agree. That's basically what I do when it's a more relatively complicated query.  I've worked with much longer or at least as long statements in VBA as this one, but perhaps not as relatively complicated SQL statements in VBA too, but this one isn't working.

    What about the rest of it though?  If I'm going to cycle through using a Select Case, how would that work with a DoCmd.OpenQuery or CurrentDb.Execute command prior to?  Would the conditional pick it up or would I have to prepare the code for that somehow?

     

    Robert Bruce

    Wednesday, August 07, 2013 5:51 PM
  • I did that and it's still stepping over my Exit Sub to the error handling indicating that the table isn't being created.  

    Robert Bruce

    Wednesday, August 07, 2013 5:54 PM
  • Did you run the query via the GUI after you created it? Did it work?

    If so, then you can run the query itself, in code, but you'll need to delete the temp table each time first.

    Once that works, you can (if you like) take the sql from the query and run that in code.

    If it doesn't work then, it would have to do with the way the sql was broken up for wrapping in the code window.
    You do a debug.print and compare it to the original you got from the query and make your corrections.

    As for docmd.OpenQuery, I never use it.

    I typically declare a database object, set it to CurrentDb and use that.
    Dim dbS as DAO.Database
    Set dbS = CurrentDb
    strSql = <yoursql>
    dbs.Execute strSql, dbFailonerror

    Wednesday, August 07, 2013 6:21 PM
  • Sorry, AG, meetings...

    But yes, I'm sure it works in design view.  It was created in design view.  I also thought it had something to do with the way the statement was broken up for wrapping, but I reduced the parameters, removed some fields and left the shortened statement unwrapped and it still is stepping over the exit sub into the error handling.  I'm pretty sure the temptable isn't being created.  I'm checking in the navigation pane and like we've mentioned, since it's stepping over the exit sub, the sql isn't being executed.  I'm going to rewrite the block.  You know Access, sometimes that works.


    Robert Bruce

    Wednesday, August 07, 2013 8:24 PM
  • >>I just left it one long string this time and it's still not running, skipping the Exit Sub and going straight to the error handling.  Debug.print is the suggestion of Alphonse G originally and then Vin Dinh later in the conversation string.  So it's identifying the variable, it's just not executing the string.<<

    I suspect that the posted code actually executes the CurrentDb.Execute statement but it fails silently since you do not have the dbFailOnError option in your statement.  You should use:

    CurrentDb.Execute strSQL, dbFailOnError

    Another problem is that your error-trapping code only handle Error 7874 and ignore the rest.  You should use standard-error trapping code to trap all errors and then add 7874 as an exception so that your error-trapping routine handles all errors.

    Change your code to use the dbFailOnError + to trap all errors including 7874 as an exeception and see (hopefully) if VBA gives some error number + error message so that you can investigate.

     

     


    Van Dinh

    Wednesday, August 07, 2013 11:29 PM
  • OK, I'll change the error coding.  I'm sure that's the only error msg though.  The block is so simple up to this point.  I think I may have found something.  I'm pulling in two autonumber fields.  That may be a problem when executing in the IDE (but in design though).

    Robert Bruce

    Wednesday, August 07, 2013 11:34 PM
  • The problem was two auto number fields.  Here's the code that works.

    Public Sub CreateLetterStep1()
    On Error GoTo CreateLetterStep1_Err
    Dim strSQL As String
    Dim tblTemp As String
    tblTemp = "tblTempTable"
    DoCmd.DeleteObject acTable, tblTemp

    strSQL = "SELECT Facility.RegistrationNum, Facility.ApplicationDate, Facility.[R-4OnFile], Contact.ContactTypeLookup, " & _
    "FacilityAddress.FacilityAddressTypeLookup, Machine.MachineID, Machine.InstallDate, " & _
    "MachineInspection.IDInspection, MachineInspection.InspectionDate, " & _
    "MachineInspection.InspectionClosedDate, MachineInspection.InspectionStatus " & _
    "INTO " & tblTemp & " FROM (((Facility INNER JOIN Machine ON Facility.FacilityID = Machine.Facility) " & _
    "INNER JOIN Contact ON Facility.FacilityID = Contact.Facility) " & _
    "INNER JOIN FacilityAddress ON Facility.FacilityID = FacilityAddress.Facility) " & _
    "INNER JOIN MachineInspection ON Machine.MachineID = MachineInspection.MachineID  " & _
    "WHERE (((Contact.ContactTypeLookup) Like '*'+'Radiation Safety Officer (RSO)'+'*') AND " & _
    "((FacilityAddress.FacilityAddressTypeLookup)='Mailing') AND " & _
    "((MachineInspection.InspectionClosedDate) Is Null Or (MachineInspection.InspectionClosedDate)=' ') AND " & _
    "((MachineInspection.InspectionStatus) Like 'Open' Or " & _
    "(MachineInspection.InspectionStatus) Not Like 'Closed' Or " & _
    "(MachineInspection.InspectionStatus) Is Null Or " & _
    "(MachineInspection.InspectionStatus)=''))"


    CurrentDb.Execute strSQL

    Exit Sub

    CreateLetterStep1_Exit:
    Exit Sub

    CreateLetterStep1_Err:
    If Err.Number = 7874 Then
        Resume Next
    Else
    MsgBox Error$
    Resume CreateLetterStep1_Exit
    End If
    End Sub


    Robert Bruce

    Wednesday, August 07, 2013 11:47 PM
  • >>I'm pulling in two autonumber fields.<<

    That would stop the Make-Table Query/SQL.

    Wrap one of the 2 AutoNumber Field with Clng() so that ACE/JET converts it to Long for you, e.g.

    SELECT ..., CLng([SecondAutoNumField]) AS SomeNewName


    Van Dinh


    • Edited by Van DinhMVP Tuesday, August 13, 2013 7:00 AM Typos
    Thursday, August 08, 2013 12:29 AM