Trouble with an ADO Execute statement

[Dugantrain]

I wrote an Update query which looks at items in a Temporary Import table and tries to match these items up and update their Names and Recommended Quantities with a Template Toolkit list in another table. The SQL for that query is as follows:

UPDATE tbl_Temp_Tools_Import, tbl_TK_Items SET tbl_Temp_Tools_Import.F1 = [Rec_Install], & _
 tbl_Temp_Tools_Import.F2 = [tk_item_name] & _ WHERE (((tbl_Temp_Tools_Import.F2) Like & _
 Left([tk_item_name],15) & "*"))

This Update Query executes perfectly. However, I'm trying to take this statement and write it in a module. I have replaced the double quotes around the * with single quotes so that no error occurs. The statement is executed and I get no error message, but the Temporary records do not correctly Update as they did in the original Query. In fact, nothing Updates at all. Is there some error in my syntax that I'm not seeing?

 

[Shadez]

your querey posted seems to be a miss match.

try posting your code and query versions seperatly and i should be able to help.

Wont be till thursday as im out of the office tomorrow.



ShadeZ

 

[Dugantrain]

That's fine, I appreciate your offering to help. Alright, so here is my query's SQL:

UPDATE tbl_Temp_Tools_Import, tbl_TK_Items SET tbl_Temp_Tools_Import.F1 = [Rec_Install], tbl_Temp_Tools_Import.F2 = [tk_item_name]
WHERE (((tbl_Temp_Tools_Import.F2) Like Left([tk_item_name],15) & "*"));

Here is the function in its entirety. Note that all of the other strings execute perfectly, that's why I have temporarily commented them out:

Public Function f_After_Import()
'This function cleans up all of the data in the import Temp Table, Appends to the
'Toolkit parts table, and then Deletes out of the Temp
On Error GoTo PreImport_Err
Dim Conn As ADODB.Connection
Set Conn = New ADODB.Connection
Dim sqlUpdate_Temp_FKs, sqlUpdate_Temp_Part_Names_And_Recs, sqlUpdate_Temp_Null_0s As String
Dim sqlUpdate_Temp_NewOH_And_Needs, sqlDelete_Temp_0s, sqlAppend_Temp_To_Toolkits As String
Dim sqlDelete_All_Import As String
    sqlUpdate_Temp_FKs = "UPDATE tbl_Temp_Tools_Import SET tbl_Temp_Tools_Import.fk_Temp_Site_ID = " & f_TK_Site_ID
    [COLOR=Blue]sqlUpdate_Temp_Part_Names_And_Recs  = "UPDATE tbl_Temp_Tools_Import, " & _
tbl_TK_Items SET tbl_Temp_Tools_Import.F1 = & _
[Rec_Install], tbl_Temp_Tools_Import.F2 = & _
[tk_item_name] WHERE & _
(((tbl_Temp_Tools_Import.F2) Like & _
Left([tk_item_name], 15) & '*'))"[/COLOR]
    sqlUpdate_Temp_Null_0s = "UPDATE tbl_Temp_Tools_Import SET tbl_Temp_Tools_Import.F1 " & _
        "= IIf(([f1] Is Null),0,[f1]), tbl_Temp_Tools_Import.F3 = IIf(([f3] " & _
        "Is Null),0,[f3]), tbl_Temp_Tools_Import.F6 = IIf(([f6] Is Null),0,[f6]), " & _
        "tbl_Temp_Tools_Import.F7 = IIf(([f7] Is Null),0,[f7]), " & _
        "tbl_Temp_Tools_Import.F8 = IIf(([f8] Is Null),0,[f8]), " & _
        "tbl_Temp_Tools_Import.F9 = IIf(([f9] Is Null),0,[f9]), " & _
        "tbl_Temp_Tools_Import.F10 = IIf(([f10] Is Null),0,[f10]), " & _
        "tbl_Temp_Tools_Import.F11 = IIf(([f11] Is Null),0,[f11])"
    sqlUpdate_Temp_NewOH_And_Needs = "UPDATE tbl_Temp_Tools_Import SET " & _
        "tbl_Temp_Tools_Import.F10 = [F3]+[F6]-[F7]-[F8], tbl_Temp_Tools_Import.F11 " & _
        "= [F1]-[F3]-[F6]+[F7]+[F8]"
    sqlDelete_Temp_0s = "DELETE tbl_Temp_Tools_Import.F1, tbl_Temp_Tools_Import.F3, " & _
        "tbl_Temp_Tools_Import.F6, tbl_Temp_Tools_Import.F7, " & _
        "tbl_Temp_Tools_Import.F8, tbl_Temp_Tools_Import.F9, " & _
        "tbl_Temp_Tools_Import.F10, tbl_Temp_Tools_Import.F11, " & _
        "tbl_Temp_Tools_Import.F13, tbl_Temp_Tools_Import.F14, " & _
        "tbl_Temp_Tools_Import.F15 FROM tbl_Temp_Tools_Import WHERE " & _
        "(((tbl_Temp_Tools_Import.F1)=0) AND ((tbl_Temp_Tools_Import.F3)=0) " & _
        "AND ((tbl_Temp_Tools_Import.F6)=0) AND ((tbl_Temp_Tools_Import.F7)=0) " & _
        "AND ((tbl_Temp_Tools_Import.F8)=0) AND ((tbl_Temp_Tools_Import.F9)=0) " & _
        "AND ((tbl_Temp_Tools_Import.F10)=0) AND ((tbl_Temp_Tools_Import.F11)=0) " & _
        "AND ((tbl_Temp_Tools_Import.F13) Is Null) AND ((tbl_Temp_Tools_Import.F14) " & _
        "Is Null) AND ((tbl_Temp_Tools_Import.F15) Is Null))"
    sqlAppend_Temp_To_Toolkits = "INSERT INTO tbl_TK_Site_Parts " & _
        "( fk_tk_site_id, [Rec Qty], fk_item_name, [Old Qty], Serial, " & _
        "New_Serial, [PU Qty], [DO Qty], [DOA New], [DOA Qty], [New Qty], " & _
        "[Need To Order], Comments, [Tracking#], Carrier ) SELECT " & _
        "tbl_Temp_Tools_Import.fk_Temp_Site_ID, tbl_Temp_Tools_Import.F1, " & _
        "tbl_Temp_Tools_Import.F2, tbl_Temp_Tools_Import.F3, " & _
        "tbl_Temp_Tools_Import.F4, tbl_Temp_Tools_Import.F5, " & _
        "tbl_Temp_Tools_Import.F6, tbl_Temp_Tools_Import.F7, " & _
        "tbl_Temp_Tools_Import.F8, tbl_Temp_Tools_Import.F9, " & _
        "tbl_Temp_Tools_Import.F10, tbl_Temp_Tools_Import.F11, " & _
        "tbl_Temp_Tools_Import.F13, tbl_Temp_Tools_Import.F14, " & _
        "tbl_Temp_Tools_Import.F15 FROM tbl_Temp_Tools_Import"
    sqlDelete_All_Import = "DELETE tbl_Temp_Tools_Import.* FROM tbl_Temp_Tools_Import"
On Error GoTo Import_Execution_Err
Conn.ConnectionString = CurrentProject.Connection
Conn.Open
'Conn.BeginTrans
'Conn.Execute sqlUpdate_Temp_FKs
[color=blue]Conn.Execute sqlUpdate_Temp_Part_Names_And_Recs[/color]
'Conn.Execute sqlUpdate_Temp_Null_0s
'Conn.Execute sqlUpdate_Temp_NewOH_And_Needs
'Conn.Execute sqlDelete_Temp_0s
'Conn.Execute sqlAppend_Temp_To_Toolkits
'Conn.Execute sqlDelete_All_Import
'Conn.CommitTrans
Conn.Close
Set Conn = Nothing
'MsgBox "Import Successful!"
Exit Function
On Error GoTo Import_Execution_Err
PreImport_Err:
    MsgBox Err.Description
    Exit Function
Import_Execution_Err:
'Conn.RollbackTrans
MsgBox "f_After_Import:  " & Err.Description & " " & Err.Number
Exit Function
End Function

 

[Shadez]

Ok the only problem i can c with your code is lack of " and '


i am assuming [Rec_Install], [tk_item_name] and [tk_item_name] are textboxes located on the same form as the code

sql = "UPDATE tbl_Temp_Tools_Import, tbl_TK_Items SET " & _ 
"tbl_Temp_Tools_Import.F1 = '" & [Rec_Install] & "', " & _
"tbl_Temp_Tools_Import.F2 = '" & [tk_item_name] & "' "  & _
"WHERE tbl_Temp_Tools_Import.F2 Like '" &  Left([tk_item_name],15) & "*';"

Note
& _ is used to concattinate 2 lines of code, if these lines of code are a string then you must close your string in order for it to work.


Hope this makes sence


ShadeZ

 

[Dugantrain]

Well, the double quotes and "&" were there in my original string, but when I originally posted, it was unreadable, so I fooled around with it to make it legible and I guess I forget to put the double quotes back in. But yes, now that I look at it again, the use of single quotes as well as my original syntax of:

Like Left([tk_item_name], 15) & '*'))"

as opposed to your syntax of:

Like '" &  Left([tk_item_name],15) & "*';"

are the problem areas. Thanks so much!

 

[Shadez]

NP,


I think both methods for the like should work.

i just prefere to use vba to do function call like LEFT, Its a bit faster than letting sql do teh work