Insering actual documents into a table 3

[migv1]

Greetings:

I'm working on a form that would let users insert an actual document into an OLE Object field of a table.

I can get the path and filename of the document using the FileDialog object, but how do you insert the actual object into the table programmatically?

I need users to insert one object per record, basically providing a command button for executing the menu commands:

Insert Object > Create from file > browse for file > display as icon

Thanks in advance for any suggestions.

 

[Remou]

You may like to look at:
Open Word, create and save document in Access table
thread702-1160634

 

[migv1]

Thanks, Remou!

I think I'm at least going in the right direction. Actually, what I need is a routine for saving an existing document into an Access table, no need to open any other programs.

I'm a little confused as to how to use
DoCmd.RunCommand acCmdInsertObject
to insert the file into the appropriate field once the path and filenames have been chosen using FileDialog.

Any suggestions will be greatly appreciated.

 

[Developer2U]

If you want to do this programmatically, this is one way to accomplish it. In this case I have hard-coded an actual Word document on the hard drive, but in your case, you can use the file explorer.

Private Sub cmdAdd_Document_Click()
    With Me.Document
        .Enabled = True
        .Locked = False
        ' Specify what kind of object can appear in the field.
        .OLETypeAllowed = acOLELinked
        ' Class statement        .Class = ".doc"
        ' Specify the file to be linked.
        ' Type the correct path name.
        .SourceDoc = "C:\MyDoc.doc"
        ' Range statement--optional 
        .SourceItem = ""
        ' Create the linked object.
        .Action = acOLECreateLink
        ' Optional size adjustment.
        .SizeMode = acOLESizeStretch
    End With

End Sub

 

[Remou]

Sorry migv1, forgot that post was about a new document.
To add to Developer2U's post, you can use:
.OLETypeAllowed = acOLEEmbedded
.Action = acOLECreateEmbed
to embed documents, though it is not recommended because of bloat.

 

[mp9]

You might like to look up the FileDialog object in Access VBA help to get a "file open" dialogue rather than hard-coding a file location, e.g.

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

http://www22.brinkster.com/accessory/

 

[migv1]

Thanks to everyone for the tips and code - I was able to accomplish my original goal, and now that I have, I can show my boss that keeping the actual documents in the database was not such a good idea. The Access (2002, BTW) file grows in size at an exponential rate, and I even get 'not enough memory' errors (even though I have 1GB of RAM) when trying to insert relatively small PDFs. And I only have 6 test records in there so far!

Anyway, plan B is to use the Access database to select the local file, copy it to a network drive, and keep the link to the saved file in a table along with other metadata relating to that file. This should be done separately for each file, i.e., no multiple file selection allowed.

Unfortunately, this far exceeds my current skills in Access VBA, and I must ask for help once more. Any suggestions?

Thanks again for all your help, and I hope everyone has a stress-free holiday season!

 

[Remou]

I guess MP9's code returns a string with a file name and path, which can be used to copy to the network directory. Say the return is StrFilePath:

'Reference Microsoft Office Object Library
Dim fso As Object
Dim strFileName
Dim strNetDir

strNetDir = "D:\Data\"
Set fso = CreateObject("Scripting.FileSystemObject")
strFileName = fso.GetFileName(strFilePath)
fso.CopyFile strFilePath, strNetDir & strFileName

Then strFileName can be stored to your database. An index can ensure or search can ensure that names are stored only once.

FollowHyperlink can be used to open documents.

This:
open a specific word doc from access
thread702-1162288
Has some code that loads a bunch of files with a few details into a database - you might like to look at it.