Microsoft Access 2000

[buchert1]

Hello, I've been given the task at work to create a database for everything single part that gets painted in the factory. The guys on the assembly line want pictures so they can easily understand how to hang and pack out different parts. Is there any way to insert pictures into each separate part page without storing the picture in the database and instead pulling the picture from a folder outside the database. I also do have code to look up each part number whether it be the painted or unpainted part number. Please let me know if anyone can help!

Thanks

(... from the second thread ...)
I'm working on a database for work that involves putting pictures into the database. But I'm trying to find a way to store them in the database without actually keeping the picture in the database to avoid a storage overload in the database. I have a search bar that will look up either the painted or unpainted picture but putting a picture in has been a struggle. Just a beginner using Microsoft Access 2000 and looking for help with as much detail as possible. Thanks!

[zmbd]

Hello buchert1
Welcome to Bytes.com
You'll find an answer to this exact question here:

https://bytes.com/topic/access/insights/970955-display-images-your-db-without-using-attachment-fields

Post#2 has a copy of my database that gives an example of one method I use for this, a custom function.

On a 10 point scale 0=novice to 10=Expert I would put this around an 8

What kind of experience with Access and Relational-Database development do you have?
Do you understand the term "Normalization" ?
If not then you'll want to take a look at[*]> Database Normalization and Table Structures.

[buchert1]

neither of these have worked...
Can you please help me figure out the correct solution

[zmbd]

@bucher1 neither of these have worked... Can you please help me figure out the correct solution

Well... you have to help us to help you!
⛔Simply saying "this doesn't work" without any details is not going to result in any reasonable help...
What did you try, what didn't work, what error message(s) did you receive, what do you not understand?
⚠Please take a moment to visit our FAQ covering how to ask a question.

☑The link I gave you has examples of how to implement what you described in your post... is this a custom solution to your situation, absolutely not, Bytes.com isn't a coding service. We're happy to help you with a project; however, if you want someone to custom build the project for you... there are services out there that will do that for you, for a small fee, of course 😉

Using the idea in the archive in post#2 in the thread I linked you to for custom function to pull the image from a file... I use that concept for a class roster... as seen below (sorry, I had to redact the name and the beautiful young lady's face due to privacy concerns):
When I select the student in the left pane, their information is populated, and the picture of that student is displayed.
If one goes the the reports tab in the main form, the student reports will insert the image of the student....
All using the custom function concept as presented in Post#2 of the Insight article I linked you to
[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=10 556[/IMGNOTHUMB]

[NeoPa]

Hi Buchert.

I will start by commenting that even MDB files are generally better used with Access 2003 rather than 2000, but I don't know your circumstances so I'll leave it there for you to change or not.

The usual approach to handling pictures is to store them on the file system and then, when it comes to showing them on a Form or Report, to update the picture Control to point to the relevant one.

To give more detail I'd need a better understanding of your situation. You describe it, but only cursorily. I expect we can make better progress after you reply with more info.

[zmbd]

@bucher1
You've already asked this question - please do not double post

[buchert1]

Thank you for getting back to me NeoPa!

Unfortunately I am stuck using 2000 due to what work has given me. I will try and give you as many details about the database to help better describe the situation I'm in. This database is being continuously used throughout the day back on the assembly line looking up different part numbers in the search bar I have created using VBA coding. But the problem I'm running into is finding code to help put different pictures that will link with one part number. So in the end they will look up one part number and the picture of that part will show up. But there are many parts so I want to put the picture into the database without actually storing it in the database. I have read about options that are possible but everyone that I try seems to go no where. This is the first project I've used with Access 2000 so I'm still a beginner. Hopefully that is enough info to help you with an answer to the problem! Let me know if I can provide anymore details that will make it easier to resolve the problem!

Thanks!

[zmbd]

Multiple Pictures associated with one record

@bucher1 But the problem I'm running into is finding code to help put different pictures that will link with one part number.

Good Morning Bucher,
Now we have something to work with!
If you stick with this... you can do this... however, this is really not a novice project - so be patient with yourself (and with us :) ) we've all been there, and we'll help you get thru this!

📌You are not going to find a single VBA code solution that will show multiple images for a given part.

🏁 HOWEVER - comma...
the Concept of Normalization can help here: > Database Normalization and Table Structures.

So to give you idea as to how I might accomplish what it appears you are trying to do...

