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:
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:
Dim strSQLConnection As String
Dim strSQLStatement As String
Dim Q As Query
Dim S1 As New Spreadsheet
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)
Is there anything wrong with this?
Does anyone have working code for this kind of task?
Thanks for your input!
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: