Appending Table with a Picture (1 Viewer)

Gasman said:

It does not theDBguy, it merely mentions it and the download is clearly shown on the left.
The code for the demo is also available in the forms.

Click to expand...

Hi. Thanks. The intent was to convey it was originally created as part of something people have to buy, but I wanted to share this particular part of it for free to everyone. I hope that's clearer now. Cheers!

Post #18 was moderated. Posting this to trigger email notifications

isladogs said:

Post #18 was moderated. Posting this to trigger email notifications

Click to expand...

Any idea why? Just for my own education. Thanks!

NancyR said:

The attachment is a picture of that design and doesn't change. This isn't a power database, it's simple and only for me, but needs to be portable to my laptop.

I created a blank DB and pulled both main tables and their look-up tables into it. Then started trying to combine them into one main table. So both mains (with attachments) and the combined table (with half the pics repeated) are in there. With all that in the in-progress rebuild, it's just over half a meg. When/if I can pull the other 600 pics into the new main, and the two old tables deleted, I still won't be in any size danger.

I see in the 10 Commandments of Access that I should now abhor look-up tables. In my computer science classes (admittedly years ago), look-up tables were THE method to refrain from mis-typing, mis-spelling, etc., data in a field that's repeated over and over (like the source of one of my designs). I have over 1200 designs but only 51 sources and 12 formats and 27 locations (vital for finding that design when I want it!). Look-up tables!

Back on topic, why does access give us a data type (attachments) that is so different in routine use? That is rhetorical, I don't expect you to answer. Thanks for sharing!

Click to expand...

Whether its just for you or not, using attachment fields isn't a good idea. The database will grow by more than the size of each image added. As it grows, performance will begin to suffer ... long before you hit the 2GB limit.
Using a text field with file paths is a much better method.

Its easy enough to make the image paths portable to your laptop (etc) either by using consistent path structures or a subfolder of the app itself or by specifying the folder as a variable

My commercial apps are used in many schools each of which use a different path to store files such as images & PDFs used by the application. I can assure you it works well

I think you may be confusing lookup tables (which are fine) with lookup fields in tables (which are definitely not a good idea)

MS provides lots of features designed to allow beginners to create fairly complex applications with little knowledge. Unfortunately some of those features cause major issues which is why experienced developers advise against their use

theDBguy said:

Any idea why? Just for my own education. Thanks!

Click to expand...

It sometimes happens when members have less than 10 posts. That occurs if the post contains links, quotes or code (all based on tags) ...or sometimes it seems to occur if its quite long.
I believe this is built in to the forum software to help limit spam but in some cases like this one, it blocks posts for no obvious reason

isladogs said:

It sometimes happens when members have less than 10 posts. That occurs if the post contains links, quotes or code (all based on tags) ...or sometimes it seems to occur if its quite long.
I believe this is built in to the forum software to help limit spam but in some cases like this one, it blocks posts for no obvious reason

Click to expand...

Interesting. Thanks for the info. Cheers!

Don't build lookups in table, especially lookups with alias (saves identifier but displays another value). Build combobox or listbox on form.

Definitely don't have to buy the book. Book is mentioned out of professional courtesy and as additional reference material.

Hi Nancy. We haven't heard from you in a while. We hope you're doing okay. Please let us know if you still need help. Cheers!

Solved my issue, thanks! It was so simple, I felt like this: :banghead:

Thanks all for your input!

NancyR said:

Solved my issue, thanks! It was so simple, I felt like this: :banghead:

Thanks all for your input!

Click to expand...

Well, glad to hear you got it sorted out. Would you mind sharing your solution with others? It might help others too in the future. Cheers!

Sure. I opened my new database and table in one monitor, then the old database and table on the other monitor. I made sure they had the same fields, named the same, and then selected the records I wanted copied, did a ^c of the old fields and ^v in the new table. Copied 632 records in one go, WITH the pictures. Then I cleaned up my new database and started designing forms and queries.

NancyR said:

Sure. I opened my new database and table in one monitor, then the old database and table on the other monitor. I made sure they had the same fields, named the same, and then selected the records I wanted copied, did a ^c of the old fields and ^v in the new table. Copied 632 records in one go, WITH the pictures. Then I cleaned up my new database and started designing forms and queries.

Click to expand...

Hi. Nice going. Thanks!

theDBguy said:

Hi Nancy. If you're looking for a workaround, then maybe you could take a look at this demo. Hope it helps...

Click to expand...

HI DBguy,