Using a very simple construct (I'm going to GREATLY simplify the normalization)
Table with your "Part information"

[iCODE]tbl_Parts[/iCODE]
[PK_Part] [PartID] [PartName] [PartDetail                  ]
[1      ] [124A  ] [Example1] [Just an example part item one]
[2      ] [567A  ] [Example2] [Just an example part item two]

Table linking each part to a series of pictures

[iCODE]tbl_ImagesToParts[/iCODE]
[PK_Img2Prt] [FK_Part] [ImgName     ] [ImgDetail                  ]
[1         ] [1      ] [Img_1001.jpg] [1st Image for item one]
[2         ] [1      ] [Img_1002.jpg] [2nd Image for item one]
[3         ] [1      ] [Img_1003.jpg] [3rd Image for item one]
[4         ] [2      ] [Img_2001.jpg] [1st Image for item two]
[5         ] [2      ] [Img_2002.jpg] [2nd Image for item two]
[6         ] [3      ] [Img_3001.jpg] [3rd Image for item three]

All of the fields that start with "PK" are of type "Autonumber " and will be set to the "primary key" for the table
What these fields do is keep a unique record id that is easier, and more stable, to use to relate records from one table to another. (Please read the article about Normalization if you don't understand)
Better yet, this primary key field, IMNSHO, should NEVER change for the record; hence why we have the field for the [PartID] . Field [PartID] can be either text or numeric data type and if the part id changes (say management wants to add a location id or whatever), you only need to update in one place!)

Something to note: [tbl_ImagesToPar ts]![ImgName ]
In this example I've only used the image file name. In post#2 in the insight article I linked you to the function uses the entire file-path-file name to the image - we can talk about how to handle this later as the function can be changed to search for the image file in various ways depending on the need.

[tbl_ImagesToPar ts]![FK_Part] is of data type Numeric-Long-Integer
This is the field that we're using to establish the relationship the two tables
(IIRC: In Access2000 > Menu bar > DATABASE TOOLS > Relationships)
In the dialog that opens you add the two tables and then drag-drop
[tbl_Parts]![PK_Part] on top of [tbl_ImagesToPar ts]![FK_Part]
so you have something analogous to what I have in my school roster database the table between students, classes, attendance etc... (the lines here show 1:1 and 1:Infinity ... that's next)
[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=10 557[/IMGNOTHUMB]
now change the relationship by RIGHT-clicking on the line (here I show the relationship between the student list and the attendance table.)...
[IMGNOTHUMB]http://bytes.com/attachment.php? attachmentid=10 558[/IMGNOTHUMB]
> Set Enforce Referential Integrity to true
Two things here, I do not use the Cascade-Update option because I use the autonumber field as the primary key (again, see the article on Normalization if you don't understand). This field will NEVER change so the auto-update is not needed. The second thing here is that I rarely if ever use the Cascade-Delete option because it's all too easy to lose a lot of information (for example, if we set C-Delete to true, deleted item one in tbl_parts (PK_Parts = 1) then all of the records in [tbl_ImagesToPar ts]![FK_Part]=1 would also be deleted!!! OUCH (yes, it's happened to me, learn from my mistake) Whereas if C-Delete is set to false then every record in [tbl_ImagesToPar ts]![FK_Part]=1 would have to be deleted first before the record in tbl_parts could be deleted.

If you look back at post#4 I have something fairly complex; however, the left panel is the parent form, when I select a record the two sub-forms populate...
So, for this example, something really simple would be to setup two forms for example
frm_Parent - data source will be [tbl_parts]
frm_Child - data source will be [tbl_ImagesToPar ts] and will eventuall be the subform to frm_Parent. Once linked, the related images to the current record in frm_Parent will show. As a stand-alone form all of the images will be available.

Have your part information in the header section of frm_Parent and in the details section of frm_Parent insert the frm_Child as the subform relating the parent to child on between the [PK_Parts] and the [FK_Parts] fields

In the frm_Child, the detail section would have the image control that uses the custom function (that I referred you to in post#2 in the insight article I linked you to - this custom function "reaches out" on to your network, local drive, etc... and finds the image file and then displays it in the image control...
So, because you're stuck with Access2000, before we go any further; the question here is: can you download and open the database and related files in the attachment to the post in the above link?
If not, I can still down grade it to an Access2000 MDB - however, try opening it and let me know 😵
It will be easier for you to understand how the function works and how it is used if you can open that database and related files... to try and explain it would make the post 1000x longer!

[NeoPa]

Hi Buchert & zmbd.

I can see that Z has already put a great deal of effort into this question so I would expect anything I add simply to muddy the waters for everyone concerned.

@Buchert.
Please take Z's instructions on board about not reposting questions. He's saved me having to give the same. It simply wastes people's time and also tends to upset those who find out later their time was not well used. It's perfectly excusable on the first occasion though of course.

If at any time you feel a response doesn't give you everything you need, or even that you struggle to understand it, then feel free to post back to explain clearly what you're struggling with. When we know what it is that you don't understand we can put our focus to where you need it most. The converse is also true of course. If we don't know then we can't.

Good luck & welcome to Bytes.com.

[zmbd]

@Buchert.
When you get your database lined out... please drop by - success stories make the best reading material!

[buchert1]

Hello zmbd,

Unfortunately I still haven't figured out how to connect to two tables to the data table being able to connect everything together. I got the part of making the tbl_Part and tbl_ImagestoPar ts but then trying to connect everything throughout relationships and queries is getting to my head a little bit. I know you said it would take a lot longer to explain but if you could explain how to put it into a MDB that would be fantastic. Again very sorry but I think my lack of experience is getting to me a little bit. So my overall final question would be how to connect the tables and make it all into one mega form with everything connected to it.

Thanks again