I want to print a series of reports. All are driven by the same report except that the report.caption varies. I am using an ADODB connection and recordset to get the successive captions.
First I define and open the ADODB connection as follows:
Dim Cnxn As New ADODB.Connection
Dim strCnxn As String
strCnxn = "Provider=Microsoft.Jet.OLEDB.4.0;" ' Access 2003
strCnxn = strCnxn & "Data Source=" & Chr(34) & CurrentDb.Name & Chr(34)
Cnxn.Open strCnxn
Then I define, open, and move through the RecordSet (which I've determined isn't relevant to this problem).
For each record in the rsList recordset (all of which works fine), I am trying to execute the following commands:
DoCmd.OpenReport "Sales Report", acViewDesign
Reports![Sales Report].Caption = "New Report Caption" ' this is replaced by rsList!RptName normally
DoCmd.Close acReport, "Sales Report", acSaveYes
All this works fine if I comment out the "Cnxn.Open strCnxn" statement. However, apparently that statement causes it to fails. It appears to open correctly, the caption is changed, however, the Close, Save doesn't save the new caption. If I simply comment out the Cnxn.Open statement, it updates the report caption.
Is there something in the Open Connection process that put the objects in an "exclusive" state which prevents the update from working?
Eddie