Change the sql string of a dataprovider with VBA

[dospierredos]

I am trying to change using VBA the sql of a data provider (bodoc.DataProviders(i).sql),replacing some conditions with others. The query with those changes is bonewsql ,and when I assign it to the bodoc.sql, the data provider is automatically refreshed. I would like to change the sql without refreshing the data provider, as if I were pressing the button "save and close" in the query panel of the data provider. How can I do that?

Thanks,

 

[Sridharan]

Are you calling the Refresh Method??? If you are not calling it then it shouldn't be Refreshing. Copy / Paste the relevant code so that peers can have a look at it and give suggestions.

Sri

 

[dospierredos]

No, I am not calling the Refresh Method. I am just using this code:

Sub sql_change()

Dim bosql As DataProvider
Dim bonewsql As String
Dim bosqlresult As String
Dim bodoc As Document
Dim varname As String
Dim i As Integer
Dim j As Integer


Set bodoc = busobj.ActiveDocument

For i = 1 To bodoc.DataProviders.Count
Set bosql = bodoc.DataProviders(i)
bosqlresult = bosql.SQL
bonewsql = replace_sql(bosqlresult)
Next i

bosql.SQL = bonewsql 'this sentence refresh bosql

End Sub


where replace_sql is a function that replace all the strings like @variable('text') in bosqlresult with its value. For example, replace @variable('Date dd/mm/yyyy') with 14/10/2003. That function works correctly.
My objetive is to construct a new sql without prompts to send the document to the BCA (in Unix) with the document variable values selected by the user. All this process (to select variable values, to send to the BCA) is made with VBA, and until the sentence
bosql.SQL = bonewsql
the document is not refreshed.