Solved

Sql to Replace Folderpath string in MS Access Table field

Posted on 2016-11-30
7
36 Views
Last Modified: 2016-11-30
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)
0
Comment
Question by:Sam OZ
7 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41908078
will this good enough for you?

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

Open in new window

0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 41908091
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
 
LVL 45

Expert Comment

by:aikimark
ID: 41908094
Why is \\server\Root\2\SubFold\file3.doc not changed to \\server\Root\SubFold\file3.doc ?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 30

Expert Comment

by:hnasr
ID: 41908116
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
 

Author Comment

by:Sam OZ
ID: 41908182
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
 
LVL 49

Accepted Solution

by:
Ryan Chong earned 500 total points
ID: 41908193
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
 
LVL 30

Expert Comment

by:hnasr
ID: 41908196
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

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now