refreshing data from SQL different - solved

[jik_ff]

Well, as I expand on my current project, more offices are getting added, and no I have found a flaw in my data retrieval from SQL.

When the file is first run, it grabs the data from the SQL server with this statement:

Code Select Expand


    '********new link straight to SQL server*******************************************
    accessStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;"
    accessStr = accessStr & "Initial Catalog=CNRFloorPlan;Data Source=SQLserver;Use Procedure for Prepare=1;"
    accessStr = accessStr & "Auto Translate=True;Packet Size=4096;Workstation ID=TOR80000890S;"
    accessStr = accessStr & "Use Encryption for Data=False;Tag with column collation when possible=False"
   
    sqlSelect = "SELECT * " _
            & "FROM [SQLFloorPlanQuery] " _
            & "WHERE [SQLFloorPlanQuery].LocCode = '" & offCode & "';"
   
   
    'sqlSelect = "select * from ""CNRFloorPlan"".""dbo"".""SQLFloorPlanQuery"""
    '*********************************************************************************
   
    Application.ActiveDocument.DataRecordsets.Add accessStr, sqlSelect, 0, offCode & "-FloorPlanData"


the var offCode is pulled from an entry entered by the user at the start of the process.  This seems to work fine, only grabbing data based on the provided office code.

My problem is that when the file is re-opened, in the interest of not having the end user have to worry about much, I have it attempt to refresh the data from the server:

Code Select Expand


    intCount = ThisDocument.DataRecordsets.Count
    szBoxTitle = "Data Refresh Result"
    If intCount > 0 Then
        'There is data associated with this drawing.  Refresh!
        Set vsoDataRecordset = ThisDocument.DataRecordsets(intCount)
        Debug.Print vsoDataRecordset.DataConnection.fileName
        vsoDataRecordset.Refresh
        Debug.Print "Error No: "; Err.Number
       
        If Err.Number Then
            Err.Clear
            szMessageText = "ERROR-File cannot be reached:" + vbCrLf + vsoDataRecordset.DataConnection.fileName + vbCrLf
            szMessageText = szMessageText + "Please verify network connection and file location"
        Else
            'resolve any shared office conflicts
            Call CheckConflicts
            szMessageText = "All Data Records have been successfully refreshed.  Document SAVED."
            'Save document after refreshed
            Me.Application.ActiveDocument.Save
        End If
       
    Else
        'no linked data found.  Do not refresh or save
        szMessageText = "I've no Data Attached at " + vbCrLf + "   " & ThisDocument.Path
    End If

unfortunately, it seems that this does not seem to be keeping the WHERE clause in the refresh, and it starts to bring in all offices....  Is there a way to check the exact string it is using for the refresh, or will it just pull straight from the query in question?  If it's the latter, how can I rectify this?  I have already added a cell on the page that will store the Office code just in case that is needed.

[jik_ff]

ok, I think I found what I need, but need to find out how to set it through VBA.  When I checked the Refresh All>Configure Refresh, the area for Filter was blank.  I went into Change Data Source... and was able to add the filter, but would prefer to have the code set this when the Data Source is made.  I guess the WHERE clause does not populate the Filter area...

[jik_ff]

Could not find (as of yet) what I was looking for.  Would have been nice.  Did find this little gem:
Data Linking to a Stored Procedure
From <https://blogs.msdn.microsoft.com/chhopkin/2011/02/10/data-linking-to-a-stored-procedure/>

This allows me to make and use a stored procedure after the connection has been set up.  I am testing it out now, but I am assuming that I can continue to use my initial connection with the WHERE clause, then on data refreshes, use the Stored Procedure.

Will post my last findings when testing is done...

[Yacine]

As this is more a SQL server issue than a Visio one, you'd probably have more replies in a SQL server forum than here.

[jik_ff]

I was looking to do it with Visio really.  Was looking to see if it was possible to add a filter to the Data Source.

Well I found another way.  Similar to what Chris was talking about in that link.  Instead, I used the DataRecordSet's connection string to re-initiate the SQL command including the WHERE clause:

Code Select Expand


        Set vsoDataRecordset = ThisDocument.DataRecordsets(intCount)
        vsoDataRecordset.CommandString = "SELECT * " _
            & "FROM [SQLFloorPlanQuery] " _
            & "WHERE [SQLFloorPlanQuery].LocCode = '" & myLoc & "';"
        vsoDataRecordset.Refresh


Even though I did some playing around with my SQL server, I can kill all that.  Good exercise, but this is a bit more visible.