Getting an error while joining the SQL tables through excel VBA

Question

i need some help here. I am trying to extract the data through SQL in excel having the join query but i am getting the debug error while running through below code..

recset.Open strSQL, con, adOpenDynamic, adLockOptimistic

Error - Runtime error -2147467259

Below is the code.....Please help :confused:

Sub Show_data()

    Dim con As ADODB.Connection
    Dim recset As ADODB.Recordset
    Dim ConnectionString As String
    Dim strSQL As String

    Set con = New ADODB.Connection
    Set recset = New ADODB.Recordset

''Check for the connectivity or connected to the xx network
    On Error GoTo errHandler

errHandler:
    If Err.Number = -2147467259 Then
        MsgBox "Please check for the xx connectivity ", vbExclamation + vbOKOnly
        Exit Sub
    End If


    ConnectionString = "Provider=MSDASQL;User ID=myuser;password= mypass;Data Source=mys"

    con.Open ConnectionString

    'Set and Excecute SQL Command'

        strSQL = "SELECT B.USER_NAME AS CREATED_BY, A.CREATION_DATE, C.USER_NAME, A.LAST_UPDATE_DATE, A.PFIZER_ITEMCODE, A.SYSTEM_ITEMCODE AS ORACLE_ITEM_CODE," & _
        "A.ITEM_DESCRIPTION, A.BATCH_NUMBER, A.MFR_CODE, A.MFR_DESC AS MFR_DESCRIPTION, TO_CHAR(A.MFR_DATE,'DD-MON-YYYY')As MFR_DATE, TO_CHAR(A.EXPIRY_DATE,'DD-MON-YYYY')As EXPIRY_DATE," & _
        "TO_CHAR(A.EFFECTIVE_FROM,'DD-MON-YYYY') AS EFFECTIVE_FROM," & _
        "A.EFFECTIVE_TO, A.EXCISE AS EXCISE_AMOUNT, A.EXCISE_RATE, A.P2S, A.P2R, A.MRP, A.STATE_CODE, A.STATE," & _
        "(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN 'OPF' ELSE 'OWL' END )AS LEGAL_ENTITY" & _
        "FROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c" & _
        "WHERE 1=1" & _
        "AND A.CREATED_BY = B.USER_ID" & _
        "AND A.LAST_UPDATED_BY = C.USER_ID"

        If (ActiveSheet.cmbLE.Text) <> "" Then
        strSQL = strSQL & " LEGAL_ENTITY='" & ActiveSheet.cmbLE.Text & "'"
        End If

        If (ActiveSheet.cmbProduct.Text) <> "" Then
            If (ActiveSheet.cmbLE.Text) <> "" Then
                strSQL = strSQL & " AND [ORACLE_ITEM_CODE]='" & ActiveSheet.cmbProduct.Text & "'"
            Else
                strSQL = strSQL & " [ORACLE_ITEM_CODE]='" & ActiveSheet.cmbProduct.Text & "'"
            End If
        End If

        If (ActiveSheet.txtBatch.Text) <> "" Then
            If (ActiveSheet.cmbLE.Text) <> "" Or (ActiveSheet.cmbProduct.Text) <> "" Then
                strSQL = strSQL & " AND [BATCH_NUMBER]='" & ActiveSheet.txtBatch.Text & "'"
            Else
                strSQL = strSQL & " [BATCH_NUMBER]='" & ActiveSheet.txtBatch.Text & "'"
            End If
        End If



    'Open Recordset

    Set recset.ActiveConnection = con
    recset.Open strSQL, con, adOpenDynamic, adLockOptimistic

    'Copy the data
    If recset.RecordCount > 0 Then
    Sheets("Sheet2").Range("A1").CopyFromRecordset recset
'    ActiveSheet.Range("A1").CopyFromRecordset recset
    Else
    MsgBox "No Data Available", vbExclamation + vbOKOnly
    Exit Sub
    End If

recset.Close
con.Close

End Sub

As per all you requirements i had print the strSQL

    SELECT B.USER_NAME AS CREATED_BY, A.CREATION_DATE, C.USER_NAME, 
    A.LAST_UPDATE_DATE, 
    A.PFIZER_ITEMCODE, A.SYSTEM_ITEMCODE AS ORACLE_ITEM_CODE, 
    A.ITEM_DESCRIPTION, A.BATCH_NUMBER, 
        A.MFR_CODE, A.MFR_DESC AS MFR_DESCRIPTION, 
        TO_CHAR(A.MFR_DATE,'DD-MON-YYYY')As MFR_DATE, 
    TO_CHAR(A.EXPIRY_DATE,'DD-MON-YYYY')As EXPIRY_DATE, 
        TO_CHAR(A.EFFECTIVE_FROM,'DD-MON-YYYY') AS EFFECTIVE_FROM, 
A.EFFECTIVE_TO, A.EXCISE AS EXCISE_AMOUNT, 
        A.EXCISE_RATE, A.P2S, 
