Solved

Sql to Replace Folderpath string in MS Access Table field

Posted on 2016-11-30
7
50 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 52

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 52

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 46

Expert Comment

by:aikimark
ID: 41908094
Why is \\server\Root\2\SubFold\file3.doc not changed to \\server\Root\SubFold\file3.doc ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 52

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

623 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