activating long SQL string from VBA

[berlinhammer]

Hello All,

In the interests of end user simplification, I was hoping to activate an SQL query from a VBA macro in excel. Unfortunately the SQL is quite long and complex and I keep getting an automation error &H8004005(-2147467259). Initially I had a subroutine return a concatenated SQL string to one cell and then called it from there. It was clear that the SQL was too long for a cell to be happy holding it so I tried a function instead (seems neater anyway), unfortunately that is not working either.

Would any body be good enough as to offer any suggestion? I'm all out of ideas.

I'm sure my SQL is ok, but as it is so long I can not get it all to show in Immediate Window, so if it is being messed up by quotation marks or line breaks or whatever in the macro I can't say. I presume that is the problem but am not sure how to identify it precisely or where to go from here.

The SQL is stored in a sheet named SQL in a Named Range called "Test" and is over c ouple of hundred rows (i.e. one row per line of SQL)

Also if SQL is more my issue here then apologies and please be so kind as to point me in the direction of an appropriate forum.

Thank you for any help!!

Jon

Option Explicit

Private Function GenerateSQLString() As String

Dim l As Long
Dim rng As Range
Dim str As String

Set rng = Sheet2.Range("Test")

For l = 1 To rng.Cells.Count
If Len(rng.Cells(l).Value) > 0 Then
    str = str & rng.Cells(l).Value & Chr(10)
    Debug.Print str
End If
Next l

Debug.Print str

GenerateSQLString = str

End Function


Public Sub GetData()
    Dim conn As New ADODB.Connection
    Dim connString As String
    Dim sqlString As String
    Dim iCols As Long
    Dim rsRecords As New ADODB.Recordset

    connString = "DSN=xxx;Uid=xxxxxx;Pwd=xxxx"

    sqlString = GenerateSQLString
    
    Debug.Print sqlString
    Debug.Print Len(sqlString)
    
    conn.Open connString
    rsRecords.CursorLocation = adUseServer
    rsRecords.Open sqlString, conn, adOpenForwardOnly, adLockReadOnly

    If conn.State = adStateOpen Then
        Worksheets("Data").Range("A2").CopyFromRecordset rsRecords
      
        For iCols = 0 To rsRecords.Fields.Count - 1
            Worksheets("Data").Range("A1").Cells(1, iCols + 1).Value = rsRecords.Fields(iCols).Name
        Next
    Else
        MsgBox "no connection"
    End If

    rsRecords.Close
    Set rsRecords = Nothing
    conn.Close
    Set conn = Nothing
End Sub

SQL:

