SQL Statement Won;t Execute Programatically???

[mrmmickle1]

I have the below SQL statement:

INSERT INTO PendingSamples

SELECT MYSELECT.* FROM

   (SELECT SWITCH([Hold_Reason] LIKE '*X REPREP*', 'Reprep',
                           [Hold_Reason] LIKE '*X RAPIDFIRE*', 'RapidFire',
                           [Hold_Reason] LIKE '*X D/L ISOMER SEND OUT*', 'DLIsomer',
                           [Hold_Reason] LIKE '*X AMBIEN SEND OUT*', 'Quest_Sendout',
                           [Hold_Reason] LIKE '*X NEEDS DATA*', 'Needs_Data',
                           [Hold_Reason] LIKE '*X NEEDS SCREENING*', 'Needs_Screening',
                           [Hold_Reason] LIKE '*X TEST ORDER CONFIRMATION*', 'TO_Conf',
                           [Hold_Reason] LIKE '*X CLERICAL REVIEW CONFIRMATION*', 'Clerical_Review',
                           [Hold_Reason] LIKE '*X COMPLIANCE*', 'Compliance',
                           [Hold_Reason] LIKE '*X NO PAF*', 'NO_PAF',
                           [Hold_Reason] LIKE '*X POSITVE FOR ILLEGAL*', 'ILL_NARC',
                           [Pathologist] IS NULL, 'Other_NR',
                           TRUE, 'Other_NS'
                   ) AS Type, 

[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],
[Date_Received] - [Date Specimen Taken] AS Pending_Days, [Pathologist]

FROM SamplesReceived
 WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND 
([Chart_Number] LIKE 'UC14-*' OR [Chart_Number] LIKE 'OF14-*' OR [Chart_Number] LIKE 'RF14-*' OR [Chart_Number] LIKE 'SA14-*') AND [Pending_Days] <> 0) AS MYSELECT

LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]
WHERE (((PendingSamples.[Chart_Number]) IS NULL));

That works fine if I go to SQL view and Run it. However when I try to do it programatically like this it refuses to work......

Public Sub TEST()

    Dim dbpath            As String
    Dim dbconnectStr      As String
    Dim StrSQL            As String
    Dim cn                As Object

    dbpath = "C:\Users\mmickle\Documents\NewPendingLog.accdb"  
    dbconnectStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath & ";"
    Set cn = CreateObject("ADODB.Connection")
    cn.Open dbconnectStr
   
         StrSQL = "INSERT INTO PendingSamples"
         StrSQL = StrSQL & vbLf & "SELECT MYSELECT.* FROM"
         StrSQL = StrSQL & vbLf & "(SELECT SWITCH([Hold_Reason] LIKE '*X REPREP*', 'Reprep',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X RAPIDFIRE*', 'RapidFire',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X D/L ISOMER SEND OUT*', 'DLIsomer',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X AMBIEN SEND OUT*', 'Quest_Sendout',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X NEEDS DATA*', 'Needs_Data',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X NEEDS SCREENING*', 'Needs_Screening',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X TEST ORDER CONFIRMATION*', 'TO_Conf',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X CLERICAL REVIEW CONFIRMATION*', 'Clerical_Review',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X COMPLIANCE*', 'Compliance',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X NO PAF*', 'NO_PAF',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X POSITVE FOR ILLEGAL*', 'ILL_NARC',"
         StrSQL = StrSQL & vbLf & "[Pathologist] IS NULL, 'Other_NR',"
         StrSQL = StrSQL & vbLf & "TRUE, 'Other_NS'"
         StrSQL = StrSQL & vbLf & ") AS Type, "
         StrSQL = StrSQL & vbLf & "[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],"
         StrSQL = StrSQL & vbLf & "[Pending_Days], [Pathologist]"
         StrSQL = StrSQL & vbLf & "FROM SamplesReceived"
         StrSQL = StrSQL & vbLf & "WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND "
         StrSQL = StrSQL & vbLf & "([Chart_Number] LIKE 'UC14-*' OR [Chart_Number] LIKE 'OF14-*' OR [Chart_Number] LIKE 'RF14-*' OR [Chart_Number] LIKE 'SA14-*') AND [Pending_Days] <> 0) AS MYSELECT"
         StrSQL = StrSQL & vbLf & "LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]"
         StrSQL = StrSQL & vbLf & "WHERE (((PendingSamples.[Chart_Number]) IS NULL));"
         
         cn.Execute StrSQL
         cn.Close
         
End Sub

I get no error messages.... Does anyone have any idea why the append query won't run???

