Replace and Removing SQL VBA

[rebelscum0000]

Dear All

How can I construct a Query in order to replace all the records from
my Tbl TempInfo_Tbl:

DIR Dups V.1.0\
DIR Dups V.1.0\bas\2006\
DIR Dups V.1.0\bas\2006\Automation\
DIR Dups V.1.0\bas\2006\Backup 12-24-06\
DIR Dups V.1.0\bas\2006\Backup 12-26-06\
DIR Dups V.1.0\bas\2006\Backups\
DIR Dups V.1.0\bas\2007\
DIR Dups V.1.0\mdb progress\Duplicates 2006\
DIR Dups V.1.0\mdb progress\Duplicates 2007\
DIR Dups V.1.0\mdbs\Access Web\November 6, 2000\
DIR Dups V.1.0\mdbs\Access Web\November 6, 2000\accwebfaq-10-10-00-A9\
DIR Dups V.1.0\mdbs\ACG Soft\Browse Dialogs\
DIR Dups V.1.0\mdbs\ACG Soft\Browse Dialogs\Info_Browse Dialogs\
DIR Dups V.1.0\mdbs\ACG Soft\Browse Dialogs\Zip_Browse Dialogs\
DIR Dups V.1.0\Reposteria\Access\
DIR Dups V.1.0\Reposteria\Outlook\
DIR Dups V.1.0\Visual\Test\
DIR Dups V.1.0\xls examples\
DIR END\RECORDS\

FROM DIR Dups V.1.0\ TO C:\Dups V.1.0\ leaving the path after the
first \ (Reverse Solidus) AND removing the last \ (Reverse Solidus)
Example:

Actual:

DIR Dups V.1.0\mdbs\Planet Source\Dups Remover Non Dups Finder\Dup Edit
\

Replace::

C:\Dups V.1.0\mdbs\Planet Source\Dups Remover Non Dups Finder\Dup Edit

Is this possible in one step?

Thanks in advance

Regards,
Antonio Macias

 

[SusanV]

Probably be easier to wipe the table (DELETE from Tbl TempInfo_Tbl) and
repopulate it...

 

[rebelscum0000]

Probably be easier to wipe the table (DELETE from Tbl TempInfo_Tbl) and
repopulate it...

I can not delete the records of the Tbl because the source is a TXT
file, I will have to change all my code

Any other idea?

Thanks in advance
Regards,
Antonio Macias

 

[SusanV]

This is a linked table? If so, and the end result is to replace the text
file, import the txt file, wipe it, then export it back out to the txt file
after repopulating it...

 

[rebelscum0000]

This is a linked table? If so, and the end result is to replace the text
file, import the txt file, wipe it, then export it back out to the txt file
after repopulating it...

No, is not a linked table and the txt file does not have and "end
of result" or "DIR END\RECORDS\ this last records is there due cos
this code

MyLastID = amcrs.Fields("ID")
'Adds 1 to Counter ID
MyLastID = MyLastID + 1
MyLastRec = "DIR END\RECORDS\"

With amcrs

.MoveLast
.AddNew 'Add a new record
!ID = MyLastID
!DirHashFiles = MyLastRec
.Update 'Write the new record to the table
.Bookmark = .LastModified

End With

What other option do I have?

Regards
Antonio Macias

 

[John W. Vinson]

FROM DIR Dups V.1.0\ TO C:\Dups V.1.0\ leaving the path after the
first \ (Reverse Solidus) AND removing the last \ (Reverse Solidus)
Example:

Actual:

DIR Dups V.1.0\mdbs\Planet Source\Dups Remover Non Dups Finder\Dup Edit
\

Replace::

C:\Dups V.1.0\mdbs\Planet Source\Dups Remover Non Dups Finder\Dup Edit

Is this possible in one step?

Yes: update to

Left(Replace([fieldname], "DIR Dups V.1.0\", "C:\Dups V.1.0\"),
Len(Replace([fieldname], "DIR Dups V.1.0\", "C:\Dups V.1.0\")) - 1)

John W. Vinson [MVP]

 

[rebelscum0000]

Yes: update to

Left(Replace([fieldname], "DIR Dups V.1.0\", "C:\Dups V.1.0\"),
Len(Replace([fieldname], "DIR Dups V.1.0\", "C:\Dups V.1.0\")) - 1)

John W. Vinson [MVP]

WOOOOOO! Perfect Thank you so much!!

Dim sQL18 As String
Dim MyTempInfoCount As Integer
Dim MyTempInfoID As Integer
Dim MyDirFolder As String
Dim MyFilesFound As Integer
Dim U As Integer
'Initialize Variables
MyTempInfoCount = DCount("*", "TempInfo_Tbl")

Set smadb = CurrentDb
Set smars = smadb.OpenRecordset("TempInfo_Tbl", dbOpenDynaset)

For U = 1 To MyTempInfoCount

MyTempInfoID = smars.Fields("ID")
MyDirFolder = smars.Fields("DirFolder")
MyReplaceDirFolder = Left(Replace(MyDirFolder, "DIR Dups V.
1.0\", "C:\Dups V.1.0\"), Len(Replace(MyDirFolder, "DIR Dups V.1.0\",
"C:\Dups V.1.0\")) - 1)
MyFilesFound = smars.Fields("FilesFound")

sQL18 = _
"UPDATE TempData_Tbl " & _
"INNER JOIN TempInfo_Tbl " & _
"ON TempData_Tbl.DirHashFiles = TempInfo_Tbl.DirFolder SET
TempData_Tbl.FilesinDir = " & MyFilesFound & " , " & _
"TempData_Tbl.DirPathOnly = '" & MyReplaceDirFolder & "' "
& _
"WHERE TempInfo_Tbl.ID= " & MyTempInfoID & " AND
TempInfo_Tbl.DirFolder Like 'DIR*' "
Debug.Print sQL18
CurrentDb.Execute sQL18, dbFailOnError

smars.MoveNext

Next U

smars.Close
smadb.Close
Set smadb = Nothing

Regards,
Antonio Macias

 

[Ken Sheridan]

Antonio :

Call the Replace (to replace the substring) and the LEFT function to trim
off the final backslash character, using an update query, e.g.

UPDATE Tbl TempInfo_Tbl
SET [YourField] = REPLACE(LEFT([YourField],LEN([YourField])-1),"DIR Dups
V.1.0","C:\Dups V.1.0")
WHERE LEFT([YourField0,14) = "DIR Dups V.1.0";

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

Antonio :

Sorry, typo. Should have been:

UPDATE Tbl TempInfo_Tbl
SET [YourField] = REPLACE(LEFT([YourField],LEN([YourField])-1),"DIR Dups
V.1.0","C:\Dups V.1.0")
WHERE LEFT([YourField],14) = "DIR Dups V.1.0";

Ken Sheridan
Stafford, England

 

[rebelscum0000]

Antonio :

Call the Replace (to replace the substring) and the LEFT function to trim
off the final backslash character, using an update query, e.g.

UPDATE Tbl TempInfo_Tbl
SET [YourField] = REPLACE(LEFT([YourField],LEN([YourField])-1),"DIR Dups
V.1.0","C:\Dups V.1.0")
WHERE LEFT([YourField0,14) = "DIR Dups V.1.0";

Ken Sheridan
Stafford, England

Thank you, Ken this code works perfect to me

Regards,
Antonio Macias