updating ole with sql recordset (2003 office)

Topic

New Reply

Hi all,
I am lost in confusion here. I am trying to update an ole object in a table using the following:

Sub ProcesstableTARReport()

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim Counter As Integer
Dim mySQL As String

On Error GoTo ErrHandler

‘ Reference to database
Set dbs = CurrentDb
‘ Open recordset from table

mySQL = “SELECT Pricing.Modified_Pricing_ID, Pricing.[Technical Report] from Pricing WHERE Pricing.Modified_Pricing_ID= ‘” & [Forms]![Splash Screen]![vLastPricing] & “‘”

Set rst = dbs.OpenRecordset(mySQL, dbOpenDynaset)

‘ mySQL = “UPDATE Pricing ” & _
‘ “SET Pricing.[Desk_TAR_Complete] = True ” & _
‘ “WHERE Pricing.Pricing_ID In (” & vPricingPackage & “)”

‘ DoCmd.RunSQL mySQL ‘for the table named pricing to update TAR ID COde

‘ find number of records
Counter = rst.RecordCount

‘ loop through records of imported table
Do While Not rst.EOF
‘ loop through fields (starting with 3rd field)
‘ originally had For i = 2 To rstTemp.Fields.Count
For i = 0 To Counter
DoCmd.OpenForm “frmSelectFileXP”, acNormal, , , , acDialog
oleTechnicalReport.Class = “Excel.Sheet” ‘ Set class name.
‘ Specify type of object.
oleTechnicalReport.OLETypeAllowed = acOLELinked
‘ Specify source file.
oleTechnicalReport.SourceDoc = Forms![frmSelectFileXP]![vTARReportName]
‘ Specify data to create link to.
‘TechnicalReport.SourceItem = “R1C1:R25C15”
‘ Create linked object.
oleTechnicalReport.Action = acOLECreateLink
‘ Adjust control size.
oleTechnicalReport.SizeMode = acOLESizeZoom
Next i
rst.MoveNext
Loop

ExitHandler:
‘ Clean up
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

In this case, the Pricing Table is a linked table, the object is an excel spreadsheet on our network. The path of the spreadsheet is captured in the line …DoCmd.OpenForm “frmSelectFileXP”, acNormal, , , , acDialog …

Right now as the code exists, I am getting an object required error. This code worked when I was updating the table through a form and updating only one record at a time. I am trying to use the sql statement to generate a recordset of the records I want to update and then scroll through the records udating them in one shot.

I put this code togehter using peices of code that worked individually before when applying the logic to a single reocrd on a form. Don’t know where to go from here.

Can someone point me in the right direction.

Thanks

Reply | Quote

Replies

1) You should remove the lines

For i = 0 To Counter

and

Next i

You don’t do anything with i and I see no reason to loop through the fields of the recordset (the upper bound should have been Counter – 1, by the way: the fields of a recordset start numbering at 0, not at 1).

2) You haven’t defined oleTechnicalReport anywhere in the code. Do you mean the Technical Report field of the recordset?

3) You cannot do it this way. Properties such as Action belong to OLE controls on a form, not to OLE fields in a recordset. So unless you want to get *very* technical, you have to set the OLE field thorugh a form bound to the field.

Reply | Quote

thanks Hans,

Your comments are appreciated. I zoomed in on # 3 real quick. Is there anyway to set the OLE to multiple records at the same time. In the current environment, I can only display one record on the form. I have been able to set the ole using the method in # 3. I was hoping to be able to set the same object to many records at the same time. Is there a query I can run in the back ground after setting the first one and update the other records based on the first?

To give you an idea of what I am trying to accomplish is that when a record is processed, it might be processed by itself or it may be grouped with other records and processesd. If done by itself I am trying to assign the ole object to that record (Already done that). It is this grouped process that has messed things up. When a group is processed, the same ole object applies to the entire group.

Any suggestions?

Thank You
Kevin

Reply | Quote

You’ll have to move through the records of the form, for example by using

RunCommand acCmdRecordsGoToNext

in a loop.

Reply | Quote