Hi Darryl,
I have done something similar lately, and have discovered that the best way
(IMHO) to update a hyperlink to a file is to do so in code, wher you can
append "#file://" to the beginning of the hyperlink data.
I was importing a bunch of files that were all in one directory, and had
pretty much completely automated this routine.I used Terry Kreft's
BrowseFolder function
http://www.mvps.org/access/api/api0002.htm to select a
directory, then wrote the results of a Dir loop to a table that contained
the file names ("tblFileNames").
If you want (or need) to import these files on an individual basis rather
than importing the entire contents of a directory, you could use the File
Open/Save dialog box
http://www.mvps.org/access/api/api0001.htm to select a
single file, then modify my code accordingly. If you need help with this,
reply here, and I or someone else will give you a hand.
Notes:
This code was also much longer originally, and performed a whole bunch of
other conversion operations as well.
I have trimmed it down here so that it should be much closer to what I think
you are looking for.
I should also mention that this was a mainform / subform thing. The command
buttons were on the mainform, and the file hyperlinks were displayed in the
subform.
This code hunts for ".XLS" files, but could be easily changed to something
else.
**************************************************
Private Sub cmdImportMergeXL_Click()
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rstFiles As DAO.Recordset
Set rstFiles = MyDB.OpenRecordset("tblFileNames")
Dim MyDir As String
Dim MyFile As String
Dim MyPath As String
Dim FileSpec As String
Dim Msg As String
Dim intFC As Integer 'File Counter
intFC = 0
'Start by browsing for the drive/directory containing the XL files
'-----------------------------------------------------------------
MyDir = BrowseFolder("Find the directory containing the desired files")
FileSpec = MyDir & "\*.xls"
MyPath = MyDir & "\" & Dir(FileSpec)
MyFile = Dir(FileSpec)
'This section loops thru the files in the directory one at a time
'and adds the imported filenames to the table
'-----------------------------------------------------------------
Do While Len(MyFile) > 0
With rstFiles
.AddNew
!FilePath = "#file://" & MyPath & "#"
'This converts the filepath string to a hyperlink,
'which allows the user to open the XL file from a subform link
.Update
End With
MyFile = Dir 'Import the next XL file in the directory.
If Len(MyFile) > 0 Then
MyPath = MyDir & "\" & MyFile
End If
intFC = intFC + 1
Loop
Set rstFiles = Nothing
Set MyDB = Nothing
Msg = ""
Msg = Msg & intFC
Msg = Msg & " XL filenames have been imported."
MsgBox Msg
Me.Refresh 'Refreshes the subform that now contains the imported filenames.
End Sub
************************************************
You might also like to create some code that could (optionally )delete any
existing file paths from the table before appending these new ones.
****************************************
Private Sub cmdStartOver_Click()
Dim Msg As String
Dim CR As String
CR = vbCrLf
Msg = ""
Msg = Msg & "ARE YOU SURE that you want to erase everything?" & CR & CR
Msg = Msg & "This will delete all of the existing File Names from the
table... " & CR
Msg = Msg & "Which will mean having to re-import them."
If MsgBox(Msg, vbYesNo, "Confirm Deletion") = vbYes Then
Dim MyDB As DAO.Database
Set MyDB = CurrentDb
MyDB.Execute "DELETE tblFileNames.* FROM tblFileNames;", dbFailOnError
Me.Refresh
MsgBox ("ALL information has been succesfully deleted.")
Set MyDB = Nothing
End If
End Sub
****************************************
--
HTH,
Don
=============================
Use
My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)
I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop
================================
"Darryl Neale" <da****@allaboutgames.co.uk> wrote in message
news:bb**************************@posting.google.c om...
I have been tasked with reviving an old database that stopped working
about 3 years ago :(
On one of the tabs is a list of links to other databases utilising the
Hyperlink feature of MS Access and one of the problems with this
database is that all these files moved to new locations when a big
server "move round" occured about a month ago.
Whenever i right click the button that opens the page and select "Edit
Hyperlink" i get a new form with the hyperlink on it and this is what
i update however when i click OK it does not save this change and
reverts back to the original Hyperlink.
So...
How when and why does it happen?
How do i update the hyperlinks without deleting the entire form and
starting from scratch using my own methods to create links to other
files.
