Question Linking Jpeg Images to access: Selecting 2 from multiple images stored on network (2 Viewers)

[geekygal]

Hi everyone! Let me first introduce myself as new to this site, but a bit of a computer geek. When I divorced my ex took his code, and I am tired of waiting for other people to do my work (even those I was going to pay), so I decided to learn Access to do my own work. I can navigate word and publisher decently, with a little tiny bit of excel and Power point as well. I am using Office 2007. I started my voyage into Access with a Microsoft Step-by Step Access 2007 book (and yes I did all the demos in the book). I understand a lot of the concepts of database, but I lack the training or technical terminology, but I have spent about 200 hours in the past 6 weeks working with access. I do have some friends who are helping me with most issues I encounter, but they have limited access knowledge in the area of images, so here I am! If anyone has tips or assistance it would be greatly appreciated.. I'm not looking for someone to hand over a solution in a box (lol, if someone has that I will gladly pay for it), but I want to learn how to do it myself!
Here is the situation:
I have a large inventory (million items) of let’s say baseball cards. Every item will be scanned (front and back). I use a duplex scanner, so the images are scanned and saved in order. The images will be stored on a 7.5T array that will be connected to my home network. I want to be able to link these images to my Access database when using a form, to assign 2 images to every inventory item. Occasionally I may have a need for linking more than 2 images, but for 98% of the time it will be 2 images per inventory item. In my ideal situation, when I open my form, I would direct the form to a folder where the images are stored, and select what 2 images to start with. Each inventory item (thus each time I fill out my form) will require 2 images (front and back side of inventory number). At this point I think that the images will be 300 dpi jegs.
For various reasons I will need to be able to manually select which two images are assigned to each inventory number. <Upon occasion the scanner jams or the operator (ME!) makes a mistake and a card is scanned back side down making the images not in the proper order (thus a computer program can not automatically assign the images to the inventory item).
Can anyone point me in the right direction? I am thinking that I am going to need a program that I have to pay for, and I have stumbled across a few, but not really sure I am looking for… But also if anyone has any old code laying around that they would like to share (and that I could adapt), that would be great. I do have friends locally who can help me work with it. Most importantly, just looking to converse with someone who has done this before and who wants to share their expertise with me!

This is a project for my own small business, not some program that I am going to repackage and sell, nor some college course assignment. All tips and help is greatly appreciated!

THANKS!!!!!
GeekyGal Aka Stacey

 

[ChrisO]

It would help to know if there is some correlation between inventory number and image names.

Example:
Inventory number = 123456
Related image names are 123456_A and 123456_B

 

[geekygal]

It would help to know if there is some correlation between inventory number and image names.

Example:
Inventory number = 123456
Related image names are 123456_A and 123456_B

When the items are scanned, there are approximately 500 items per folder.
All the images in the folder have a name such as
Baseball_1, baseball_ 2, baseball 3_, baseball_4
inventory #1 = baseball_1, baseball_2
inventory #2 = baseball_3, baseball_4


As of now, I have no inventory #'s assigned. There is no data on each inventory item, it all needs to be entered in, starting from total scratch so to speak. I will be assigning the inventory number as I enter the information into the access forms.

So no there is no correlation at the current time between the inventory # and the images....

Any other information that I can provide that can give you a more complete picture?


