Link to home
Start Free TrialLog in
Avatar of Sam OZ
Sam OZFlag for Australia

asked on

Sql to Replace Folderpath string in MS Access Table field

Hi Experts,

    I have an MS Access Table MYTable   which has the field MyPath

      The Sample entries in MyPath  (The values corresponding to a  folder structure that  has several subfolders
           \\server\Root\1\file1.pdf
           \\server\Root\2\file2.pdf
           \\server\Root\2\SubFold\file3.doc

         I need the  SQL to  modify the entries in  the MYPath  and all files will correspond to the root folder
             The Entries after modification will look

          \\server\Root\file1.pdf
          \\server\Root\file2.pdf
          \\server\Root\file3.doc

           All I need is the SQL  to modify the entries ( And not the actual physical moving of files)
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

will this good enough for you?

SELECT 
MyPath, "\\server\Root" & mid(MyPath, instrrev(MyPath, "\")) as newField
FROM MYTable;

Open in new window

or you can try to create a self-defined function:

Function generatePath(ByVal path As String, Optional ByVal SubDirectoryLevel As Integer = 2)
    Arr = Split(path, "\")
    If UBound(Arr) <= SubDirectoryLevel + 1 Then
        generatePath = ""
    Else
        For i = 0 To SubDirectoryLevel + 1
            generatePath = generatePath & Arr(i) & "\"
        Next
        generatePath = generatePath & Mid(path, InStrRev(path, "\") + 1)
    End If
End Function

Open in new window

then call your query like this:
SELECT 
MyPath, 
"\\server\Root" & mid(MyPath, instrrev(MyPath, "\")) as newField,
generatePath(myPath) as newField2
FROM MYTable;

Open in new window

Why is \\server\Root\2\SubFold\file3.doc not changed to \\server\Root\SubFold\file3.doc ?
Try this:
In a module:
Function SplitPath(varString As String)
    Dim b() As String
    b = Split(varString, "\")
    If UBound(b) > 2 Then
     SplitPath = "\\" & b(2) & "\" & b(3) & "\" & b(UBound(b))
    End If
End Function

Open in new window

In query:
SELECT SplitPath(MyPath) FROM MYTable ;

Open in new window

Avatar of Sam OZ

ASKER

Hi All,
    I am looking  SQL to modify the field ... Not Select statements
    ( VBA routines is also fine) . Finally the values in the field need to be changed as per the rule

    All the  files are in Root path . So folder paths are changed accordingly
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The field in query is modified as per rule. To update table:
UPDATE MYTABLE SET MyPath = SplitPath(MyPath)

Open in new window

Use with the following function in a module (repeated from previous comment):
Function SplitPath(varString As String)
    Dim b() As String
    b = Split(varString, "\")
    If UBound(b) > 2 Then
     SplitPath = "\\" & b(2) & "\" & b(3) & "\" & b(UBound(b))
    End If
End Function

Open in new window