|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
February 2nd, 2007, 03:32 AM
|
||||
|
||||
|
Efficient Find and Replace Method on Access DB
Hi All,
I would appreciate any ideas, tips or code!!! I have been asked to write a simple little program using VB6 which opens an Access DataBase and does a Find and Replace on a particular column. This particular column stores pathnames which contain backslashes (\) and I need to replace them with forward slashes (/). I could obviously just write a simple program which selects every line from the database one at a time and then use the VB Replace function to substitute the slashes, then write the new string back to the database, but there could potentially be 1000's of records in the DB so this method will be very slow and use unnecessary resources. I would be grateful if anyone could suggest an efficient method of doing a find and replace within an Access table from a standalone VB6 program? Thank you very much in advance!
|
|
#2
February 2nd, 2007, 04:07 AM
|
||||
|
||||
|
why not just run a once of query?
Code:
update theTable set theColumn = replace(theColumn, "\", "/") this would update the whole database in one shot. hope this helps
__________________
Look! Its a ShemZilla    
|
|
#3
February 2nd, 2007, 06:01 AM
|
||||
|
||||
|
Nofriends,
Thankyou very much for the quick reply!! I do not have access to the database itself so I need to write a standalone VB app. which performs the find and replace externally. Am I right in assuming that the REPLACE function will not work externally? If I run the following code I get the error 'Undefined function 'REPLACE' in expression': Code:
Private Sub Form_Load() Set cn = New ADODB.Connection strDBPath = "C:\Test.mdb" With cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source= " & strDBPath .CursorLocation = adUseClient .Open End With strSQL = "UPDATE ChangeLog SET fileName=REPLACE(fileName,'\','/')" cn.execute (strSQL) cn.Close End Sub Thanks once again!
|
|
#4
February 2nd, 2007, 06:24 AM
|
||||
|
||||
|
yeah, its seems like the external provider doesn't support that
function. seems like you will have to loop through the records set. not sure if there is a better way, sorry...
|
|
#5
February 2nd, 2007, 06:26 AM
|
||||
|
||||
|
Thank you very much for your time anyway!!
|
|
#6
February 5th, 2007, 08:58 AM
|
||||
|
||||
|
Quote:
Nofriends, Thank you once again for your time, unfortunately, after much research I have come to the same conclusion as you: the only way to achieve what I want is to loop through the recordset!! Although this seems a little bit clunky it seems to work, I have tested it on a table that contains 10,000 rows and it completes almost instantly. My problem is that I have now tried to extend the program so that instead of a pre-defined column, I want the user to be able to select a database from an Open Dialog, select a table from a dropdownlist (populated using the TableDefs property of the chosen DB), then finally select a field from a dropdown list (populated using the OpenSchema function of an ADODB Recordset) and perform the find and replace on the chosen field. I have tested the following code and it seems to work in most cases: Code:
strSQL = "select distinct " & Combo2.Text & " from " & Combo1.Text
Dim strCurrentLine As String
strCurrentLine = ""
With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockOptimistic
.Open strSQL
End With
With rs
.MoveFirst
While Not rs.EOF
strCurrentLine = rs.Fields(0)
If InStr(1, strCurrentLine, Trim(Text2.Text)) Then strCurrentLine = Replace(strCurrentLine, Trim(Text2.Text), Trim(Text3.Text))
.Fields(0).Value = strCurrentLine
.Update
.MoveNext
Wend
.Close
End With
cn.Close
However, the code produces the following error when duplicate rows exist in a table: "key column information is insufficient or incorrect. too many rows were affected by update" It seems that the way around this is to ensure that there is a primary key defined. I was wondering if there was another way of working with the recordset, i.e. to do all of the find and replace operations and then just perform an Update at the end, irrespective of whether duplicate rows exist!!! Any ideas appreciated!! Last edited by sync_or_swim : February 5th, 2007 at 09:00 AM. Reason: Forgot the code tags!!
|
|
#7
February 6th, 2007, 01:06 PM
|
||||
|
||||
|
try using STRTRAN instead of replace ... using your first posted code
Code:
Private Sub Form_Load() Set cn = New ADODB.Connection strDBPath = "C:\Test.mdb" With cn .Provider = "Microsoft.Jet.OLEDB.4.0" .ConnectionString = "Data Source= " & strDBPath .CursorLocation = adUseClient .Open End With strSQL = "UPDATE ChangeLog SET fileName=STRTRAN(fileName,""\"",""/"")" cn.execute (strSQL) cn.Close End Sub
__________________
Come JOIN the party!!! Quote of the Month: Retirement: Because you've given so much of yourself to the company that you don't have anything left we can use. Questions to Ponder: What do you do when you see an endangered animal eating an endangered plant? iif([sarcasm]=true,iif([you have to ask]=true,"didn't work","ha ha ha"),"not sarcasm") copyright© 2008 sbenj69
|
|
#8
February 7th, 2007, 04:06 AM
|
||||
|
||||
|
Mehere,
Thank you for your suggestion, unfortunately it gives the same error as when I use REPLACE: "Unknown function STRTRAN" I will keep experimenting with it though and keep you posted, thanks again.
|
|
#9
February 7th, 2007, 03:56 PM
|
||||
|
||||
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > Efficient Find and Replace Method on Access DB |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