(YES I know that I need inventory #'s, that is why I need this program, to help me assign inventory #'s to everything!)

Thanks!

GeekyGal aka Stacey

 

[ChrisO]

Okay, this is the first problem:

You said; “Occasionally I may have a need for linking more than 2 images, but for 98% of the time it will be 2 images per inventory item.”

A 98% hit rate is not good enough to calculate the inventory numbers from the image names, for that we need a 100% hit rate. If we can’t calculate the inventory numbers from the image names we (you) will be in for a lot of manual work if you have a million image names. Even at 2% exception there would still be 20,000 records to change manually so it’s best to figure out some automated means.

Let’s say, for argument sake, that we can create an inventory number based on folder name and image name. The inventory number would then be unique because Windows would not allow duplicate names. The Form could then calculate which two images to display from that unique inventory number.

That then leaves the three image exception scenario. They could go in an exception folder so that if the Form needs the third image it at least knows where to look for it, if it exists and if required.

I guess the question is now; what does the exception look like at the present?
inventory #1 = baseball_1, baseball_2
inventory #2 = baseball_3, baseball_4
inventory #3 = baseball_5, baseball_6, baseball_7
inventory #4 = baseball_8, baseball_9

 

[geekygal]

Okay, this is the first problem:


I guess the question is now; what does the exception look like at the present?
inventory #1 = baseball_1, baseball_2
inventory #2 = baseball_3, baseball_4
inventory #3 = baseball_5, baseball_6, baseball_7
inventory #4 = baseball_8, baseball_9

The exception would go like this:
A.
inventory #1 = baseball_1, baseball_2
(image 3 needs to be deleted because it was a mis-scan)
inventory #2 = baseball_4, baseball_5

or
B.
inventory #3 = baseball_5, baseball_6,
inventory #4 = baseball_9, baseball_8
(image 8 is actually the front of the card and needs to be showing 1st in my record)


((Lets take the inventory that needs more than 2 images out of the equation for now))

There are two reasons why the cards will never be in 100% front/back order. Scanner problems (I have a nice $5,000 scanner that I can not afford to upgrade) where it occasionally jams and in the example above A, where image 3 needs to be deleted, as there is no matching back side image.

Second is that some of the cards *must* be scanned in reverse and thus would appear in order (back, front) in the folder, but not in the right order (I need them to appear in front/back order). For these cards, it is the only way that I can get the cards to adf to catch the cards.

I have pretty much accepted the fact that I will have to do manual matching of the images to items in the database. I know with a million items that is a daunting and multi-year process, and accept that. And yeah this is a small home based business... if anyone doubts me I can show photos of my home office...


Thanks again! So nice to know there are fellow geeks willing to share their expertise...

GeekyGal aka Stacey

 

[smig]

not sure what you use the inventory for.
the easiest way is to put an image on a form and change the rowsource of the image to show the correct image.
you can either store the full image name in your database, or give the image a name that will fit the correct record ID.

give the cards names like "Card_000000003_front.jpg"
and make sure the 000000003 is the ID of the record in the database.

 

[ChrisO]

G’day Stacey.

Attached is a small demo to load the Invoice numbers based on image files.

At this stage all I want to test is if we are on the same wave length and get Invoice numbers.

Regards,
Chris.

 

[geekygal]

Smig- That sounds like what I want to do, except I want to do it from within Access be able to view the images and select which two are assigned to each item as I enter the information into the Access form.

ChrisO - Not sure what I am doing wrong, but your demo is not working for me. I select the folder, click Load Inventory number (that part works okay) then open tblInventoryPictures and nothing is in that folder.
Right now the images are in this path
c:\Users\Stacey\Desktop\Images_Scanned\10112009\68_cont_Italy
and the images are named like this: 68_cont_italy0001


You and I are on the same wavelength though! When you say invoice #'s do you mean inventory #'s?

CONFESSION .... CONFESSION... and I feel horrible about this, but I need to come clean, as it may shed a little more light on the situation.. the inventory we are talking about is antique postcards, not sport cards. My sincerest apologies. This is the first time asking for help in a public forum and I was leery of disclosing the product ((not for you techies reading, but for my competition researching themselves online... but then again my 8 year old daughter has more computer skills than most other postcard dealers)). Not sure how relevant this info is, but I wanted to come clean...

Thanks
GeekyGal aka Stacey

 

[jdraw]

G’day Stacey.

Attached is a small demo to load the Invoice numbers based on image files.

At this stage all I want to test is if we are on the same wave length and get Invoice numbers.

Regards,
Chris.

Chris,
I'm following the thread also. I downloaded your sample mdb. Selected a drive/directory with a number of jpg images. I then clicked the LoadInventoryNumbers. Nothing.

I'm just looking into the code - there are no comments - to see if I can see something.

I changed this line ( removed the "Baseball" from the filename in ScanForFiles)

strFileName = Dir(strFolder & "*.JPG") 'Baseball.jpg

I do not get any Inventory numbers

I do get a list of the Folders I have selected.
I selected 2 folders
1) c:\Alex that had 6 jpgs, and
2) I:\2008 Pat Pictures\2008 Pics\2008 Oct\ that had 135 jpgs. numbered 7 thru 141

Note: I changed my adjusted version of your mdb to InventoryPicturesJD.mdb

Then could not add my mdb to your zip. Zip gave Could not create error???
So created a new zip (attached) Another.zip that contains
your zip and my version of the mdb InventoryPicturesJD.mdb


I think your general approach is how I would attack the problem too. Read the "scanned images" from the various folders, recording the image names, folders and creating skeleton records for each of the inventory items.
You could then review the images and augment the records (add field values, descriptive texts or whatever).


Edit:
Just did a little more testing. There are no Inventory Numbers because there are no "_" in my file names.
My jpgs are named like this: DSCF2696.jpg


Here is the code that assigns InventoryNumber

 Do While strFileName <> ""
            
                .AddNew
                    !InventoryFK = lngFK
                    
                  [COLOR="Magenta"]  lngInvNum = InStr(strFileName, "_")[/COLOR]
                    If lngInvNum <> 0 Then
                        lngInvNum = Val(Mid$(strFileName, lngInvNum + 1))
                        lngInvNum = (lngInvNum \ 2) + 1
                        !InventoryNumber = "Inventory_" & CStr(lngInvNum)
                    End If

 

[geekygal]

