File location changing

[Russ]

I've got a Access 2000 database that saves some photo locations in a
table. When this table is moved to another machine, frequently the
path has changed sometimes including the drive letter etc. I can
change it manually in the form, going from one record to the next and
so forth, but with many records it gets difficult. Does anyone know
of another way to accomplish this... maybe with an update query? I
tried to change my table with a update query but since each record was
unique it didn't work.

Anyone try this before?

Russ

 

[Dirk Goldgar]

I've got a Access 2000 database that saves some photo locations in a
table. When this table is moved to another machine, frequently the
path has changed sometimes including the drive letter etc. I can
change it manually in the form, going from one record to the next and
so forth, but with many records it gets difficult. Does anyone know
of another way to accomplish this... maybe with an update query? I
tried to change my table with a update query but since each record was
unique it didn't work.

Anyone try this before?

You *can* use an update query, but the query must parse out the old
folder path and replace it with the new one. Something like

UPDATE MyTable
SET PhotoFile =
Replace(PhotoFile, "C:\Old Path\", "C:\New Path\")

As originally released, unfortunately, Access 2000 couldn't resolve the
Replace function in queries, so you may have to use a wrapper function
in the query instead; e.g., have a function like this:

Function fncReplace( _
pExpression As Variant, _
pFind As Variant, _
pReplace As Variant) _
As Variant

fncReplace = Replace(pExpression, pFind, pReplace)

End Function

.... and use it in place of Replace() in your update query.

*However*, if all the photos are going to be in the same folder, I would
just store the path to that folder once, in a field in a one-record
Profile table, and store only the file names in the original table.
Then, when I wanted to retrieve a photo for display, I'd look up the
PhotoFolder value from the Profile table and prefix it to the filename.
That way, I'd only ever have to change the folder path in one place,
which would be a lot easier to do.

 

[Russ]

Good idea on the new table with one record. But I don't think I've
ever prefixed any item from one table to a value in another table.
How is that done?

Russ