Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Solving SQL error using appendText (Read 2703 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Solving SQL error using appendText
21.09.09 at 13:59:49
 
Solving SQL error using appendText
 
Quote:
I'm trying to re-arrange, or break the lines in my VBA script into more and shorter lines so that they will fit the VBA editor window using additional .appendText lines.  When I do, the script errors out with a "FROM keyword not found where expected" error on the SQLXL.Sql.Statements(3).Execute line (3rd line up from the end of the script). Please look at this script and see if you can tell me where the syntax errors are. If I run it just as it was created by the automatic SQL*XL process, the script runs fine. The problem originates in the first WITH clause in the .appendText lines.

Thanks!

 
Edited:
Note that in SQL*XL 5.2 the syntax changed slightly. The ConnectionType parameter became read only and instead the ConnectionType is passed as a parameter to the Connect routine. For further information see:
http://www.oraxcel.com/projects/sqlxl/help/vba/SQLXL/Database/index.html

 
Code:
Private Sub CommandButton1_Click()

SQLXL.InitialiseSQLXL
    SQLXL.Database.ConnectionType = litSQLXLADO
    SQLXL.Database.Connect UserName:="prod", Password:="scrambled", DBAlias:="Oracle Wire Protocol", ConnectionString:="Provider=MSDASQL.1;Password=scrambled; User ID=prod; Extended Properties=""DSN=Oracle Wire Protocol; UID=PROD;PWD=scrambled; Database=Oracle;Host=123.456.789.123;Port=1521;SID=PROD""", AllowTransactions:=True

    
  SQLXL.Database.Connect _
    UserName:="scrambled", _
    Password:="scrambled", _
    DBAlias:="Oracle Wire Protocol", _
    ConnectionString:="Provider=MSDASQL.1;Password=scrambled;" & _
    " User ID=prod;" & _
    " Extended Properties=""DSN=Oracle Wire Protocol;" & _
    " UID=NOR_PROD;PWD=scrambled;" & _
    " Database=Oracle;" & _
    " Host=10.0.0.1;" & _
    " Port=1521;SID=PROD""", AllowTransactions:=True
    With SQLXL.Sql
     .Clear
     .appendText "ACCEPT SAL_PR_PD_END_DATE PROMPT 'Enter SALARIED PR Period End Date as YYYY/MM/DD:';  "
     .appendText "ACCEPT CO_ID PROMPT 'Enter Company to Process - IMPR, NORM, or NRSH:';  "
     .appendText "SELECT p.emp_id, l.ded_num, d.descr,f.ss_num, f.ou_id, SUM(NVL( l.amt, 0 )) "
     .appendText "p.gross_pay, e.pct, f.last_name, f.first_name, f.middle_initial   "
     .appendText "FROM lab_tran l, deduction d, pr_chk_hist p, emp_ded e, employee f  "
     .appendText "WHERE (f.alpha_6 = '&CO_ID' OR f.alpha_6 = '&&CO_ID' OR f.alpha_6 = '&&&CO_ID')  "
     .appendText "AND (l.ded_num = '553' OR l.ded_num = '567')  "
     .appendText "AND l.ded_num = d.ded_num  "
     .appendText "AND l.pr_chk_sa_num = p.pr_chk_sa_num  "
     .appendText "AND ((p.pay_group_num IN ('200','250','325', '600', '625', '750')  "
     .appendText "AND p.prd_end_date = to_char(to_date('&SAL_PR_PD_END_DATE','YYYY/MM/DD')-7,'YYYY/MM/DD'))  "
     .appendText "OR (p.prd_end_date = '&&sal_pr_pd_end_date' "
     .appendText "AND p.pay_group_num IN ('100', '300','400', '600', '700'))) "
     .appendText "AND ((p.emp_id = e.emp_id AND e.ded_num = '553') OR (p.emp_id = e.emp_id AND e.ded_num = '567'))  "
     .appendText "AND f.emp_id = p.emp_id GROUP BY p.emp_id,f.ss_num, f.ou_id, l.ded_num, d.descr, p.gross_pay,  "
     .appendText "e.pct, f.last_name, f.first_name, f.middle_initial   "
     .appendText "HAVING SUM(NVL( l.amt, 0 )) <> 0  "
     .appendText "ORDER BY p.emp_id "
     .Parse
    End With
    SQLXL.Sql.Statements(1).Execute
    SQLXL.Sql.Statements(2).Execute
    Set SQLXL.Sql.Statements(3).Target = Targets(litExcel)
    SQLXL.Sql.Statements(3).OptimiseForLargeQuery = False
    With Targets(litExcel)
	  .AutoFilter = False
	  .AutoFit = True
	  .Headings = True
	  .Sort = False
	  .StartFromCell = "$A$1"
	  .Transpose = False
	  .SQLInNote = False
	  .ShowNote = False
	  .FormatData = True
	  .FreezePanes = False
	  .PasteInsert = False
    End With
    SQLXL.Sql.Statements(3).SubstVariables.Add "SAL_PR_PD_END_DATE"
    SQLXL.Sql.Statements(3).SubstVariables.Add "CO_ID"
    With SQLXL.Sql.Statements(3)
	  .ShowParametersDlg = False
	  .ShowResultsetDlg = False
    End With
    SQLXL.Sql.Statements(3).Execute
    SQLXL.Database.Disconnect
    
UserForm1.Show vbModeless

End Sub
 


 
My advice is to investigate 2 things.  
 
First remove the parameter temporarily. You use substitution variables and when mistyped they get inserted into the SQL and all kinds of errors can stem from it.  
 
The second thing I would recommend is to print the SQL text after you have finished building it up using the appedText calls. It is most convenient to do this using the debug.print command. It will print the SQL in the immediate pane of VB. Use:
Debug.print sqlxl.sql.statements(3).TextMy advice is to investigate 2 things.  
 
First remove the parameter temporarily. You use substitution variables and when mistyped they get inserted into the SQL and all kinds of errors can stem from it.  
 
The second thing I would recommend is to print the SQL text after you have finished building it up using the appedText calls. It is most convenient to do this using the debug.print command. It will print the SQL in the immediate pane of VB. Use:
Debug.print sqlxl.sql.statements(3).Text
Back to top
 
« Last Edit: 30.07.14 at 13:49:11 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1