Okay so this 2nd version worked better in the sense that it found my image folder. I selected 2 folders, but no inventory numbers are assigned. Do I need to (for future scans) avoid using _ in the file names? I was told to use _ instead of spaces, but I can not use the _ if necessary.

I then selected another folder on my network and those jpegs did not have _ in the names. (\\Stacey-PC\Post Card Business\Post Card Depot\website images\jpeg). This seemed to work better.

So where do I go from here?

Attached is a file with my results...

Thanks!
Geeky Gal aka Stacey

 

[smig]

I'm reading what you wrote from start and scratch my head
are you realy going to scan 1 milion items and set their names into the database

is there any order the names of the images are created ?
if they do get sme order I think you create a simple code that will put the correct names in the database, and you might need to fix the mistakes after that.

let's say the file names are something like 0000001.jpg to 9999999.jpg

you can write a simple code like this:

for i = 1 to 9999999, steps 2
record(i).picture1 = "folder_name" & format(i, "0000000") & ".jpg"
record(i).picture2 = "folder_name" & format(i + 1, "0000000") & ".jpg"
next i


this is only the idea, and not a real code !!!

 

[ChrisO]

G’day Stacey.

I think we are getting closer.
See if you get inventory_1, inventory_2 and so forth.

Same test required with current attachment.

Regards,
Chris.

 

[jdraw]

G’day Stacey.

I think we are getting closer.
See if you get inventory_1, inventory_2 and so forth.

Same test required with current attachment.

Regards,
Chris.

Just tried it Chris - works as expected.

 

[ChrisO]

G’day jdraw.

I think your situation is different to Stacey’s in that she has 2 pictures for each inventory and you probably only have 1.

But in your case if you had an image called DSCF2696.jpg you should have got an Inventory_1349 number. So try ABCD = 1349 subtract 1 and multiply by 2.

Then FolderName & “DSCF” & Format(ABCD, “0000”) & “.jpg” should be the image path and name.

 

[geekygal]

Wow.... that works great. Except that every image is assigned a different inventory #.

Attached is a sample of my results.


In response to those who think I am crazy, lol.... a little more info...

I'm reading what you wrote from start and scratch my head
are you realy going to scan 1 milion items and set their names into the database

is there any order the names of the images are created ?
if they do get sme order I think you create a simple code that will put the correct names in the database, and you might need to fix the mistakes after that.

let's say the file names are something like 0000001.jpg to 9999999.jpg

you can write a simple code like this:

for i = 1 to 9999999, steps 2
record(i).picture1 = "folder_name" & format(i, "0000000") & ".jpg"
record(i).picture2 = "folder_name" & format(i + 1, "0000000") & ".jpg"
next i


this is only the idea, and not a real code !!!

Yes, I do have close to a million items. Yes I am planning on scanning them all in. I have already scanned close to 100,000 items. My scanner is capable of, if I am operating it for most of the day (this is a full time endeavor for me) scanning in 15,000 cards per day (it is similiar to a fujitsu fi-6670). I know this is a huge project, but there is me, my mom and even my 8 year old daughter all working on various aspects of it.

Let me walk you through my scanning process. I have a shoebox filled with 750 cards. All of those cards are scanned (duplex) so they create 1500 images. All 1500 images are saved into a file: the file name is a combo of the box # and a one/two word id of the cateogry/type (ie. 4_cont_Florida).

Many thanks again
Stacey

 

[ChrisO]

Stacey, how many images do you have in that folder and what is the number range of the images?

 

[geekygal]

Stacey, how many images do you have in that folder and what is the number range of the images?

In this folder there are 1338 images. They are in this order:
17_foreign0001
17_foreign1338

Stacey

 

[ChrisO]

Good, we are very close.

Each inventory number points to a pair of images and it would appear two images names are wonky.

So I think we can now expand the code and I’ll also get rid of the prefix “Inventory_” because all we need is the number. We can tack the “Inventory_” on for display only, no need to save it and it will save table space.

We will also save the, in this case, 17_foreign in the Inventory table.

Give me a couple of hours and I should have the tables working.

Regards,
Chris.

 

[geekygal]

Good, we are very close.

Each inventory number points to a pair of images and it would appear two images names are wonky.

So I think we can now expand the code and I’ll also get rid of the prefix “Inventory_” because all we need is the number. We can tack the “Inventory_” on for display only, no need to save it and it will save table space.

We will also save the, in this case, 17_foreign in the Inventory table.

Give me a couple of hours and I should have the tables working.

Regards,
Chris.

Amazing! Incredible! I am heading out to my local geek friend to help me with some other issues with my databases. I won't have access to the images here on my computer to test it out until really late tonight/early morning, but I check back in the morning!

You have just made my day/week/month year if you can get this working for me. Thanks!

Stacey

 

[ChrisO]

G’day Stacey.

See how the new attachment goes.

Regards,
Chris.