I was able to solve this by modifying some VBA-code I found. In principle you have a vba-code, which remove the ole-object and then insert it again. I do this in a database each day. By doing this your are forcing access to update the ole-object.
I can not remember who made the original code, which I modified. But all the credits to that person. I have inserted the VBA-codes I use. I hope you can modify these to your needs.
Regards,
Torben
Option Compare Database
Option Explicit
Function GetLinkedPath(objOLE As Variant) As Variant
Dim strChunk As String
Dim pathStart As Long
Dim pathEnd As Long
Dim path As String
If Not IsNull(objOLE) Then
'Convert string to Unicode.
strChunk = StrConv(objOLE, vbUnicode)
pathStart = InStr(1, strChunk, ":\", 1) - 1
'If mapped drive path is not found, try UNC path.
If pathStart <= 0 Then pathStart = InStr(1, strChunk, "\\", 1)
'If either drive letter path or UNC path is found, determine
'the length of the path by searching for the first null
'character Chr(0) after the path was found.
If pathStart > 0 Then
pathEnd = InStr(pathStart, strChunk, Chr(0), 1)
path = Mid(strChunk, pathStart, pathEnd - pathStart)
GetLinkedPath = path
Exit Function
End If
Else
GetLinkedPath = Null
End If
End Function
Option Compare Database
Option Explicit
Sub SetOLEPath(objform As Form, OLEName As String, NewPath As String)
On Error GoTo ErrSetOLEPath
Dim objOLE As Variant
Dim tmpPath As String
Dim tmpClass As String
Dim iCount As Integer
Dim oldPath As String
Dim rs As Recordset
oldPath = ""
Set rs = objform.Recordset
rs.MoveFirst
Do
objOLE = objform(OLEName)
'Get the current path for the linked OLE object.
oldPath = GetLinkedPath(objOLE)
'Determine the file name from the current path.
If oldPath <> "" Then
iCount = 0
tmpPath = oldPath
Do
iCount = InStr(tmpPath, "\")
If iCount > 0 Then
tmpPath = Mid(tmpPath, iCount + 1)
End If
Loop Until iCount = 0
End If
'Set the new path and file name for the OLE object.
With objform(OLEName)
'OLE object must be enabled and unlocked for modification to take place.
.enabled = True
.Locked = False
tmpClass = .Class
'Remove the current object; otherwise, the object cannot be changed.
objform.Recordset.Edit
objform.Recordset(objform(OLEName).ControlSource) = ""
objform.Recordset.Update
.OLETypeAllowed = acOLELinked
.Class = tmpClass
'Put in the new path and file name.
.SourceDoc = oldPath
'.SourceDoc = NewPath & IIf(Right(NewPath, 1) = "\", "", "\") & tmpPath
'Create the actual link
.Action = acOLECreateLink
.SizeMode = acOLESizeZoom
End With
'Move to the next record.
rs.MoveNext
Loop Until rs.EOF
Exit Sub
ErrSetOLEPath:
MsgBox Error
MsgBox Err
Resume Next
End Sub
Option Compare Database
Option Explicit
Function UpdateOLE1(FormName As String, OLEName As String, NewPath As String)
'?UpdateOLE("frmFormular", "MetaStock1", "<New path to linked files>")
'.FilterOn = True
' DoCmd.ApplyFilter , "[TMS_DATE] >= " & DateValue(!txtWkStart) & "
Dim myForm As Form
Dim oldPath As String
Dim rtn As Variant
'Open the form if it is not already open.
DoCmd.OpenForm FormName, acNormal, , , acFormEdit
DoCmd.ApplyFilter , "(MetaStock1<>'') "
'DoCmd.ApplyFilter ,"(status_probleem = 'open') "
'Bring the form to the front if it is currently behind other objects. FEJL I SET MYFORM
DoCmd.SelectObject acForm, FormName
Set myForm = Forms(FormName)
'Call the subroutine to make changes to the OLE object in the form.
SetOLEPath myForm, OLEName, NewPath
DoCmd.Close acForm, FormName
End Function
