Updating linked file fields

Question:

I have created a database of graphics using Datapower 2. It contains data which pupils can search to find appropriate pictures. These are not stored in the database (over 3000 pictures) but referenced via a directory folder field which when clicked on opens the appropriate directory. The reference is CDNET::$.pictures.....etc.

I want to transfer the database and contents to another drive / filing system but have found that the file reference is specific to that drive and system. Is there a way of modifying all references so I can get it to look at a new drive? I will need to change the reference to an alternative filing system - is this possible or do all the references need to be changed by hand?

Answer:

Yes, it certainly is possible to do this using a script.

There are two main functions you need:

FileName()    // returns the name of a linked file
FileLink()    // creates a link to a file from a name

The following script replaces all occurrences of a given string within file links in a given field of a given table with another string:

ReplaceLinks("Table1","MyFileField","CDNET::$.pictures","NEWDISC::$.Pictures")

assuming:

• Table1 is the name of the table (this is the default in a flat file)
• MyFileField is the name of your field in this table
• CDNET is the old disc name
• NEWDISC is the new disc name

In order for this to work, you first need to copy and paste the following script into the global scripts of your database. To do this, edit the layout and choose Edit Scripts from the File menu.

Sub ReplaceLinks ( tname as text, fname as text, old as text, new as text )
Dim r as recordset, f as field, link as text
r = CurrentDB.QueryDef(tname).OpenRecordset
f = r.Field(fname)
Dim ok as boolean
ok = r.MoveFirst
While ok
  link = FileName(f.GetValue)
  if link <> null then 
    link = replace(link,old,new)
    f.SetValue(FileLink(link))
    r.Update
  endif
  ok = r.MoveNext
EndWhile
EndSub

Having done this, you can create a background frame somewhere on your main layout (using the frame tool in the toolbox, drag out a rectangle to create the frame, then click the text tool and click in the frame to enter a label (eg. "Convert links")).

Then select your background frame button and press Ctrl-T, then enter the following script:

On ClickLeft
  ReplaceLinks("Table1","MyFileField","CDNET::$.pictures","NEWDISC::$.Pictures")

You'll need to alter the table and field names as appropriate, of course.

If you wanted to transfer a database like this onto a PC, you'd also need to perform a similar type of conversion, as the PC is not going to understand absolute file paths designed for an Acorn (relative paths are OK, however).

As an example, if your pictures moved from an absolute location on a separate drive to a directory next to the database, you could use:

On ClickLeft
  ReplaceLinks("Table1","MyFileField","CDNET::$.pictures","Pictures")

In this case the file links would remain in Acorn format, which is OK as long as they're relative to the database.

This would then work in mixed platform client/server mode as well, since any Acorns using the file would append the file links to an Acorn absolute path, whereas any PCs would append the relative path name to a PC absolute path.