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
Re: Macro coding to automate batch insert (Read 7031 times)
liew
YaBB Newbies
*




Posts: 4
Re: Macro coding to automate batch insert
05.12.05 at 10:20:29
 
Thanks for your feedback.
 
Btw, does SQLXL.InsertRecordset command return a status code? Hence, if I assume all are for insertion records, and upon a bad status code, I could then do an update command (what similar command for update, is it SQLXL.UpdateRecordset??? Syntax please)?
Back to top
 
 
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Macro coding to automate batch insert
Reply #1 - 06.12.05 at 09:06:27
 
Dear Liew,
 
The output from the InsertRecordset command is that in the column to the right of the data a status value is put. Successful if the insert was ok and the database error message when the insert failed. This way you can find out which inserts failed.
 
The SQLXL.UpdateRecordset function does not exist. Updating is a more difficult issue as the row to update in the database needs to be found. There is nothing preventing you to code an update statement and to execute these through SQL*XL (just set it to the SQL.setText method and use the Statements collection to execute the individual statements). SQL*XL cannot do this generically because it does not know about your key columns.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
liew
YaBB Newbies
*




Posts: 4
Re: Macro coding to automate batch insert
Reply #2 - 07.12.05 at 11:12:43
 
Hi,
I am doing a batch manual checks for now. It works fine except when I try to do a sql update via SQLXL object, it gives me command error:
 
 sqlupd = "update prod01.fc_direct_int set forecast = '" & forecast & "', country = '" _
        & country & "', project_name = '" & projectname & "', base_technology = '" _
        & basetechnology & "', product_gp = '" & productgp & "', product = '" & Product & "', project_type = '" & projecttype & "'" _
 _
        & " WHERE forecast = '" & forecast & "' and country = '" _
        & country & "' and project_name = '" & projectname & "' and base_technology = '" _
        & basetechnology & "' and product_gp = '" & productgp & "' and product = '" & Product & "' and project_type = '" & projecttype & "'"
        
 
 SQLXL.Sql.setText Text:=sqlupd
        
        
        With Targets(litExcel)
            .AutoFilter = False
            .AutoFit = False
            .Headings = False
            .Sort = False
            Set .StartFromCell = Range("$A$2999")
            .Transpose = False
            .SQLInNote = False
        End With
        With SQLXL.Sql.Statements(1)
            '.ShowParametersDlg = False
            '.ShowResultsetDlg = False
            .Execute
        End With
 
 
If i msgbox the sql text that I am trying to update, it is as follow (not sure why it is illegal):
See attachment for screen shot.
Back to top
« Last Edit: 07.12.05 at 11:13:55 by liew »  

sqlxl_syntax_error.gif
  IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Macro coding to automate batch insert
Reply #3 - 07.12.05 at 13:35:01
 
You are using an older version of SQL*XL. I can see this from the changed syntax for this line:
 
Set .StartFromCell = Range("$A$2999")
 
This line has now become:
 
.StartFromCell = "$A$2999"
 
There was also a small problem with the SQL parser in SQL*XL that assigned SQL statements to the wrong type. It seems that your update statement was handled as a SQL*Plus statement. It should have been handled as a DML_DLL statement.
 
I suggest to get the very latest version of SQL*XL and try that. Please find the location of the latest version from the SQL*XL release log in the news forum:
http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1130772637
Back to top
 
 

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