A.P2R, A.MRP, A.STATE_CODE, A.STATE, 
        (CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN 'OPF' ELSE 'OWL' END )AS LEGAL_ENTITY 
            FROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c 
WHERE 1=1 AND A.CREATED_BY = B.USER_ID AND A.LAST_UPDATED_BY = C.USER_ID  AND [A.LEGAL_ENTITY]='J0012' AND [A.SYSTEM_ITEMCODE]='1407_PI-J0012'

Can you print strSQL just before its run and attach to the question? — Andy Joiner
– Andy Joiner, Commented May 19, 2015 at 16:05
Should [A.LEGAL_ENTITY] be instead A.LEGAL_ENTITY or [A].[LEGAL_ENTITY] and so on. — barrowc
– barrowc, Commented May 19, 2015 at 23:32

Andy Joiner · Accepted Answer · 2015-05-19 16:09:17Z

1

Where you have code like

"WHERE 1=1" & _
"AND A.CREATED_BY = B.USER_ID" & _

It will be rendered

"WHERE 1=1AND A.CREATED_BY = B.USER_ID"

i.e. No space between the 1=1 and AND

Try putting a space before closing quotes on each line:

"WHERE 1=1 " & _
"AND A.CREATED_BY = B.USER_ID " & _

answered May 19, 2015 at 16:09

Andy Joiner

6,7315 gold badges52 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

ChipsLetten Over a year ago

which line of code creates the error? Can you print the strSQL variable? does the error still occur if using something like "SELECT * FROM Table"?

ChipsLetten Over a year ago

Does the error occur at this line: recset.Open strSQL, con, adOpenDynamic, adLockOptimistic? To print the value of strSQL use the line Debug.Print strSQL just before this line and the value of the variable gets written to the immediate window in the IDE.

ChipsLetten Over a year ago

Is there an 'AND' missing from just before 'LEGAL_ENTITY' in this sql AND A.LAST_UPDATED_BY = C.USER_ID LEGAL_ENTITY='OIP' AND

ChipsLetten Over a year ago

I think you need to comment out all of the WHERE code and then gradually uncomment bits and run the code to find the bit(s) that are causing the issues. It also looks like you are using the fnd_user table twice in the FROM clause - is that intentional?

ChipsLetten Over a year ago

As mentioned before, I think you need to comment out all of the WHERE code, run the code and then gradually uncomment bits and run the code to find the bit(s) that are causing the issues.

|

dendarii · Accepted Answer · 2015-05-19 16:43:54Z

1

There is a limit to the number of characters that can be added to a string variable using the "& _" extender.

Chip's suggestion to Debug.Print the variable will tell you whether or not this query is viable before you try to execute it.

When I create my SQL for a query, I use a process like this to avoid truncation:

strSQL = "SELECT "
strSQL = strSQL & "Field1, Field2, Field3 "
strSQL = strSQL & "FROM MyTable "

Adding a space at the end of each line ensures that the issue Andy notes above doesn't happen.

answered May 19, 2015 at 16:43

dendarii

3,10823 silver badges15 bronze badges

Comments

Gene · Accepted Answer · 2015-05-19 16:55:18Z

1

Similiar to Andy Joiner suggestion, you're actually missing a space at the end of several places. For example

"(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN 'OPF' ELSE 'OWL' END )AS LEGAL_ENTITY" & _
"FROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c" & _

will look like:

"(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN OPF' ELSE 'OWL' END )AS LEGAL_ENTITYFROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c" & _

Thus eliminating your FROM clause.

answered May 19, 2015 at 16:55

Gene

3926 silver badges15 bronze badges

5 Comments

Kalpesh Koli Over a year ago

Yes i tried with space but still the same error i m facing

Gene Over a year ago

As suggested previously we need to see the full query as it is being passed to SQL

Kalpesh Koli Over a year ago

Hi i have put the query from the print of debug sql ...pls see and let me know if any...please

Gene Over a year ago

Is LEGAL_ENTITY a field in your xxdhl_pf_batch_pricing table?

Gene Over a year ago

Then you can't use a.[LEGAL_ENTITY], you should use :(CASE SUBSTR(A.SYSTEM_ITEMCODE,6,2) WHEN ('PI') THEN 'OIP' WHEN ('PF') THEN 'OPF' ELSE 'OWL' END ) = ''J0012' instead. Which looks like it will never evaluate to true....

Kalpesh Koli · Accepted Answer · 2015-05-21 15:16:18Z

0

Finally i had solved the error.....actually in a coding where i have given parameter in which the quotes was given the problem...i.e.

Previously i was running the query with below code,

strSQL & " AND [ORACLE_ITEM_CODE]='"

Just beacause there was the quote

"["

...ws getting that error.

But after removing the same its working fine....

I would thanks to all of you who shared their knowledge to solve the error...it also helps me to gain my knowledge...

Thanks all

answered May 21, 2015 at 15:16

Kalpesh Koli

872 gold badges2 silver badges16 bronze badges

Collectives™ on Stack Overflow

Getting an error while joining the SQL tables through excel VBA

4 Answers 4

15 Comments

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

15 Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related