Replace and Removing SQL VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rebelscum0000 - 26 Mar 2007 16:53 GMT

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

Reply to this Message

SusanV - 26 Mar 2007 17:06 GMT

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

Signature

hth,
SusanV

> Dear All
>
[quoted text clipped - 40 lines]
> Regards,
> Antonio Macias

Reply to this Message

rebelscum0000 - 26 Mar 2007 17:17 GMT

> 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

Reply to this Message

SusanV - 26 Mar 2007 17:43 GMT

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...

>> Probably be easier to wipe the table (DELETE from Tbl TempInfo_Tbl) and
>> repopulate it...
[quoted text clipped - 7 lines]
> Regards,
> Antonio Macias

Reply to this Message

rebelscum0000 - 26 Mar 2007 17:50 GMT

> 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

Reply to this Message

John W. Vinson - 26 Mar 2007 18:05 GMT

>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)
[quoted text clipped - 10 lines]
>
>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]

Reply to this Message

rebelscum0000 - 26 Mar 2007 19:52 GMT

> 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

Reply to this Message

Ken Sheridan - 28 Mar 2007 01:57 GMT

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

> Dear All
>
[quoted text clipped - 40 lines]
> Regards,
> Antonio Macias

Reply to this Message

rebelscum0000 - 28 Mar 2007 12:05 GMT

On Mar 27, 6:57 pm, Ken Sheridan
<KenSheri...@discussions.microsoft.com> wrote:

> Antonio :
>
[quoted text clipped - 8 lines]
> Ken Sheridan
> Stafford, England

Thank you, Ken this code works perfect to me

Regards,
Antonio Macias

Reply to this Message

Ken Sheridan - 28 Mar 2007 02:10 GMT

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

Reply to this Message