Recordset and acOLECreateLink problem

[Billy B]

I am new to recordset and am having a problem and after a week at it thought
I would give the experts a try. I have a form with all unbound controls,
including an image control. On the form I have a button that should update
the table and everything works except the code to update the link in the
table. Below is the code I have so far. Any and all help is appreciated.
Thank you.

Private Sub AddNewRecord()
'***********************************************************
'Procedure to on the Add Plants form to add another record

Dim conn As adodb.Connection, rsPlants As adodb.Recordset
Dim strSql As String, strLoc As String
Set conn = Application.CurrentProject.Connection
Set rsPlants = New adodb.Recordset
strSql = "Select * from tblPlants"
'strLoc = Me.cboLoc.Value
Dim intNextNum As Integer
intNextNum = DMax("PlantID", "tblPlants")
intNextNum = intNextNum + 1

rsPlants.Open strSql, conn, adOpenForwardOnly, adLockPessimistic
'********************* following 2 lines used for testing*****
'********************* have dialog box to find path********
Dim strPathToIt As String
strPathToIt = "E:\Graphics\JPEG's\bbpurplebak9.jpg"

'open the recordset
'check to see if there is a record in the table
If Not rsPlants.BOF And Not rsPlants.EOF Then
With rsPlants
'if table contains data
.AddNew
.Fields("PlantID").Value = intNextNum
.Fields("PlantName").Value = Me.PlantName.Value
.Fields("Alias").Value = Me.Alias.Value
.Fields("BotonName").Value = Me.BotonName.Value
.Fields("PlantType").Value = Me.cboPlantType.Value
.Fields("PlantCat").Value = Me.cboPlantCat.Value
.Fields("GrowthSize").Value = Me.GrowthSize.Value
.Fields("PlantingLoc").Value = Me.cboLoc.Value
.Fields("Description").Value = Me.Description.Value
.Fields("Culture").Value = Me.Culture.Value
.Fields("Moisture").Value = Me.Moisture.Value
.Fields("HardinessZones") = Me.HardinessZones.Value
.Fields("Features") = Me.Features.Value
.Fields("Usage") = Me.Usage.Value
.Fields("PlantWarnings") = Me.PlantWarnings.Value
'******* here is the problem*********
.Fields("Pic").OLETypeAllowed = acOLELinked
.Fields("Pic").SourceDoc = strPathToIt
.Fields("Pic").Action = acOLECreateLink
.Update
End With

'clear contents of form
Me.PlantName.Value = ""
Me.Alias.Value = ""
Me.BotonName.Value = ""
Me.PlantType.Value = ""
Me.PlantCat.Value = Null
Me.GrowthSize.Value = ""
Me.PlantingLoc = ""
Me.Description.Value = ""
Me.Culture.Value = ""
Me.Moisture.Value = ""
Me.HardinessZones = ""
Me.Features = ""
Me.Usage = ""
Me.PlantWarnings = ""
Me.Pic.Value = '*****??????????

Dim Again As String
Again = MsgBox("Add another record?", vbYesNo, "Records")
If Again = vbYes Then
Set rsPlants = Nothing
Set cnncon = Nothing
DelInvalidRows
Exit Sub
Else
DoCmd.Close Me.Name
DelInvalidRows
DoCmd.OpenForm ("frmMenu")
End If
End If

End Sub

 

[Erez Mor]

hi
i cant say i found the problem, but from my experience with access, and i
have some, i found out it's much better to save images' path (folder and file
name) in the database record, than the objects themselfes (as OLEs) the
database tends to explode in size when saving ole data.
it's very simple using a single station/user mdb, but with some thinking,
it's not very hard for multiple users as well.
just change the record's ole field to text and save the full path and
filename of the image.
then, when you display the record on the screen, use the CURRENT event of
the form to load the image to an unbound picture control.

hope it makes sense to you
good luck
Erez.