SELECT 
DEAL_ID DEAL_ID 
,TEMPTABLE.COMMITTEE_ID COMMITTEE_ID 
,TEMPTABLE.COMMITTEE_DT COMMITTEE_DT 
,TEMPTABLE.COMMITTEETYPE COMMITTEETYPE 
,TEMPTABLE.COMMITTEEPURPOSE COMMITTEEPURPOSE 
,TEMPTABLE.COUNTRYNAME COUNTRYNAME 
,TEMPTABLE.BUSINESSLINE BUSINESSLINE 
,TEMPTABLE.DEAL DEAL 
,CASE WHEN UPPER(TEMPTABLE.OOSCREENERCMMTT) LIKE '%RED%' THEN 'RED' 
WHEN UPPER(TEMPTABLE.OOSCREENERCMMTT) LIKE '%AMBER%' THEN 'AMBER' 
WHEN UPPER(TEMPTABLE.OOSCREENERCMMTT) LIKE '%GREEN%' THEN 'GREEN' 
ELSE NULL END DESCISION 
,TEMPTABLE.OOSCREENERCMMTT FULLOUTCOME 
,TEMPTABLE.PRIMARYANALYST PRIMARYANALYST 
,TEMPTABLE.CHAIRVOT CHAIRVOT 
,TEMPTABLE.CHAIRNVOT CHAIRNVOT 
,TEMPTABLE.CHAIRIND CHAIRIND 
,TEMPTABLE.INDPERSON INDPERSON 
,TEMPTABLE.VOTER VOTER 
,TEMPTABLE.NONVOTER NONVOTER 
,TEMPTABLE.SECANALYST SECANALYST 
,TEMPTABLE.OBSERVER OBSERVER 
,TEMPTABLE.SCREENERCMMTT_COMMENTS SCREENERCMMTT_COMMENTS 
,TEMPTABLE.SRVC_ID SRVC_ID 
,TEMPTABLE.GEO_SEGMENT GEO_SEGMENT 
,TEMPTABLE.NBRPRIMARYANALYST NBRPRIMARYANALYST 
,TEMPTABLE.NBRCHAIRVOT NBRCHAIRVOT 
,TEMPTABLE.NBRCHAIRNVOT NBRCHAIRNVOT 
,TEMPTABLE.NBRCHAIRIND NBRCHAIRIND 
,TEMPTABLE.NBRINDEPENDENT NBRINDEPENDENT 
,TEMPTABLE.NBRVOTER NBRVOTER 
,TEMPTABLE.NBRNONVOTER NBRNONVOTER 
,TEMPTABLE.NBRSECONDARYANALYST NBRSECONDARYANALYST 
,TEMPTABLE.NBROBSERVER NBROBSERVER 
FROM 
(SELECT 
DEAL.DEAL_ID DEAL_ID, 
DEAL.DEAL_LEGAL_NAME DEAL, 
BG.BUS_GRP_TYP BUSINESSLINE, 
DC.SRVC_ID SRVC_ID, 
DC.COMMITTEE_ID COMMITTEE_ID, 
DC.COMMITTEE_DT COMMITTEE_DT, 
CT.COMMITTEE_TYPE_DESC COMMITTEE_DATE, 
SCO.SCRNR_CMTE_OUTCOME_NAME OOSCREENERCMMTT, 
DC.SCRNR_COMMENTARY SCREENERCMMTT_COMMENTS, 
PRIMAN.PRIMARYANALYST PRIMARYANALYST 
,CHAIRVOT.CHAIRVOT CHAIRVOT 
,CHAIRIND.CHAIRIND CHAIRIND 
,VOTER.VOTER VOTER 
,INDPERSON INDPERSON 
,NONVOTER.NONVOTER NONVOTER 
,SECANALYST.SECANALYST SECANALYST 
,OBSERVER.OBSERVER OBSERVER 
,CHAIRNVOT.CHAIRNVOT CHAIRNVOT 
,CT.COMMITTEE_TYPE_DESC COMMITTEETYPE 
,CNTRY.CNTRY_NM COUNTRYNAME 
,CMTTPURPOSE.CMTT_PURPOSE_DESC COMMITTEEPURPOSE 
,GS.GEO_SEGMENT_NAME GEO_SEGMENT 
,NVL(QTITLECNT.CHAIRVOTTOTAL, 0) NBRCHAIRVOT 
,NVL(QTITLECNT.INDPTOTAL, 0) NBRINDEPENDENT 
,NVL(QTITLECNT.VOTERTOTAL, 0) NBRVOTER 
,NVL(QTITLECNT.NONVOTERTOTAL, 0) NBRNONVOTER 
,NVL(QTITLECNT.PRIMANLYTOTAL, 0) NBRPRIMARYANALYST 
,NVL(QTITLECNT.SECANLYTOTAL, 0) NBRSECONDARYANALYST 
,NVL(QTITLECNT.CHAIRINDTOTAL, 0) NBRCHAIRIND 
,NVL(QTITLECNT.OBSERVERTOTAL, 0) NBROBSERVER 
,NVL(QTITLECNT.CHAIRNVOTTOTAL, 0) NBRCHAIRNVOT 
FROM GLOBAL_SF.DEAL_COMMITTEE DC, 
FII_CORE.CNTRY CNTRY, 
GLOBAL_SF.COMMITTEE_TYPE CT, 
GLOBAL_SF.DEAL DEAL , 
FII_CORE.BUS_GRP BG, 
GLOBAL_SF.GEO_SEGMENT GS, 
FTCH_SRVC.CMTT_PURPOSE CMTTPURPOSE, 
GLOBAL_SF.SCREENER_COMMITTEE_OUTCOME SCO, 
(SELECT 
COMMITTEE_ID COMMITTEE_ID 
,MAX(DECODE(QUORUM_TITLE_ID, 1, SUBTOTAL, 0)) CHAIRVOTTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 2, SUBTOTAL, 0)) INDPTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 3, SUBTOTAL, 0)) VOTERTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 4, SUBTOTAL, 0)) NONVOTERTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 5, SUBTOTAL, 0)) PRIMANLYTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 6, SUBTOTAL, 0)) SECANLYTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 16, SUBTOTAL, 0)) OBSERVERTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 17, SUBTOTAL, 0)) CHAIRNVOTTOTAL 
,MAX(DECODE(QUORUM_TITLE_ID, 18, SUBTOTAL, 0)) CHAIRINDTOTAL 
FROM( 
SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
DCA.QUORUM_TITLE_ID QUORUM_TITLE_ID , 
COUNT(*) SUBTOTAL 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA 
GROUP BY DCA.COMMITTEE_ID 
,DCA.QUORUM_TITLE_ID ) 
GROUP BY COMMITTEE_ID) QTITLECNT 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') PRIMARYANALYST 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 5 
AND DCA.EMPNO = EMP.EMPNO) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)PRIMAN 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') CHAIRVOT 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 1 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)CHAIRVOT 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') CHAIRNVOT 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 17 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)CHAIRNVOT 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') CHAIRIND 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 18 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)CHAIRIND 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') VOTER 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 3 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)VOTER 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') SECANALYST 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 6 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)SECANALYST 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') NONVOTER 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 4 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)NONVOTER 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';')INDPERSON 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 2 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)INDPERSON 
,(SELECT COMMITTEE_ID COMMITTEE_ID, 
LTRIM(MAX(SYS_CONNECT_BY_PATH(PRIMARYANALYST,';')) 
KEEP(DENSE_RANK LAST ORDER BY RNT),';') OBSERVER 
FROM 
(SELECT DCA.COMMITTEE_ID COMMITTEE_ID, 
TRIM(FNAME)|| ' '|| TRIM(LNAME) PRIMARYANALYST, 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) ) RNT , 
ROW_NUMBER() OVER (PARTITION BY DCA.COMMITTEE_ID ORDER BY TRIM(LNAME), TRIM(FNAME) )-1 PRIOR_RNT 
FROM GLOBAL_SF.DEAL_COMMITTEE_ATTENDEE DCA, 
FII_CORE.EMP EMP 
WHERE DCA.QUORUM_TITLE_ID = 16 
AND DCA.EMPNO = EMP.EMPNO 
) 
GROUP BY COMMITTEE_ID 
CONNECT BY PRIOR_RNT = PRIOR RNT AND COMMITTEE_ID = PRIOR COMMITTEE_ID 
START WITH RNT = 1)OBSERVER 
WHERE 
DC.COMMITTEE_TYPE_ID = CT.COMMITTEE_TYPE_ID 
AND DC.DEAL_ID = DEAL.DEAL_ID 
AND DEAL.BUS_GRP_ID = BG.BUS_GRP_ID 
AND DEAL.GEO_SEGMENT_ID = GS.GEO_SEGMENT_ID (+) 
AND DC.SCRNR_CMTE_OUTCOME_ID = SCO.SCRNR_CMTE_OUTCOME_ID (+) 
AND DC.COMMITTEE_ID = PRIMAN.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = CHAIRVOT.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = CHAIRNVOT.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = CHAIRIND.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = VOTER.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = INDPERSON.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = NONVOTER.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = SECANALYST.COMMITTEE_ID(+) 
AND DC.COMMITTEE_ID = OBSERVER.COMMITTEE_ID(+) 
AND DEAL.COUNTRY_OF_ASSETS_ID = CNTRY.CNTRY_ID (+) 
AND DC.COMMITTEE_PURPOSE_ID = CMTTPURPOSE.CMTT_PURPOSE_ID (+) 
AND DC.ACTV_FLG = 'Y' 
AND DC.COMMITTEE_ID = QTITLECNT.COMMITTEE_ID (+) 
) TEMPTABLE 
WHERE UPPER(GEO_SEGMENT) = UPPER('EMEA') 
AND UPPER(COMMITTEETYPE) = UPPER('APPEAL') 
AND COMMITTEE_DT >= '01 JUN 2010'
AND COMMITTEE_DT < '01 JUL 2010'
ORDER BY COMMITTEE_DT DESC 
;

 

