Call function is not executed

[fluffyvampirekitten]

At first my " Call Update_ProdSAP " is working until i added this few lines

UpdateReqProdSAPSQL = "UPDATE PROD_SAP " & _
            "SET PAT_NAME = PAT_LASTNAME & PAT_FIRSTNAME "
        DoCmd.RunSQL UpdateReqProdSAPSQL

For some reasons , the update can work but the call method is not executed.
Any ideas where went wrong ?
Thanks in a advance .

Private Sub cmd_YesProdSAP_Click()
Dim fDialog    As Object
Dim SourcePath As String
Dim Success As Boolean
Dim DelReqProdSAPSQL As String
Dim LoadReqProdSAPSQL As String
Dim UpdateReqProdSAPSQL As String

On Error GoTo ErrorHandler
    
    Set fDialog = Application.FileDialog(3)
    Dim varFile As Variant
    Success = False
    With fDialog
       .AllowMultiSelect = False
       .Title = "Select excel file to import the data :"
       .InitialFileName = "c:\"
        If .Show = True Then
           For Each varFile In .SelectedItems
             SourcePath = varFile
           Next
           Success = True
        Else
           Success = False
        End If
    End With
    
    If Success Then
        
    
        DelReqProdSAPSQL = "Delete * From Prod_SAP"
        DoCmd.RunSQL DelReqProdSAPSQL
        DoCmd.TransferSpreadsheet acImport, 8, "Prod_SAP", SourcePath, True, ""
                                
        Call Update_ProdSAP
        
        UpdateReqProdSAPSQL = "UPDATE PROD_SAP " & _
            "SET PAT_NAME = PAT_LASTNAME & PAT_FIRSTNAME "
        DoCmd.RunSQL UpdateReqProdSAPSQL
        
        
                                                           
    End If
    
    
ErrorHandlerExit:
    Exit Sub
    
ErrorHandler:
   MsgBox "Please import the correct file!"
    Me.Refresh
Resume ErrorHandlerExit
End Sub

[ItsMe]

You say that Update_ProdSAP used to work and now it does not? Perhaps executing UpdateReqProdSAPSQL is changing the updates made by the function.

[June7]

Why are you saving concatenated value anyway? This can be calculated when needed.

[ssanfu]

At first my " Call Update_ProdSAP " is working until i added this few lines

You would have to single step through the code to determine if the Call is being executed.


I would change the code to

        UpdateReqProdSAPSQL = "UPDATE PROD_SAP " & _
                              "SET PAT_NAME = PAT_LASTNAME & PAT_FIRSTNAME "
        Debug.Print UpdateReqProdSAPSQL

        'DoCmd.RunSQL UpdateReqProdSAPSQL
        CurrentDb.Execute UpdateReqProdSAPSQL, dbFailOnError  '<-- My preference

The debug line allows you to see if the SQL statement is correctly formed. Comment out the line after debugging.
I don't thing the SQL is formed correctly.

The "DoCmd.RunSQL" executes the SQL through Access and "CurrentDb.Execute" executes the SQL through the dB engine, either Jet (pre A2007) or ACE (A2007 and later)
(Microsoft JET stands for Joint Engine Technology)
(with version 2007 onwards, JET was replaced with what was initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine)

[fluffyvampirekitten]

Why are you saving concatenated value anyway? This can be calculated when needed.

I have 2 fields in my table : PAT_LASTNAME & PAT_FIRSTNAME (tbl: PROD_SAP)
I wanna combine these two fields into one field: PAT_NAME (this column is in the same table as well - tbl: PROD_SAP)

tbl: PROD_SAP have around 800 000 rows.

[June7]

Again, why bother saving concatenated data? This can be calculated when needed.

[fluffyvampirekitten]

Again, why bother saving concatenated data? This can be calculated when needed.

I need to use the field "PAT_NAME" to union with another table and find duplicates.

[June7]

That can be done with query. Still don't see need to save the calculated value.

You say union, did you mean join?

[fluffyvampirekitten]

You would have to single step through the code to determine if the Call is being executed.


I would change the code to

        UpdateReqProdSAPSQL = "UPDATE PROD_SAP " & _
                              "SET PAT_NAME = PAT_LASTNAME & PAT_FIRSTNAME "
        Debug.Print UpdateReqProdSAPSQL

        'DoCmd.RunSQL UpdateReqProdSAPSQL
        CurrentDb.Execute UpdateReqProdSAPSQL, dbFailOnError  '<-- My preference

The debug line allows you to see if the SQL statement is correctly formed. Comment out the line after debugging.
I don't thing the SQL is formed correctly.

The "DoCmd.RunSQL" executes the SQL through Access and "CurrentDb.Execute" executes the SQL through the dB engine, either Jet (pre A2007) or ACE (A2007 and later)
(Microsoft JET stands for Joint Engine Technology)
(with version 2007 onwards, JET was replaced with what was initially called the Office Access Connectivity Engine (ACE), but which is now called the Access Database Engine)

Thanks for the help and tips

This UpdateReqProdSAPSQL can work fine.

Still, my Call function is not executed.

[fluffyvampirekitten]

That can be done with query. Still don't see need to save the calculated value.

You say union, did you mean join?

If i create a query , it will create another table right ? Then , I might encounter this error " There isn't enough disk space or memory to undo the data changes this action query is about to make "

I mean "Union ALL" .

[June7]

A SELECT query does not create a table and therefore no, should not get that error.

[fluffyvampirekitten]

A SELECT query does not create a table and therefore no, should not get that error.

I will try to create a query . Do i need this statement CurrentDb.OpenRecordset("yourQueryName") to run my query ?
Kindly advise . Thank you.

[June7]

SELECT queries are not 'run' - only action queries (DELETE, UPDATE, INSERT) are run. SELECT queries are used like tables. They can be opened or used as source for forms and reports.

This is basic Access functionality. Your profile describes yourself as 'Advanced Beginner' - are you not already familiar with using SELECT queries?