SQL querry from macro - error on refresh - Statistica Visual Basic - Statistica - Dell Community

SQL querry from macro - error on refresh

SQL querry from macro - error on refresh

This question is not answered

 

Hi!

 

I am trying to use a SQL querry in a macro and am struggeling with the execution of the data refresh. When running the sample code (AccessQuery.svb) it doesnt work. I get the error message:

Exception: 80004005

The respective code line is: Q.Refresh(False)

with Q being the querry with connection and command string inside.

In my own code I get the exact same error. Even though I copied the connection details from a working query. I copied it out of the saved query using a macro. So it should have all the corrct values in correct formatting. Here is my full code:

'#Language "WWB-COM"

'Option Explicit

Sub Main
 Dim strSQLConnection As String
 Dim strSQLStatement As String
 Dim Q As Query
 Dim S1 As New Spreadsheet
 S1.SetSize(1,1)
 S1.Visible = True

 strSQLConnection = "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=myUser;Initial Catalog=myDB;Data Source=chhes2179;Use Procedure For Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=db_PC;Use Encryption For Data=False;Tag With column collation when possible=False;;Password=xyz"
 strSQLStatement = "SELECT * FROM dbo.T_RESULT_VE_NEARSCAN LIMIT 1"


 Set Q = S1.Queries.Add("MyQuery", strSQLConnection,1,1,strSQLStatement)
 Q.Refresh(False)

 S1.EntireRange.AutoFit

End Sub

 

Is there anything wrong with this?

Does anyone have working code for this kind of task?

Thanks for your input!

-Jean-

 

All Replies
  • I helped myself by creating a query in my table by using the statistica menu. Then I reuse the table and run the refresh in my macro by using:

    S.Queries.Item("myQuerryName").Refresh(False)

    cheers

    -Jean-