One-To-Many OLE objects

[JohnLute]

My database includes corrugated box specs that are stored in tblProfiles.txtProfileID (primary key). Each corrugated box can have many stacking patterns (.pdf files)

This would be a one-to-many relationship design, however, what's the best way to set this up? Would I make a table related to tblProfiles in which to store the .pdf file stacking patterns

Thanks!

 

[Dwayne Shrum]

Sure. Sounds like you already know the answer.

I would make a new table like tblStackPatters with no
designated primary key, the foregin key from tblProfiles,
and one ODE object (.PDF file). One row per object
therfore many (1:0-N) .PDFs can relate to one tblProfile
instance.

You could use both fields in the second table as a
composite key if you want to guarantee that you don't ever
have duplicated PDFs related to the same txtProfileID.

-----Original Message-----
My database includes corrugated box specs that are stored

in tblProfiles.txtProfileID (primary key). Each corrugated
box can have many stacking patterns (.pdf files).

This would be a one-to-many relationship design, however,

what's the best way to set this up? Would I make a table
related to tblProfiles in which to store the .pdf file
stacking patterns?

 

[JohnLute]

Thanks, Dwayne. Here's where I'm stumped now. So I have a table per this design. What's the best way to get the objects into the talble - through a form or the table

Also, in sfrmProfiles (sourced to tblProfiles) what's the best way to associate the many stack patterns to a particular tblProfiles.txtProfileID - a subform

----- Dwayne Shrum wrote: ----

Sure. Sounds like you already know the answer

I would make a new table like tblStackPatters with no
designated primary key, the foregin key from tblProfiles,
and one ODE object (.PDF file). One row per object
therfore many (1:0-N) .PDFs can relate to one tblProfile
instance

You could use both fields in the second table as a
composite key if you want to guarantee that you don't ever
have duplicated PDFs related to the same txtProfileID

-----Original Message----
My database includes corrugated box specs that are stored

in tblProfiles.txtProfileID (primary key). Each corrugated
box can have many stacking patterns (.pdf files)what's the best way to set this up? Would I make a table
related to tblProfiles in which to store the .pdf file
stacking patterns

 

[Dwayne Shrum]

Exactly!

I would use a form with a subform for the 0,1, or more PDFs

One to many with 1:0 to n participation unless at least
one PDF is required, then it would be a 1:1 to n degree of
participation.

-----Original Message-----
Thanks, Dwayne. Here's where I'm stumped now. So I have a

table per this design. What's the best way to get the
objects into the talble - through a form or the table?

Also, in sfrmProfiles (sourced to tblProfiles) what's the

best way to associate the many stack patterns to a
particular tblProfiles.txtProfileID - a subform?

 

[Ted Allen]

Hi John,

Depending how many PDF's you will have, you may want to
consider storing links to the files rather than embedding
the files themselves. You could save the PDF's in a
structured file system, such as having folders for each
box ID and hopefully some sort of file naming convention
as well.

If you do this, you could still use the same table
structure and form/subform relationship that Dwayne
suggested. The advantages would be a much smaller
database, quicker navigating forms, and the ability to
recreate the relationships if needed from the file/folder
structure. The disadvantages would be that your users
would need to put the PDF's in the proper folder and name
them properly and that the files would not be displayed
directly in the form, they would have to be clicked to
launch externally. You can decide what would work best
for you based on the number of files that you expect and
the needs of your users.

I'm not sure if you have any VBA experience, but if you
do, or if you learn in the future, there are other
options as well such as just storing the file path in a
text field and using code to launch the file. Also, you
can do things such as automatically linking files that
have been placed in the proper folder, and automatic
generation of the folder structure to begin with.
Additionally, you could write code to display a slected
PDF file (from a list) in an unbound object on the form.

Just thought I would pass along these ideas in case they
would help you. Post back if you want further info on
anything.

-Ted Allen