Results 1 to 3 of 3
  1. #1
    kdbailey is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Aug 2012
    Posts
    228

    Changing command text and connection without refreshing data

    I'm curious if it is possible to change the commandtext of an ODBC connection without the data refreshing?

    I'm looping through files and adjusting the directory of a specified source data.



    This will be adjusting many files, and the amount of time it takes to perform this would be greatly cut down if the data was not refreshed when the command text is changed.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,682
    ???? Perhaps an example would help.
    If you are changing the command text, are you not changing the data source or the criteria?

  3. #3
    kdbailey is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Aug 2012
    Posts
    228
    I'm just adjusting the target, as our IT team has changed the directory mapping. I just hope I can do this without the data refreshing at the moment to cut down on time.

    Code:
    Sub directorychange()
    Dim FileSystem As Object, HostFolder As String
    
    HostFolder = "S:\"
    
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)
    
    End Sub
    
    Sub DoFolder(Folder)
    Dim SubFolder, wb As Workbook
    
    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next
    Dim File
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    For Each File In Folder.files
        If InStr(1, File.Name, ".xl") <> 0 And InStr(1, File.Name, ".lnk") = 0 And (GetAttr(File.Path) And vbReadOnly) <> 1 Then
            Application.Workbooks.Open File.Path
            Set wb = Workbooks(File.Name)
            With wb
                If .Connections.Count <> 0 Then
                    For Each conn In .Connections
                        If conn.Type = 2 Then
                            conn.ODBCConnection.Connection = Replace(conn.ODBCConnection.Connection, "S:\Shared\", "S:\")
                            conn.ODBCConnection.CommandText = Replace(conn.ODBCConnection.CommandText, "S:\Shared\", "S:\")
                        End If
                    Next conn
                    wb.Save
                End If
            End With
            wb.Close
            Set wb = Nothing
        ElseIf InStr(1, File.Name, ".xl") <> 0 And InStr(1, File.Name, ".lnk") = 0 And (GetAttr(File.Path) And vbReadOnly) = 1 Then
        
        End If
    Next
    On Error GoTo 0
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-23-2014, 08:56 AM
  2. Refreshing Imported Text File Data Every X Minutes
    By acarella610 in forum Import/Export Data
    Replies: 5
    Last Post: 12-17-2013, 10:41 PM
  3. Replies: 1
    Last Post: 04-24-2013, 11:50 AM
  4. Replies: 1
    Last Post: 04-12-2013, 07:56 AM
  5. Changing SQL user pw causes ODBC connection issues
    By EEALLPARTS in forum Security
    Replies: 0
    Last Post: 02-28-2012, 11:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums