[X]My Assistant

[bhicks11]

Hi All,
Looking for suggestions from all you experts.
I would like to automate adding a single attachment to multiple records via client vba. So to explain: I have a table with an attachment field and an ID field (among others). I have a folder on my desktop that contains about 300 jpg images. I would like to add 1 jpg per record to an attachment field. I want to see an image on each record in the image control on the form. My form/image control works fine but adding the images is cumbersome.
Any code ideas?
Bonnie

[theDBguy]

Hi Bonnie,
You are referring to an Attachment field in a SharePoint list, correct? If so, I have been able to attach a file to a "local" table, but I haven't been able to do it to a SP list.
I would be interested to see what others will recommend.
Just my 2 cents...

[bhicks11]

I am able to attach files in Access sharepoint lists. It doesn't like tif's but jpgs work fine. My question is about batch loading attachments.
Thanks, Bonnie

[theDBguy]

Hi Bonnie,
Maybe I didn't make myself clear. I was talking about attaching a file using VBA. If you are talking about the same thing, then you should be able to loop through all the records to attach the files you need. And if you did manage to attach a file to a SP List using VBA, I would be grateful if you could share the code with us.
Just my 2 cents...

[bhicks11]

Yes, sorry DB I misunderstood.
Like you I found some code but it doest work with Sharepoint. I may have to ditch this approach.
Let you know if I find anything.
Bonnie

[bhicks11]

I GOT IT! Here's what works (it took an hour for 150 attachments to be added.):
ub LoadFromFile()
Dim mfile
Set db = CurrentDb
Dim rsTickets As DAO.Recordset
Set rsTickets = db.OpenRecordset("TicketsTbl")

If Not (rsTickets.EOF And rsTickets.BOF) Then
rsTickets.MoveFirst
Do Until rsTickets.EOF
rsTickets.Edit
Set rsPictures = rsTickets.Fields("Field1").Value
mfile = rsTickets.Fields("ImageName")
mfile = "C:\Folder\Subfolder\" & mfile

rsPictures.AddNew
rsPictures.Fields("FileData").LoadFromFile mfile
rsPictures.Update

rsTickets.Update

rsTickets.MoveNext

Loop
End If

[theDBguy]

Hi Bonnie,
Congratulations! Glad to hear you got it working.
Unfortunately, that is the same code I used earlier but got the "Invalid Argument" error when used against a SharePoint List. I wonder if you actually got it to work against SharePoint or a "local" table? I will have to test it again on Monday. Maybe I missed something.
Oknow you are working with a "web database" so VBA is not normally an option for you. Seeing that you used VBA this time makes me wonder even more.
Good luck with your project.

[bhicks11]

Hi DB, Yes I ran this against a published Sharepoint list. I, of course, ran it from the client module. This was what I needed for my application. I have a couple of remote ladies that key records and I wanted to have the image embedded. I get new images daily so I will first zap the table then add new work. One thing I didn't mention, I first populate a field with the image name (I have a function that adds the file names from a designated folder, be happy to share if you need it) and then I add the file that matches that image name (mfile = rsTickets.Fields("ImageName")
mfile = "C:\Folder\Subfolder\" & mfile).
onnie

[bhicks11]

I added a post to my blog with more detail on this process for anyone interested:
http://msaccessdevelopment.blogspot.com/20...ment-field.html
Bonnie

[theDBguy]

Hi Bonnie,
Thanks for posting the link to your blog. Seeing some of the background info about your project helped me analyze my issue a bit further. What I would like to know more, which does not show on your blog, is your current setup. I was wondering if the computer where you run this code is in the same network as your SharePoint server, or if your data is hosted at a remote server or even AccessHosting or Office 365.
HAs I was thinking more about it this weekend, I wondered if the problem was because I was using Acc2007 while you were using Acc2010. I was going to test that tomorrow when I get back to work. But, reading more from your blog, I noticed the following statement:
"Within the table design (and then forms) I included an attachment field."
Now, I'm wondering if that is the problem. You see, a "true" SharePoint List would not need an attachment field be added to it. It should come with one already. This too, I will test tomorrow when I get back at work.
Just my 2 cents...

