Error Relinking Tables with Attachment Fields

[Doctor]

I have been using the relinking code by Pascoe and Fairfield for relinking
multiple types of backends. Today, I added another BE that only contained two
tables, both with attachements in them. I was surprised to find out that
these tables can't be relinked, as the code is written.

Code is here: http://www.mvps.org/access/tables/tbl0012.htm

Can this still be accomplished?

I have two tables:
tblProductPictures with ProductID and ProductPicture.
tblProductAttachments with ProductID and ProductAttachments.
Both tables are on a one to one relationship with my Products table which is
in a separate database.

If this cannot be accomplished, I will probably try to set up something
where the user can drag a file to and drop off the file location and store a
link to that location instead of the actual attachment. Our preference would
be the attachment, if we can get the relinking to work.

 

[Stuart McCall]

I have been using the relinking code by Pascoe and Fairfield for relinking
multiple types of backends. Today, I added another BE that only contained
two
tables, both with attachements in them. I was surprised to find out that
these tables can't be relinked, as the code is written.

Code is here: http://www.mvps.org/access/tables/tbl0012.htm

Can this still be accomplished?

I have two tables:
tblProductPictures with ProductID and ProductPicture.
tblProductAttachments with ProductID and ProductAttachments.
Both tables are on a one to one relationship with my Products table which
is
in a separate database.

If this cannot be accomplished, I will probably try to set up something
where the user can drag a file to and drop off the file location and store
a
link to that location instead of the actual attachment. Our preference
would
be the attachment, if we can get the relinking to work.

I'm not familiar with the code and don't have time to go through it in order
to correct it (if that's possible), but if you'd like to try the drag-drop
approach, then here's a good start:

http://www.smccall.demon.co.uk/Downloads.htm#dragdrop

 

[Doctor]

Thanks. If I end up there, you have given me a great start for the drag and
drop. I appreciate it.

 

[David H]

Why do you have tables that contain related data such as that in separate
databases?

 

[David H]

Also for that matter why do you have separate tables for the PIX and the
attachments? I've never worked with storing files within an Access database,
however you should still be able to store both the pix and the attachments in
the same table if not in the same field, one in one field, one in another.

 

[Doctor]

This is for a printing company that wants to be able to store a picture of
each product (b/c 95% of the products are in languages other than english.
This picture will help identify what the product is for.) The attachments is
to store pdf docs about each product with the products as well. The reason
they are in a separate database and in separate tables is because I was
worried about approaching the 2GB limit with storing this type of information
and wanted to be able to split the two tables up again if necessary into two
different databases. Also, I didn't want to have to back up the db with all
of the attachements as often as the main db BE gets backed up.

 

[David H]

So what error are you getting with the code?

Have you created a test DB without any pix or attachments and tried to
attach to that table?

 

[AccessVandal via AccessMonster.com]

You can create an image control to view pictures, link to to the BE of
another folder where you store these pictures.

Or create a Hyperlink to open the pictures or your PDF files. You don't have
to store the files in Access Database. Search Help on this HyperLink. MS
advisory does not support the idea of multiple backends.

The code from the site only relink one backend, you'll need to recode the
function if you want to relink multiple BEs. The idea is possible, most
likely you'll need a table of all in the backend or recursively loop thru the
table objects to find the location or to relink to another location.

This is for a printing company that wants to be able to store a picture of
each product (b/c 95% of the products are in languages other than english.
This picture will help identify what the product is for.) The attachments is
to store pdf docs about each product with the products as well. The reason
they are in a separate database and in separate tables is because I was
worried about approaching the 2GB limit with storing this type of information
and wanted to be able to split the two tables up again if necessary into two
different databases. Also, I didn't want to have to back up the db with all
of the attachements as often as the main db BE gets backed up.

Also for that matter why do you have separate tables for the PIX and the
attachments? I've never worked with storing files within an Access database,

[quoted text clipped - 20 lines]

 

[Jack Leach]

As AccessVandal says, you can create an image control to view the pictures.
Rather than having the pics stored in the access file, create a folder on the
backend to hold all of the pictures, and programmatically file them and call
them. This way you store only the path of the picture (or any other
document) as a text field, rather than the document itself.

On the Image Control, set the Type to Linked rather than Embedded, and alter
the path programmatically:

Me.ImageControl.Picture = "YourPath\YourFile.jpg"

Obviously you would include some naming scheme for the pictures (the same as
the primary key makes the most sense), and would require a controlled folder
in the backend to store the pictures, but it is a far better approach than
storing them within access itself. 2gb fills up quickly like that.

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[David H]

I think that part of the issue was associating the picture with the product
which can get hairy if there are a ton of products. If you have a thousand
products, how do you name the files in such a way that you can easily
identify which file is for which product? If you go with a naming convention
that uses the product number as in N750163-1, -2, -3, etc, then what happens
when you need to look at the actual folder on the server? You'll go blind,
not to mention that if something should happen where a file is misnamed, you
might not realize it. Yes, properties on the folder can be such that the
average user has read only access, but I've seen to many things happen that
everyone thought could never happen.

 

[Jack Leach]

Coments in line...

how do you name the files in such a way that you can easily
identify which file is for which product? If you go with a naming convention
that uses the product number as in N750163-1, -2, -3, etc, then what happens
when you need to look at the actual folder on the server?

Neither the user or the developer should ever need to look in this folder.
With the same concept that users should never see a table, only forms, when
implemented correctly nobody should ever need to open this folder to see the
files... only links to the files from within the app. As far as naming goes,
well, every record must have a unique ID of some sort or another. I use the
autonumber value of the record ID to name all documents, with specific
folders for various catagories of files.

not to mention that if something should happen where a file is misnamed, you
might not realize it.

The only interaction with the actual files should be done programmatically.
The original file on a new record is selected from a "standard" location via
common dialog, and is then programmatically renamed and copied to it's
warehousing folder. As long as the functions are written correctly, this is
akin to asking what happens if a record ID gets accidently changed. Sure it
would suck, but how would that happen?

Yes, properties on the folder can be such that the
average user has read only access, but I've seen to many things happen that
everyone thought could never happen.

First off, noone should ever be screwing around in the backend files and
folders of any application, so this shouldn't be an issue to begin with.
Secondly, there is no amount of anything that will keep someone from screwing
up the works if they want to. If I (or anyone else) really had a notion to,
I can go deleting dlls from the system32 folder in windows. Even with Vista
security all you need is a little knowhow. But just because you can doesn't
mean you do, and if you design an app that stores pictures in various
subfolders of the app's server directory, design it so that the user has no
idea they may be stored anywhere else except in the db. Don't give them a
reason to go looking for it or suspect that it might even exist. And if you
have users that are stupid enough to go changing *anything* in an
application's folder, then it's probably time to have a little heart to heart
with that particular user.


Maybe I'm missing something here, but I don't quite see how keeping files in
the db itself has any advantage over storing the path. There are some cases
where the user needs to actually open the file itself, sure, but that's
easily accomplished by copying the original to a temp folder for use. And if
the user needs to make a change to the original? Copy the orignal, make the
changes, and give them an update function to replace the original with their
new copy.

I've practiced this since day one, and I think if I were to do a count just
on documents stored in my backend in the fashion, I would be well over a
thousand, and have never had any issues with it.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)