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)
Sam OZAsked:
Who is Participating?
 
Ryan ChongCommented:
I am looking  SQL to modify the field ... Not Select statements
do you mean using Update statement instead?

if yes, make sure you backup your data and then try like this:
Update MYTable Set MyPath = "\\server\Root" & mid(MyPath, instrrev(MyPath, "\"))

Open in new window

Or
Update MYTable Set MyPath = generatePath(MyPath)

Open in new window

0
 
Ryan ChongCommented:
will this good enough for you?

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

Open in new window

0
 
Ryan ChongCommented:
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

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
aikimarkCommented:
Why is \\server\Root\2\SubFold\file3.doc not changed to \\server\Root\SubFold\file3.doc ?
0
 
hnasrCommented:
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

0
 
Sam OZAuthor Commented:
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
0
 
hnasrCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.