[xenou]

Be sure you are using the correct driver for the database provider. The sql variant you are using here may or may not be supported by ADO - things like "start with","keep", "connect by" are not standard SQL (not sure what database engine you are using, in fact). It would be best if the query were stored as a procedure on the server. Then you would only need to call the stored procedure by name - and you wouldn't have to debug this in Excel (which looks well nigh impossible to me) - it would all be tested and proven on the server side.

 

[berlinhammer]

Be sure you are using the correct driver for the database provider. The sql variant you are using here may or may not be supported by ADO - things like "start with","keep", "connect by" are not standard SQL (not sure what database engine you are using, in fact). It would be best if the query were stored as a procedure on the server. Then you would only need to call the stored procedure by name - and you wouldn't have to debug this in Excel (which looks well nigh impossible to me) - it would all be tested and proven on the server side.

Many thanks for your reply Xenou. Sorry, I can't believe I forgot to mention the database engine was Oracle (10g)! The reason I know that the SQL is sound is that I can run it in OraDev.

I hadn't thought of that, it would make sense I suppose. I have a pretty good handle on SQL and am fairly at home in Excel, but learning how the two go together and what my Database Connectivity is, is something of a learning curve for me! DNS, ADO, ODBC, OLE etc..............

If that is not the problem I did find this bit of code which gives a bit more info on the Oracle error, it identifies it as ORA-00911 'invalid character', I don't know if these messages are to be believed though.

