VBA for Attachments?

S

SweetSolutions

New member

Local time

Today, 08:56

Joined

Mar 30, 2009

Messages

4

• Mar 30, 2009

• #1

Good Day,
Does any body know of a VB code or Module to do Attachments automatic in Access 2007?
I have a DB set up with a Table containing StockCode and Attachment Fields, what I am currently doing is attaching each file manualy, and there are 11 thousand to do. it is all .jpg images renamed to the Stock Code in My Table.

Anything would help.

Regards

 

LPurvis

AWF VIP

Local time

Today, 06:56

Joined

Jun 16, 2008

Messages

1,269

• Mar 30, 2009

• #2

Oli's kindly provided a complete example.
http://www.access-freak.com/tutorials.html#Tutorial07

Welcome to AWF.

 

HiTechCoach

Well-known member

Local time

Today, 00:56

Joined

Mar 6, 2006

Messages

4,357

• Mar 30, 2009

• #3

Welcome!

See if this gets you started:

Code:

On Error GoTo Err_AddImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.Edit

Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg")

rsChild.Update
rsParent.Update

Exit_AddImage:

Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_AddImage:

If Err = 3820 Then
MsgBox ("File already part of the multi-valued field!")
Resume Next

Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_AddImage

End If

 

S

SweetSolutions

New member

Local time

Today, 08:56

Joined

Mar 30, 2009

Messages

4

• Mar 30, 2009

• #4

Thanks for your replies,
Got some questions if you don't mind.

The Sample Code that you gave me, where do I put that code in?
My Table that I am using is Called "Items with Pictures"
the 2 fields that I have is Stock Codes and Attachment.
The Pictures are all located under C:\Desktop\Stock Pictures.
The Pictures have all got a Unique Number(9 digets) and that would be my link to the Stock Codes.

Thank you for your help, you are great!
Greatings from Namibia

 

S

SweetSolutions

New member

Local time

Today, 08:56

Joined

Mar 30, 2009

Messages

4

• Apr 1, 2009

• #5

Hi There,

I created a TestDb, and got it working from a Form,
Works nice, BUT, and here is my problem...
I need to Change the Code every time cause it only Attaches the Picture that was in the Code.

I need to do it with 11000 Pictures?, all as Unique records?

Regards

 

LPurvis

AWF VIP

Local time

Today, 06:56

Joined

Jun 16, 2008

Messages

1,269

• Apr 1, 2009

• #6

Well you can't possibly hard code the image path.
You need to select your location and iterate through the files therein.
I don't know how you want to create the related record (if they're realted to anything else other than being a simple image storage row).

The simplest example would be using Dir to enumerate the files in a folder.

 

S

SweetSolutions

New member

Local time

Today, 08:56

Joined

Mar 30, 2009

Messages

4

• Apr 1, 2009

• #7

Query2STOCK_CODEExpr1001BIN_CODEPictureData.Picture.FileDataGROUP CLASSESFLAG PICTURE423751514ACTUATOR:ELECTRO-PNEUMATIC423751514.JPG1
4810FLAG762155041ACTUATOR:ELECTRO-PNEUMATIC762155041.JPG1
4810FLAG423723235ACTUATOR:ELECTRO-PNEUMATIC423723235.JPG1
4810FLAG423751516ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751516.JPG1
4810FLAG423751519ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751519.JPG1
4810FLAG423751521ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751521.JPG1
4810FLAG423751520ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751520.JPG0
4810FLAG423751518ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751518.JPG0
4810FLAG423751512ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751512.JPG0
4810FLAG423751511ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751511.JPG0
4810FLAG423751517ACTUATOR:HYDRAULIC-PNEUMATIC;ROTARY423751517.JPG0
4810FLAG
Hi this is what I am doing at the moment, Manual copy field BIN_CODE, Double clicking on field Picture(Attachment). in the Next Screen(Choose File) I copy the BIN_CODE in, then Enter, then OK....
Thanks

 

E

elyamani

New member

Local time

Yesterday, 22:56

Joined

Jan 4, 2013

Messages

4

• Apr 1, 2013

• #8

Hay Guys
The above link is great ! but how i create dialog box and select the file i want to attach ?

appreciate your support

 

E

elyamani

New member

Local time

Yesterday, 22:56

Joined

Jan 4, 2013

Messages

4

• Apr 1, 2013

• #9

I`m found a solution but the problem is it is attaching the file at the first record only ! i want to pick the record which i`ll attach for ex
i want to attach file at record 100 so, how could i do that ?
appreciate your help guys