Help~ Ado Vba - trying to call strsql(INSERT)

[fluffyvampirekitten]

I need help with ado connection !

I'm trying to do is to do a concatenate of my 2 fields( ([PROD_SAP.PAT_LASTNAME] & [PROD_SAP.PAT_FIRSTNAME]) from my PROD_SAP table.
Then, the result will be pasted in the same table but column("PATNAME")


I'm stuck with this part .. how to call my strsql?
Any idea on how to do this part?
Thanks in advance

   objRecordset.Fields.Item(4).Value =
             ' Item(4) = PATNAME column . I wanna paste here the concatenate value

Sub Update_ConcateProdSAP()
Dim objRecordset As ADODB.Recordset
Set objRecordset = New ADODB.Recordset
Dim i As Integer
'Dim value As Variant
Dim strConcateSQL As String

strConcateSQL = " INSERT INTO PROD_SAP ( ID , EXT_PAT_ID , PAT_LASTNAME " & _
                " PAT_FIRSTNAME , PATNAME , GENDER , DOB , CREATE_DATE , " & _
                " LAST_UPDATE_DATE , C_SOURCE , CAT_TYPE ) " & _
                " SELECT ( PROD_SAP.ID , PROD_SAP.EXT_PAT_ID , PROD_SAP.PAT_LASTNAME " & _
                " PROD_SAP.PAT_FIRSTNAME , ([PROD_SAP.PAT_LASTNAME] & [PROD_SAP.PAT_FIRSTNAME]) AS PATNAME , " & _
                " PROD_SAP.GENDER , PROD_SAP.DOB , PROD_SAP.CREATE_DATE , " & _
                " PROD_SAP.LAST_UPDATE_DATE , PROD_SAP.C_SOURCE , PROD_SAP.CAT_TYPE "
'initated recordset object
objRecordset.ActiveConnection = CurrentProject.Connection
 Call objRecordset.Open("Prod_SAP", , , adLockBatchOptimistic)
  
 Do Until objRecordset.EOF
     'check for match
     
    If objRecordset.Fields.Item(2).value Is Not Null Then
    ' Item(2) = [PROD_SAP.PAT_LASTNAME] 
    'call strConcateSQL function/method/sql
    objRecordset.Fields.Item(4).Value =
    objRecordset.UpdateBatch
    
    Else
    
    objRecordset.MoveNext
Loop
        
    objRecordset.Close
    Set objRecordset = Nothing
    
End Sub

[ItsMe]

I am not an expert with ADO and I am not familiar with the nuances of UpdateBatch. However, I do not believe there is a way to avoid designating a cursor location when updating records. In other words, you cannot use an INSERT INTO statement.

What happens if you hardcode some text as a test? Create a copy and test your code on a COPY.

objRecordset.Fields.Item(4).Value = "Foo Bar"

[ssanfu]

1) This would be easier in DAO
2) You shouldn't be storing the concatenation - it can be derived in a query.
3) There is a syntax error: the sub query in the append query strConcateSQL is not correctly formed. The "FROM" clause is missing.
You have:

    strConcateSQL = " INSERT INTO PROD_SAP ( ID , EXT_PAT_ID , PAT_LASTNAME " & _
                    " PAT_FIRSTNAME , PATNAME , GENDER , DOB , CREATE_DATE , " & _
                    " LAST_UPDATE_DATE , C_SOURCE , CAT_TYPE ) " & _
                    " SELECT ( PROD_SAP.ID , PROD_SAP.EXT_PAT_ID , PROD_SAP.PAT_LASTNAME " & _
                    " PROD_SAP.PAT_FIRSTNAME , ([PROD_SAP.PAT_LASTNAME] & [PROD_SAP.PAT_FIRSTNAME]) AS PATNAME , " & _
                    " PROD_SAP.GENDER , PROD_SAP.DOB , PROD_SAP.CREATE_DATE , " & _
                    " PROD_SAP.LAST_UPDATE_DATE , PROD_SAP.C_SOURCE , PROD_SAP.CAT_TYPE "

Need to say which table(s) the records are being selected from ......

[fluffyvampirekitten]

1) This would be easier in DAO
2) You shouldn't be storing the concatenation - it can be derived in a query.
3) There is a syntax error: the sub query in the append query strConcateSQL is not correctly formed. The "FROM" clause is missing.
You have:

    strConcateSQL = " INSERT INTO PROD_SAP ( ID , EXT_PAT_ID , PAT_LASTNAME " & _
                    " PAT_FIRSTNAME , PATNAME , GENDER , DOB , CREATE_DATE , " & _
                    " LAST_UPDATE_DATE , C_SOURCE , CAT_TYPE ) " & _
                    " SELECT ( PROD_SAP.ID , PROD_SAP.EXT_PAT_ID , PROD_SAP.PAT_LASTNAME " & _
                    " PROD_SAP.PAT_FIRSTNAME , ([PROD_SAP.PAT_LASTNAME] & [PROD_SAP.PAT_FIRSTNAME]) AS PATNAME , " & _
                    " PROD_SAP.GENDER , PROD_SAP.DOB , PROD_SAP.CREATE_DATE , " & _
                    " PROD_SAP.LAST_UPDATE_DATE , PROD_SAP.C_SOURCE , PROD_SAP.CAT_TYPE "

Need to say which table(s) the records are being selected from ......

I can only achieve this by a query uh ?

Thanks for pointing out my errors .

[ssanfu]

I can only achieve this by a query uh ?

No, I didn't say that.
If you fix the syntax, your code (if it ran) would update all records because there is no criteria to limit what records would be selected for the append.


But the code starts out by having a bulk append, then switches to code to loop through a record set to concatenate two fields.
Maybe you could explain more about what you are trying to do???