Sam OZ
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\fi le3.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)
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\fi
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)
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
then call your query like this:SELECT
MyPath,
"\\server\Root" & mid(MyPath, instrrev(MyPath, "\")) as newField,
generatePath(myPath) as newField2
FROM MYTable;
Why is \\server\Root\2\SubFold\fi le3.doc not changed to \\server\Root\SubFold\file 3.doc ?
Try this:
In a module:
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
In query:SELECT SplitPath(MyPath) FROM MYTable ;
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The field in query is modified as per rule. To update table:
UPDATE MYTABLE SET MyPath = SplitPath(MyPath)
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