I've just seen this post and it is something I'm looking for, tried your example but it is coming up with a compile error, saying the code must be updated for 64bit system......How do I do this? Thanks in advance.

Also whilst on the subject I have another related question you could probably point me in the right direction with.....

I have a search query that searches a table, I want a form to display the search results then a button to append the results (with attachment field) into a table, what code will work to append the search results? I tried using an append query for a search query but I just have to enter the search information twice so will need to use vba to do this....I also want to be able to put a button next to each record so that I have the ability to just append one of the search results, but I'm guessing this will be the same as for all results just with ID=Me! before it?

Thanks very much in advance!!

Cheers

Check out the links here

Thanks very much, working. That example is appending from table to table all records, I've only ever used queries to filter records from a table, can I use a query instead of a table the INSERT INTO statement? Or is it better to use VBA to filter the records instead of a separate query?

There are a lot of gaps in this thread. For example, no one clarified this mis-statement.

I see in the 10 Commandments of Access that I should now abhor look-up tables

Click to expand...

The "commandment" is referring to defining table level lookups. It is NOT referring to using combos/listboxes on forms and reports. They are two different things. Table level lookups cause problems for both experts and novices because they obfuscate the data. In your tblOrder, you have a reference to CustomerID. If you look at the data in the table, you will see a numeric value for CustomerID which is correct. But, if you change this to a lookup and connect it to the tblCustomer, instead, you will see CustomerName. I guarantee, you will never figure out when you need to use the numeric value or the string when working with this field in a code or a query. Anyway, ANYTHING that obfuscates the actual data stored in a table is bad and that includes simple formatting. If you add a format for the date for example and you make the mistake of using Now() to populate the date. The data will include a time element and it will mess up your search criteria because 1:16 on 12/26/2022 is not the same as midnight (which is the default when no time is specified) and so the two dates will not be equal. Then there's money. If your format hides decimal places, they are still there and still count for logical compares, but you can't see them

Also, no one asked the obvious question which is, do you have ONE image per record or may you have multiple? Aside from being non-upgradable should you have to upsize to SQL Server, the Attachment data type is intended to be a multi-value fields. I.e. the point of using it is that you have multiple images or documents per row. If you have only a single product image, just use an OLE data type if you decide to store the graphic. No problems with queries, no problems with upsizing. You still have the problem with bloat but that is something only you can determine will or will not be a problem. If you have multiple images per row and don't want to get trapped, as you have, by the Attachment data type, then just do it the way we've been doing it since day 1. Create a child table and use a subform rather than the abomination control to show the images.

None of the abomination data types introduced with 2007 provided any functionality that wasn't already available. They did provide cool controls but at a cost and most of the experts choose to not pay the cost since we already know how to handle the different data types.

When I store the file name rather than the document/image, I separate the path from the name. I use a table that the users control to specify the path name. Then in my test copy of the BE, I have a path for my local computer so I can find the files there if I am testing.

Sorry, I can't answer your specific question. I don't use the attachment data type and as I explained above, you don't need it either, especially if you have only one image per row.

I'm confused! Sorry!

I have tblEquipment which has several fields such as model number, serial number, etc. One of the fields is an attachment field to attached documents and photos to (could be 3 or 4 attachments). I have a form (frmEquipment) to search this table via a query, it requery's the data and it shows the results in a subform. Next to each of the results in the subform is a button that I need to put some VBA behind so that it copies that record into (ideally) a data entry form - frmRefit (which is already open as the tblEquipment form is a popup).

I could append it directly into tblRefit but I need it to show on frmRefit so that other information can be added. Hope that makes sense!

Why would you duplicate data to another table? Especially attachment field - compounding the bloat issue. If you have a table for inputting equipment maintenance data, you do not "copy" record from tblEquipment - usual approach is to select equipment item from a combobox.

Clarify exactly what you are confused about.

BTW, should have started your own thread instead of hi-jacking. Could have included link to this one in yours.

The usual method is to use a data entry form with a subform that lets you select equipment, There will be a combo that lists all the equipment, this combo might need criteria for its RowSource if you want to restrict the list to only certain types of equipment. The bound field is EquipmentID and it is the ONLY field that would ever be saved in the data entry table. To "see" other fields, you would use unbound controls or use some other method to display them without copying them. I tend to use a query. Take a look at my Many-To-Many example. It doesn't have an attachment but that isn't the issue here. You need to understand the concept of how to use a m-m relationship.

Thank Pat, will take a look, make sense