How to Link a file instead of embeding in Access Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have seen searching and posting for help for the last 7+ days. I have not
recieved any help so far.

I hope this isn't a dumb question!!! I also tried the knowledge base, etc
with on information on reconnecting links. Could I use that if I put a OLE
object with a broken link and reconnect???

I am trying to link a folder with individual files into a table record by
record using code. There is an OLE Object defined in the table to take the
object. I have did it with embed objects by making a record and copying it
into each of the record of the OLE Field. I wish to change over to an Linked
system, and need help in putting a link of file into the field with the OLE
Object defined on each record.

I deeply appreicated any help that you can give!!!!


Thanks,

Gary
 
Hi Gary,

I almost never use OLE fields to store files (whether linking or
embedding). It's usually simpler just to store the names and locations
of the files in text or hyperlink fields (if the latter, the user can
just click on the hyperlink and Windows will open the file in its parent
application).

But as far as I know the sample code at
http://support.microsoft.com/?id=198466 will do what you want if you
just change these lines

[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed
to
[OLEFile].OLETypeAllowed = acOLELinked
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateLink

If you examine the sample code you'll see that it uses the Dir()
function to get the file names, and then stores them in the text field
OLEPath. You can use Dir() in the same way to get the file names and
locations and store them in a hyperlink field.
 
I really appreciate you input and responds.

What approach would you take if this was the requirements:

1) I need to associate a filename to each record. This can be done with
your approach by specifying the full path of the filename. It turns out the
file is a visio drawing and there will be one for each of the records.

2) I was going to make a folder that contain all of the files needed for
the database. There will be multiple folder in time, but right now, I will
have 10 folders of visio data. The location of these folders and program
will be moved from a local drive to a server mapped drived. I tried a
linked approach and the link are broken when I send the data to another place.

3) I understand that there is a chance of corruption when you embed the
visio drawing in the database. I was thinking of have a separate database
that only contains data and the drawings. I will load the main access
program with the data each time I am using it. Do you think this would help
prevent corruption of the data??? We also have multiple copies of the data
in the database.

4) If the main program is corrupted, that can be downloaded again.

5) We are thinking of not using linking for now because of it is easy for
the user. He has a form which will display the data on a form (from a table
which is loaded from the data database). The user will not have the problem
of broken links and stuff like that. They if they are linked, then there is
a chance that the data he change get reflect back to the original (but that
is a design issue).


What would you suggest????


Thank You Very Much for the Linking.


Gary

John Nurick said:
Hi Gary,

I almost never use OLE fields to store files (whether linking or
embedding). It's usually simpler just to store the names and locations
of the files in text or hyperlink fields (if the latter, the user can
just click on the hyperlink and Windows will open the file in its parent
application).

But as far as I know the sample code at
http://support.microsoft.com/?id=198466 will do what you want if you
just change these lines

[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed
to
[OLEFile].OLETypeAllowed = acOLELinked
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateLink

If you examine the sample code you'll see that it uses the Dir()
function to get the file names, and then stores them in the text field
OLEPath. You can use Dir() in the same way to get the file names and
locations and store them in a hyperlink field.

I have seen searching and posting for help for the last 7+ days. I have not
recieved any help so far.

I hope this isn't a dumb question!!! I also tried the knowledge base, etc
with on information on reconnecting links. Could I use that if I put a OLE
object with a broken link and reconnect???

I am trying to link a folder with individual files into a table record by
record using code. There is an OLE Object defined in the table to take the
object. I have did it with embed objects by making a record and copying it
into each of the record of the OLE Field. I wish to change over to an Linked
system, and need help in putting a link of file into the field with the OLE
Object defined on each record.

I deeply appreicated any help that you can give!!!!


Thanks,

Gary
Click to expand...
Click to expand...
 
In that situation, I'd store the information about the network share
(drive letter or UNC path) separately from the folder and filename.

For example, I'd store the UNC path (e.g. "\\SERVER\Data\Visio Files")
in a "settings" table along with any other configuration information.
Then it can be retrieved whenever needed with something like
Dim VisioFileLocation As String
VisioFileLocation = DLookup("SettingValue", _
"Settings", "SettingName='VisioFileLocation'")

Then I'd store the remainder of the path to each file in a text field in
the main table, e.g.

"Folder 1\Drawing 12345 v23.vsd"

To open the file, use a commandbutton on the form to assemble the full
path and tell Windows to open it, e.g. (assuming the remainder of the
path is stored in the field LinkedFile which is bound to the textbox
txtLinkedFile):

Dim VisioFileLocation As String
VisioFileLocation = DLookup("SettingValue", _
"Settings", "SettingName='VisioFileLocation'")
Application.FollowHyperlink VisioFileLocation & "\" _
& Me.txtLinkedFile.Value

Dim VisioFileLocation As String
VisioFileLocation = DLookup("SettingValue", _
"Settings", "SettingName='VisioFileLocation'")

It's probably worth taking a look at the DocMgr sample database here:
http://www.datastrat.com/DataStrat2.html . It shows how to work with
documents that are stored as external files.



I really appreciate you input and responds.

What approach would you take if this was the requirements:

1) I need to associate a filename to each record. This can be done with
your approach by specifying the full path of the filename. It turns out the
file is a visio drawing and there will be one for each of the records.

2) I was going to make a folder that contain all of the files needed for
the database. There will be multiple folder in time, but right now, I will
have 10 folders of visio data. The location of these folders and program
will be moved from a local drive to a server mapped drived. I tried a
linked approach and the link are broken when I send the data to another place.

3) I understand that there is a chance of corruption when you embed the
visio drawing in the database. I was thinking of have a separate database
that only contains data and the drawings. I will load the main access
program with the data each time I am using it. Do you think this would help
prevent corruption of the data??? We also have multiple copies of the data
in the database.

4) If the main program is corrupted, that can be downloaded again.

5) We are thinking of not using linking for now because of it is easy for
the user. He has a form which will display the data on a form (from a table
which is loaded from the data database). The user will not have the problem
of broken links and stuff like that. They if they are linked, then there is
a chance that the data he change get reflect back to the original (but that
is a design issue).


What would you suggest????


Thank You Very Much for the Linking.


Gary

John Nurick said:
Hi Gary,

I almost never use OLE fields to store files (whether linking or
embedding). It's usually simpler just to store the names and locations
of the files in text or hyperlink fields (if the latter, the user can
just click on the hyperlink and Windows will open the file in its parent
application).

But as far as I know the sample code at
http://support.microsoft.com/?id=198466 will do what you want if you
just change these lines

[OLEFile].OLETypeAllowed = acOLEEmbedded
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateEmbed
to
[OLEFile].OLETypeAllowed = acOLELinked
[OLEFile].SourceDoc = [OLEPath]
[OLEFile].Action = acOLECreateLink

If you examine the sample code you'll see that it uses the Dir()
function to get the file names, and then stores them in the text field
OLEPath. You can use Dir() in the same way to get the file names and
locations and store them in a hyperlink field.

I have seen searching and posting for help for the last 7+ days. I have not
recieved any help so far.

I hope this isn't a dumb question!!! I also tried the knowledge base, etc
with on information on reconnecting links. Could I use that if I put a OLE
object with a broken link and reconnect???

I am trying to link a folder with individual files into a table record by
record using code. There is an OLE Object defined in the table to take the
object. I have did it with embed objects by making a record and copying it
into each of the record of the OLE Field. I wish to change over to an Linked
system, and need help in putting a link of file into the field with the OLE
Object defined on each record.

I deeply appreicated any help that you can give!!!!


Thanks,

Gary
Click to expand...
Click to expand...
Click to expand...
 
Back
Top