How can I replace every occurrence of a drive letter with a different drive letter in a table in an Acess database?

April 17, 2004 6:49 PM

Say I have a database DATA.MDB
Inside DATA.MDB there is a table called ImageTable.
In this table is a field named fimageOriginalFilePath. There are aprox 4000 entries.
Each entry holds an absolute path to a directory on the C drive. For example C:\pictures\10131009
I want to automatically replace the first character of each entry with the letter F. So that each path points to the F drive.
Can it be done? Can it be done without MS Access? Can it be done by a person with no programing skills, knowledge of databases, or understanding of SQL queries and the like? If so, how? Thank you all.

Why are all the google ads about social security? Shouldn't they be about database programming?

posted by Grod to computers & internet (14 comments total)

If you don't have Access or some other way of manipulating an MDB file, it's going to be pretty hard to do it. If there's nothing private in it, I'm sure there are people here that would do it for you. (I would--email me if interested.) It would take me about 30 seconds to do it.
posted by AstroGuy at 7:26 PM on April 17, 2004

Actually, chances are you can just do a binary-mode search/replace on the file from 'C:\' to 'F:\' and have it still work; The histograms and other metadata will be wrong, but chances are everything will still be in good enough shape to have whatever tool generated it in the first place reexport it properly.

You didn't say what kind of database it was btw, but judging from your post and AstroGuy's I'm assuming you have a file DATA.MDB containing a Microsoft Access database.

This might be useful too.
posted by fvw at 7:34 PM on April 17, 2004

If you have access to the table it's dead easy: Place your cursor in the field -- go to Edit -> Replace ...

In the dialogue that pops up make sure that the 'Look In' field is correct and that the Match option is 'Any Part of Field.' In Find What type 'C:\' without the quotes. In Replace With type 'F:\' without the quotes. Hit Replace All, then Yes.

Just tested this to double-check, so I know it works.

Oh, just 'cause I'm paranoid when it comes to Access ... make a backup of that table before you start.
posted by NsJen at 7:35 PM on April 17, 2004

Oh, should have mentioned those instructions are for Access 2K ... if you have a different version there might be some slight differences but I can't check until I get to work on Monday (where I have every version known to man installed on my machine, I think). Ought to be pretty similar anyway.
posted by NsJen at 7:40 PM on April 17, 2004

Why are all the google ads about social security? Shouldn't they be about database programming?

Social Security, Access security, seems about right to me. ;-)
posted by NsJen at 7:43 PM on April 17, 2004

AstroGuy, I appriciate the offer. It's an 18mb file, so e-mail seems impractical. I can set up an ftp server though. I have downloaded a few tools that enabled me to get the information I provided in the post. These are:

BlueShellDataGuy
PrimeLogic--Prime Vision
PrjGenericMDB

None of them appear to have the functionality NsJen describes. The program that created the database is Adobe Photoshop Album. As near as I can tell there's nothing proprietary going on, as changing the file extension from .psa to .mbd allowed me to open it with each of the three programs listed above. Excell also had no problem extracting tables from it. It appears to be Jet 4, whatever that means.

fvw A binary-mode search replace? How would I do that?
posted by Grod at 8:34 PM on April 17, 2004

Jet 4 is the ODBC driver required to read data in an Access database (up to Access 2000, at least - not sure if the most recent Access versions are Jet 4).

What NsJen is describing is how you'd edit the table using MS Access. It seems you're trying to edit table data in an .mdb without using Access, yes? I suppose you could consider writing a .vbs to run in the Windows script host to do it in that case. Without access to Access, however, it might be difficult to verify success, however. Your best bet is to get someone with MS Access to edit it - it's a one minute job.
posted by normy at 11:34 PM on April 17, 2004

Use an update query. Drop the field you're updating into the qbe grid and then in the Update To box put the following:

"F" & Right([fimageOriginalFilePath],Len([fimageOriginalFilePath])-1)

As always do a back up first.

Just to explain, Right returns the specified number of right-most chars. Here I've used Len so the number specified is always one less then the total number of chars in the string
posted by dodgygeezer at 10:51 AM on April 18, 2004

Grod: any of these Windows hex editors should let you replace one string with another throughout a binary file.
posted by nicwolff at 11:21 AM on April 18, 2004

Can it be done by a person with no programing skills, knowledge of databases, or understanding of SQL queries and the like?

No.
posted by the fire you left me at 2:38 PM on April 18, 2004

Thanks for the encouragement, fire.
nicwolff, Thanks, that's what I ended up doing, it appears to have worked.
Dogeygeezer, I tried doing what you said, but I kept getting syntax errors. So I used a hexeditor instead. Thanks to everyone.
posted by Grod at 4:46 PM on April 18, 2004

dodgy: "F" & Mid([fimageOriginalFilePath],2)

Just a little bit simpler.

Grod: Glad you got it.
posted by AstroGuy at 6:43 PM on April 18, 2004

Hey Grod, you might want to keep your backup for a while just to be on the safe side. Rooting around in .mdb files with a hex editor sounds like something that might cause file corruption in an Access file, the things are easy enough to corrupt under normal use. I'd keep an eye on it, and maybe run Compact and Repair. Of course I don't know exactly what the replacement procedure was like, nut beware of unintended consequences.
posted by crunchburger at 7:03 PM on April 18, 2004

crunchburger. That occured to me after I deleted my backup. Sigh. No problems so far, though. The replacement proceudre was simple. I used winhex and did a search and replace on the unicode string "C:\Pictures" and replaced it with "F:\Pictures". This was the only way I could think of to avoid replacing every instance of the letter c with the letter f. It appears to have worked perfectly. I have since done various manipulations to the database through Adobe Photoshop Album (adding images, deleting images, adding tags (tags are what make psa so great, rather than caring about the directory structure I just make a tag called Cat and associate it with every picture of my Cat. If I also have a tag called Girlfriend associated with every picture of my girlfriend, then doing a search for "Cat" AND "Girlfriend" returns only the pictures containing both my cat and my girlfriend.)) and so on), and it hasn't encountered any errors, so I don't think I fucked anything up.

Thanks again for all the help.
posted by Grod at 12:18 PM on April 19, 2004