ErrHandler:
     If conn.Errors.Count > 0 Then
          MsgBox conn.Errors(0).Description
     End If

I have no idea how to set up a Stored Procedure but if it is Server side then I very much doubt I have the access to do that, I'll ask my superviser if it is an option.

Incidentally is there a list of functions and keywords in SQL that is supported by ADO?

Thanks for the help on this I really appreciate it.

Jon

 

[xenou]

Truthfully, you might want to search for an Oracle forum. I would expect the Oracle driver for ADO to be pretty good but on the other hand its not used as much by members here (many more threads on Access/Jet and SQL server). Oracle error messages (in my experience) aren't particularly helpful. That could mean no more than a missing comma or parenthesis. It's really a very long sql string and you almost need to run it on the server to get a better debug environment. For instance, on the Oracle server itself it would probably give you the line and a caret telling you exactly where the error is (or where it thinks it is). Aside from that, the only method I know of is to break the query down into parts and run them one by one - each nested query by itself and so on. Dropping it into a good text editor with SQL keyword highlighting is useful too - you'll get better visual clues.

 

[berlinhammer]

Thanks for that Xenou

I actually did post this up on Oracle after your reply and someone suggested dropping the final semi-colon. To my surprise it worked, though I do not understand why as I ran a simpler 5 line SQL WITH the semi-colon and it worked fine :S.

It would appear to be a problem that needs debugging on the Oracle side than the Excel side I suppose so I guess I'm in the wrong place. I don't have any admin rights to our server, in truth I'm not really sure where or what it is exactly. All I can do is query it from my client PC. Hopefully if I can see exactly what it is that I am passing to Oracle from the Macro then I can get to understanding why it was tripping.

Thanks for all the help.

Jon

 

[Akihito Yamashiro]

Hi.
At which line does the error occur ?
If 'CopyFromRecordset' occurs the problem,
what about replacing the method to the following.
(I'm sorry , the code is not tested at all.)

        '''Worksheets("Data").Range("A2").CopyFromRecordset rsRecords
        Dim x As Long
        Dim y As Long
        Do Until rsRecords.EOF
 
           For x = 0 To rsRecords.Fields.Count - 1
               Worksheets("Data").Range("A2").Offset(y, x) = rsRecords.Fields(x) & ""
           Next
           RsRecords.MoveNext
           y = y + 1
        Loop

 

[xenou]

I actually did post this up on Oracle after your reply and someone suggested dropping the final semi-colon. To my surprise it worked, though I do not understand why as I ran a simpler 5 line SQL WITH the semi-colon and it worked fine :S.

That's certainly a curiosity. Ending sql statements with ; is pretty standard fare. I would never have guessed it! Typically if you have access to some kind of native query design application its much easier for building and testing more complex queries, but I understand not everyone has such tools.