[bhicks11]

My site is hosted by accesshoster.com. About the Attachment field, I added an Attachment field when I designed the table. They didn't become Sharepoint lists until I published the site.
onnie

[theDBguy]

Hi Bonnie,
Thanks, that explains the difference in our settings. I will do some testing today and let you know what I find out.
Cheers
DBG

[bhicks11]

Here's another solution:
esign CLIENT forms that have an unbound Image Control, place TIF images in a consistent folder on the users computer (I would automate the daily image distribution). Run PopulateImages() function that adds the images names to a field in the table. In the OnCurrent event of the form, set the unbound image control's "Picture" to the image. As my tables are still Sharepoint lists, I still have the above stated advantages. This is more reasonable for highly daily volumes (remember it took and hour to add 150 images to the attachment field). It also gives the advantage of distributing any updates to users automatically whenever they login. Users can use the free MS Access 2010 runtime. Either browser based or client based can be available from the same application.
Private Sub Form_Current()
Dim MImage
MImage = "C:\folder\NewImages\" & Me.ImageName
Me.ImageNew.Picture = MImage
End Sub
I think it's pretty cool!
Bonnie

[theDBguy]

Hi Bonnie,
agree... Displaying the images from the network folder would be faster than embedding them into the table. However, don't you lose the ability to see the images for any "remote" users?
Just my 2 cents...

[bhicks11]

Yes DB but for my application, I only need the person keying the assigned records to be able to see them (and of course myself but I will have them all in the designated folder). In my scenario, I am running through different images daily. Including a batch process that removes the completed images and replaces with the new, works well.
onnie

[theDBguy]

Hi Bonnie,
Thanks for the update. id=":thumbup:" border="0" alt="thumbup.gif" />
Good luck with your project.

[JackLamberton]

I would like to be able to add a file (jpg) in an attachment field of a "local" table programmatically? I have about 450 student photos that I want to "attach" to their respective students in the table and then display them individually on a form.
Can someone show me how to add jpg files to a local table, please?
Thanks.
Jack

[theDBguy]

Hi Jack,
The code Bonnie used in Post #7 should also work on local tables. Give it a shot and let us know if you run into any problems.
Just my 2 cents...

[JackLamberton]

Thank you but before I jump into the lake with Bonnie's code, I would like to share with you my environment:
1. Access version 2010
2. Database to be split into front end and back end modules so that the module can run on local machines in a multi-user enviornment
3. My VBA expertise is very limited.
My local table would look like this;

Table tblImages
Field name: StudentID
Field type: Text
Field name: StudentImage
Field type: Attachment
Table property: Primary Key: StudentID

Bonnie's code below with my changes (underlined). I dont know what "FileData" (underlined, italicized and bold) does and where it fits in a table.
And where the code should go? And how and where is it invoked?
Please advise. Thanks.
====================================
Sub LoadFromFile()
Dim mfile
Set db = CurrentDb
Dim rsImages As DAO.Recordset
Set rsImages = db.OpenRecordset("tblImages")
If Not (rsImages.EOF And rsImages.BOF) Then
rsImages.MoveFirst
Do Until rsImages.EOF
rsImages.Edit
Set rsPictures = rsImages.Fields("StudentImage").Value
mfile = rsImages.Fields("StudentID")
mfile = "\\csdfsrv08\campus_wide\ImageStream_2012-13\SchoolData\ImageStream\LID00121111\Images" & mfile
rsPictures.AddNew
rsPictures.Fields("FileData").LoadFromFile mfile
rsPictures.Update
rsImages.Update
rsImages.MoveNext
Loop
End If

[theDBguy]

Hi,
ileData is a property (or a field) of the Attachment field. You just use it as is. For best practice, we'll need to add some explicit variable declarations to your code. For example:
Dim mFile As String
Dim db As DAO.Database
Dim rsPictures As DAO.Recordset
Can you post some sample data of what values are stored in the field "StudentID?" The way you have used it in your code above makes it seem like it is the name of the image file. Is that correct? If not, what is the image file name look like and where do you store it?
Just my 2 cents...