I tried adding in Application.Wait ---> But it didn't seem to help anything.

This is part of a larger procedure. I am able to run two SQL statements programatically before this one that execute without a problem.... Why is this one giving me a hitch?

Any help or advice would be much appreciated. Thanks!

[llkhoutx]

You're where clause is suspect to me, because you get nothng. Get the actual value of the SQL string and paste it in the SQL view of a query, view same in the design view. It may not convert to what you expect.

[John_G]

Another thing to try is use
cn.Execute StrSQL,dbfailonerror

The .Execute is notorious for not giving any indication that it has failed with an error, and the dbfailonerror will often give some idea as to what the problem is.

[mrmmickle1]

llkhoutx,

Background: I have an existing program that uses a daily database export from our corporate database. I manipulate this Corporate Database information in MS Excel. Once it has been cleaned and manipulated to a certain point I export data from the previous day's MS Access database--> this data is then cross referenced with the cleaned corporate database export. The information that remains is then moved to a new Access Database.

Note: I know this is not ideal. It is just what I could get to work. The reason for the transition into MS Access is due to the amount of users that need to access the data. (Previously we used shared workbooks but, they were prone to crashing) I was able to get the above described method to work but it was taking 3- 4 minutes to process. That is why I am now trying to do it differently....I have been able to get the process to work when I run the 3 queries in the SQL view of the database. I know that it works because the resulting table contains the exact same information that my current program gets.
It is not possible to view the Query in Design view as it exceeds 1024 characters. Bottom line is that the query works. The cn.execute method does not seem to work for this one SQL statement....

John_G,

I tried to use this argument :dbfailonerror but I get a variable not defined error... (It should be noted that I am running this program through MS Excel and not through MS Access) I would be curious to if this would give me information onto why the query fails to execute or raise an error.
For Now I created this workaround using DoCmd.RunSQL which seems to execute the statement perfectly fine...... :

   
         appAccess.Visible = False         
         appAccess.OpenCurrentDatabase dbpath
         DoCmd.SetWarnings False
       
         'Update the PendingSamples Table with a New Types

         StrSQL = "INSERT INTO PendingSamples"
         StrSQL = StrSQL & vbLf & "SELECT MYSELECT.* FROM"
         StrSQL = StrSQL & vbLf & "(SELECT SWITCH([Hold_Reason] LIKE '*X REPREP*', 'Reprep',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X RAPIDFIRE*', 'RapidFire',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X D/L ISOMER SEND OUT*', 'DLIsomer',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X AMBIEN SEND OUT*', 'Quest_Sendout',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X NEEDS DATA*', 'Needs_Data',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X NEEDS SCREENING*', 'Needs_Screening',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X TEST ORDER CONFIRMATION*', 'TO_Conf',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X CLERICAL REVIEW CONFIRMATION*', 'Clerical_Review',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X COMPLIANCE*', 'Compliance',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X NO PAF*', 'NO_PAF',"
         StrSQL = StrSQL & vbLf & "[Hold_Reason] LIKE '*X POSITVE FOR ILLEGAL*', 'ILL_NARC',"
         StrSQL = StrSQL & vbLf & "[Pathologist] IS NULL, 'Other_NR',"
         StrSQL = StrSQL & vbLf & "TRUE, 'Other_NS'"
         StrSQL = StrSQL & vbLf & ") AS Type, "
         StrSQL = StrSQL & vbLf & "[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],"
         StrSQL = StrSQL & vbLf & "[Pending_Days], [Pathologist]"
         StrSQL = StrSQL & vbLf & "FROM SamplesReceived"
         StrSQL = StrSQL & vbLf & "WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND "
         StrSQL = StrSQL & vbLf & "([Chart_Number] LIKE 'UC14-*' OR [Chart_Number] LIKE 'OF14-*' OR [Chart_Number] LIKE 'RF14-*' OR [Chart_Number] LIKE 'SA14-*') AND [Pending_Days] <> 0) AS MYSELECT"
         StrSQL = StrSQL & vbLf & "LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]"
         StrSQL = StrSQL & vbLf & "WHERE (((PendingSamples.[Chart_Number]) IS NULL));"
      
         DoCmd.RunSQL StrSQL

         DoCmd.SetWarnings True
         appAccess.Visible = True
         appAccess.CloseCurrentDatabase

Could it be possible that the cn.execute method is not able to be used with INSERT INTO query statements???? My other two queries use UPDATE and DELETE and they process fine...

By manipulating the current database I was able to get the process taking 3-4 minutes down to a little over 10 seconds Which is a vast improvement.

