Having trouble with OLE - Help please 3rd time asking - No respons

[David O''Malley]

I have a note table that I want users to be able to attach or link to a
number of different objects (1 object per Record). Meaning they may need to
attach a video or a graphic or a document to the note. I set up the field as
an OLE Object (Imbedded, which I need to change.) within my table.

When I am at my home I right click and browse to the object and it inserts
it as a package. I am then able to view the object by double-clicking on it.
I then transfered a copy of the DB to my office and. . .

When I am in the Office and I browse to a jpg or anythin and click Ok. the
system just freezes. It does not matter what the object is mov, jpg,
realplayer, doc, xls, anything. Access just freezes and has to be restarted.

I also realized I do not want to imbed the object within the database I want
them to be stored in a sub directory so that the DB does not get to big.

So the two things I need help with are:

1) Why would it freeze here and not at my home?

2) Is there away to have them select the object and have access copy ot the
subdirectory and set a link in the DB to it?

Any help would be greatly appreciated.

 

[bob]

Fixing 2) (by avoiding OLE) will likely fix 1).

Below are some tips. Most of these relate primarily to images, but can be applied to any type of file
(adjust file-extensions accordingly, and ignore code related to Image Controls).

a) Provide a way for users to select a file:
Display the common ‘File-Open’ dialog:
http://www.ammara.com/access_image_faq/file_open_dialog.html


b) Implement a file-naming scheme
You’ll need a way to relate a file to a specific record, and to ensure uniqueness of filenames. A good
way to do this is to use the value from an Autonumber field as the filename:
http://www.ammara.com/access_image_faq/autonumber_file_names.html

If you adopt the Autonumber file-naming scheme, then since you need to support multiple file-types you’ll
need to parse the extension from the full path when the user selects a file, and store the extension in
a field. The following FAQ shows how to parse a filename from a full path – you can adapt this to get
just the extension:
http://www.ammara.com/access_image_faq/parse_path_filename.html


c) Once a file has been chosen it needs to be copied to a location that all users can access. A good option
is to use a path relative to the database location, then the files are accessible whether the database
is opened via a local path, mapped drive or UNC path, without any further configuration and without hard
coding or storing full paths. http://www.ammara.com/access_image_faq/image_relative_paths.html

When you are copying the file to the storage location, and subsequently opening it, you’ll need to build
the path by getting the database location then appending the relative part of the path and the filename.
There are various ways to get the database location, depending on the version(s) of Access being used,
and whether or not the database is a split (Front-End/Back-End) design:
http://www.ammara.com/access_image_faq/get_mdb_database_path.html

For example, if you use one of the implementations above to get the database path, and you store the files
in a subdirectory named ‘files’, and use the value of an autonumber field named ‘Id’ as the filename, and
store the extension (without the ‘.’) in a field named ‘Extension’ then you would generate the path as
follows:

Dim FilePath As String
FilePath = GetDBPath & "files\" & Me!Id & "." & Me!Extension

This is the path to use as the copy destination when adding a file, and as the target when opening the
file for display.


d) Provide a way for the user to open the file using the associated application on his system:
http://www.mvps.org/access/api/api0018.htm


e) When records are deleted you may want to clean-up the associated files, to prevent ‘orphaned’ files
building-up:
http://www.ammara.com/access_image_faq/image_file_deletes.html


Are you certain that only one file will ever need to be associated with each record? If it might be useful
(either now or in the future) to support more than one file then design for this now by using a separate
table for the file information, using a one-to-many relationship with the master table.

 

[David O''Malley]

Thank you Bob!,

This looks like it will be able to handle what I need to do. I will be
workign on it today and I will let you know the result! thank you again for
responding and for the clear instructions.

 

[David O''Malley]

Hi Bob,
I ran into a glich. I used the code as described and when I ran just to
test the `File-Open' Dialog I get "Compile error: sub or function not
defined" on ahtAddFilterItem and when I remove the filters and tried to just
set the strFilter to *.* then I get the same error on ahtCommonFileOpenSave.
Do I need to enable that functionality some how? I checked the VB help in the
Code Builer and can not find referance to either.

Thank you in advance

 

[bob]

It sounds like you haven't added the common code to a module, as described in the 'Usage' section of the
article. Could this be the cause?

 

[David O''''Malley]

What Module would I put that in? Just in the begining of the code for that
form?

 

[bob]

The description is "paste the entire contents into a new module":

Click 'Modules'.
Click 'New'.
Paste the contents of the code file.

 

[David O''''Malley]

Well that was getting a little to complicated or conveluted for me. The
original articles that you had suggested were a great help and got me started
in the right direction.

Here is the code that I created. It lets you select a file, create a new
unique filename for the file, copy the file to the subdirectory of the
current database

Dim FileOpen As FileDialog
Dim SelectedFile As String
Dim SavePath As String
Dim Filename As String
Dim FileExtention As String
Dim NewFileName As String


Set FileOpen = Application.FileDialog(msoFileDialogOpen)
Dim vrtSelectedItem As Variant

With FileOpen
.AllowMultiSelect = False
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems


SelectedFile = Right(vrtSelectedItem, Len(vrtSelectedItem) -
InStrRev(vrtSelectedItem, "\"))
'MsgBox "The file you selected is: " & vrtSelectedItem
SavePath = CurrentProject.Path & "\Attachments\"
Filename = [BugID] & "_" & [BugNoteID]
FileExtension = "." & Right(SelectedFile, Len(SelectedFile) -
InStrRev(SelectedFile, "."))
NewFileName = SavePath & Filename & FileExtension
FileCopy SelectedFile, NewFileName
MsgBox "Your file " & SelectedFile & " was successfully copied
to " & Filename
Next vrtSelectedItem
Else
End If
End With

Now I have to figure out how to set a hyperlink location in the OLE field, I
can set the text but not the actual link through the codebuilder.

Thank you for your help! If you know how to assign the hyperlink address to
a text box or OLE field That would be great.