If either of you have any more suggestions or ideas on how to assess why this SQL statemnt is not being executed. I would love to hear them! It would be nice to diagnose the problem.

[Lalit Mohan]

Just replace * with % when you are running this query programmatically.

[June7]

The % won't necessarily work as wildcard. See item 8 in http://www.techrepublic.com/article/...a-expressions/

And http://office.microsoft.com/en-001/a...005188185.aspx

I've never used vbLF in constructing SQL statements. I just make sure the requisite spaces are included on each line. I use Execute with INSERT INTO just fine.

Type is a reserved word, I doubt it will make a difference but try enclosing in [] or use different field name.

[mrmmickle1]

Lalit Mohan,

Thanks so much for taking the time to respond to my question. I tried replacing all of the asterisks with % and added in some debug print statements....and I got the following:

StrSQL:- 


INSERT INTO PendingSamples
SELECT MYSELECT.% FROM
(SELECT SWITCH([Hold_Reason] LIKE '%X REPREP%', 'Reprep',
[Hold_Reason] LIKE '%X RAPIDFIRE%', 'RapidFire',
[Hold_Reason] LIKE '%X D/L ISOMER SEND OUT%', 'DLIsomer',
[Hold_Reason] LIKE '%X AMBIEN SEND OUT%', 'Quest_Sendout',
[Hold_Reason] LIKE '%X NEEDS DATA%', 'Needs_Data',
[Hold_Reason] LIKE '%X NEEDS SCREENING%', 'Needs_Screening',
[Hold_Reason] LIKE '%X TEST ORDER CONFIRMATION%', 'TO_Conf',
[Hold_Reason] LIKE '%X CLERICAL REVIEW CONFIRMATION%', 'Clerical_Review',
[Hold_Reason] LIKE '%X COMPLIANCE%', 'Compliance',
[Hold_Reason] LIKE '%X NO PAF%', 'NO_PAF',
[Hold_Reason] LIKE '%X POSITVE FOR ILLEGAL%', 'ILL_NARC',
[Pathologist] IS NULL, 'Other_NR',
TRUE, 'Other_NS'
) AS Type, 
[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],
[Pending_Days], [Pathologist]
FROM SamplesReceived
WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND 
([Chart_Number] LIKE 'UC14-%' OR [Chart_Number] LIKE 'OF14-%' OR [Chart_Number] LIKE 'RF14-%' OR [Chart_Number] LIKE 'SA14-%') AND [Pending_Days] <> 0) AS MYSELECT
LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]
WHERE (((PendingSamples.[Chart_Number]) IS NULL));


Error Number:- -2147217900
Error Description:- Invalid use of '.', '!', or '()'. in query expression 'MYSELECT.'.

June7,

I tried what you mentioned and ended up with no error but the update does not happen:

StrSQL:-

INSERT INTO PendingSamples
SELECT MYSELECT.* FROM
(SELECT SWITCH([Hold_Reason] LIKE '*X REPREP*', 'Reprep',
[Hold_Reason] LIKE '*X RAPIDFIRE*', 'RapidFire',
[Hold_Reason] LIKE '*X D/L ISOMER SEND OUT*', 'DLIsomer',
[Hold_Reason] LIKE '*X AMBIEN SEND OUT*', 'Quest_Sendout',
[Hold_Reason] LIKE '*X NEEDS DATA*', 'Needs_Data',
[Hold_Reason] LIKE '*X NEEDS SCREENING*', 'Needs_Screening',
[Hold_Reason] LIKE '*X TEST ORDER CONFIRMATION*', 'TO_Conf',
[Hold_Reason] LIKE '*X CLERICAL REVIEW CONFIRMATION*', 'Clerical_Review',
[Hold_Reason] LIKE '*X COMPLIANCE*', 'Compliance',
[Hold_Reason] LIKE '*X NO PAF*', 'NO_PAF',
[Hold_Reason] LIKE '*X POSITVE FOR ILLEGAL*', 'ILL_NARC',
[Pathologist] IS NULL, 'Other_NR',
TRUE, 'Other_NS'
) AS [Type], 
[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name], [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],
[Pending_Days], [Pathologist]
FROM SamplesReceived
WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND 
([Chart_Number] LIKE 'UC14-*' OR [Chart_Number] LIKE 'OF14-*' OR [Chart_Number] LIKE 'RF14-*' OR [Chart_Number] LIKE 'SA14-*') AND [Pending_Days] <> 0) AS MYSELECT
LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]
WHERE (((PendingSamples.[Chart_Number]) IS NULL));

When I run the same statement manually in SQL view it updates 3934 records.
The fiel type is .ACCDB

Thanks for the assistance. It doesn't seem like I can get this to work using cn.execute

[Lalit Mohan]

Based on assumption, Try writing SQL query like this:

    Dim strSql          As String
    
    strSql = "INSERT INTO PendingSamples"
    strSql = strSql & vbLf & "SELECT MYSELECT.* FROM"
    strSql = strSql & vbLf & "(SELECT"
    strSql = strSql & vbLf & "SWITCH("
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X REPREP%', 'Reprep',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X RAPIDFIRE%', 'RapidFire',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X D/L ISOMER SEND OUT%', 'DLIsomer',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X AMBIEN SEND OUT%', 'Quest_Sendout',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X NEEDS DATA%', 'Needs_Data',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X NEEDS SCREENING%', 'Needs_Screening',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X TEST ORDER CONFIRMATION%', 'TO_Conf',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X CLERICAL REVIEW CONFIRMATION%', 'Clerical_Review',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X COMPLIANCE%', 'Compliance',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X NO PAF%', 'NO_PAF',"
    strSql = strSql & vbLf & "[Hold_Reason] LIKE '%X POSITVE FOR ILLEGAL%', 'ILL_NARC',"
    strSql = strSql & vbLf & "[Pathologist] IS NULL, 'Other_NR',"
    strSql = strSql & vbLf & "TRUE, 'Other_NS'"
    strSql = strSql & vbLf & ") AS Type,"
    strSql = strSql & vbLf & "[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name]"
    strSql = strSql & vbLf & ", [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],"
    strSql = strSql & vbLf & "[Pending_Days] , [Pathologist]"
    strSql = strSql & vbLf & "FROM SamplesReceived"
    strSql = strSql & vbLf & "WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND"
    strSql = strSql & vbLf & "([Chart_Number] LIKE 'UC14-%' OR [Chart_Number] LIKE 'OF14-%'"
    strSql = strSql & vbLf & "OR [Chart_Number] LIKE 'RF14-%' OR [Chart_Number] LIKE 'SA14-%')"
    strSql = strSql & vbLf & "AND [Pending_Days] <> 0) AS MYSELECT"
    strSql = strSql & vbLf & "LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]"
    strSql = strSql & vbLf & "WHERE PendingSamples.[Chart_Number] IS NULL"

OR

INSERT INTO PendingSamples
SELECT MYSELECT.* FROM
(SELECT
SWITCH(
[Hold_Reason] LIKE '%X REPREP%', 'Reprep',
[Hold_Reason] LIKE '%X RAPIDFIRE%', 'RapidFire',
[Hold_Reason] LIKE '%X D/L ISOMER SEND OUT%', 'DLIsomer',
[Hold_Reason] LIKE '%X AMBIEN SEND OUT%', 'Quest_Sendout',
[Hold_Reason] LIKE '%X NEEDS DATA%', 'Needs_Data',
[Hold_Reason] LIKE '%X NEEDS SCREENING%', 'Needs_Screening',
[Hold_Reason] LIKE '%X TEST ORDER CONFIRMATION%', 'TO_Conf',
[Hold_Reason] LIKE '%X CLERICAL REVIEW CONFIRMATION%', 'Clerical_Review',
[Hold_Reason] LIKE '%X COMPLIANCE%', 'Compliance',
[Hold_Reason] LIKE '%X NO PAF%', 'NO_PAF',
[Hold_Reason] LIKE '%X POSITVE FOR ILLEGAL%', 'ILL_NARC',
[Pathologist] IS NULL, 'Other_NR',
TRUE, 'Other_NS'
) AS [Type],
[MR_Num], [Chart_Number], [Clinic_Location], [Last_Name]
, [First_Name], [Date_Received], [Sales_Rep], [Hold_Reason],
[Pending_Days] , [Pathologist]
FROM SamplesReceived
WHERE ([Clinic_Location] <> 'Central Perch' AND  [Clinic_Location] IS NOT NULL) AND
([Chart_Number] LIKE 'UC14-%' OR [Chart_Number] LIKE 'OF14-%'
OR [Chart_Number] LIKE 'RF14-%' OR [Chart_Number] LIKE 'SA14-%')
AND [Pending_Days] <> 0) AS MYSELECT
LEFT JOIN PendingSamples ON MYSELECT.[Chart_Number] = PendingSamples.[Chart_Number]
WHERE PendingSamples.[Chart_Number] IS NULL