How can I dynamically load a BLOB header into a report?

[David Lightman Robles]

Hi all,

I'm trying to migrate a single-company database into multi-company. The
front-end is written in MS Access 2000 and the data stored en several
backend SQL Servers. Each company has its own database (dbcompany1,
dbcompany2, etc). All databases share the same schema, tables names,
structures, etc. Hence I have to maintain only 1 version of the MS Access
front-end.

The challenge is as follows:

I have several reports that need to be customised with each company name,
logo, etc., just the header and footer of some reports. I have a table that
stores the header and footer for each company in a BLOB field, as a Word OLE
object.

I want my reports to reload the proper header depending the company the
front-end is run from: When the front end is run for company1, I want
Header1 to be shown in the report; when run for company2, a different header
Header2 must be shown. I must make clear that I just want to have 1-report
that dinamically changes, and not n-different-reports, each of them
customised for the n-companies.

I have tried to achieve this using an OLE Object at the top of the report,
setting its SourceDoc to C:\Program Files\MyDatabase\Header.doc and setting
it to be linked not embeeded. And also having:

Private Sub Report_Open(Cancel As Integer)
StoreCurrentCompanyHeaderIn('C:\Program Files\MyDatabase\Header.doc')
Me.HeaderOLE.Requery
End Sub

So, each time I call the report, the procedure StoreCurrentCompanyHeaderIn
saves the correct header stored as a BLOB in my database as a file into the
location that the HeaderOLE is linked to, and try to requery it so that the
new header is shown.

However, this does not work. It seems that the OLE object can just be
updated in design time, and not in run time, on the fly. Has anyone face
this kind of problem before? Can I achieve the same results using other
aproximation to the problem? It seems very simple and common (I think) but
can't find a way to make it work.

For simplicity, if you don't want to work with databases and BLOB objects,
you can just create 2 different headers using MS Word, i.e. Header1.doc and
Header2.doc and try to show them on a report depending a variable is set to
1 or 2. Remember, just 1 report. The headers are written with Word because
they are somewhat complex (with tables, images, email addresses, webpages,
etc). I couldn't find a way to really customise each company report without
using a completely blank rectangle what could be filled as desired (leaving
a place for the name of the company and other for the company logo is not
enough).

Thanks in advance... and happy new year.

 

[Maury]

I've been trying to set up something similar.
I wanted to have a (logo) bitmap stored in an OLE object
field in the back-end table. The report contains an
image control, since there's only one logo, many reports,
and I needed to save space in the front-end database.

Anyway, in your case, could the report control be a
bound OLE object, and then design a query to associate
the appropriate OLE object for the appropriate company?
It seems like Access should handle this just like other
types of bound data fields.

-----Original Message-----
Hi all,

I'm trying to migrate a single-company database into multi-company. The
front-end is written in MS Access 2000 and the data stored en several
backend SQL Servers. Each company has its own database (dbcompany1,
dbcompany2, etc). All databases share the same schema, tables names,
structures, etc. Hence I have to maintain only 1 version of the MS Access
front-end.

The challenge is as follows:

I have several reports that need to be customised with each company name,
logo, etc., just the header and footer of some reports. I have a table that
stores the header and footer for each company in a BLOB field, as a Word OLE
object.

I want my reports to reload the proper header depending the company the
front-end is run from: When the front end is run for company1, I want
Header1 to be shown in the report; when run for company2, a different header
Header2 must be shown. I must make clear that I just want to have 1-report
that dinamically changes, and not n-different-reports, each of them
customised for the n-companies.

I have tried to achieve this using an OLE Object at the top of the report,
setting its SourceDoc to C:\Program

Files\MyDatabase\Header.doc and setting

 

[David Lightman Robles]

If I include the BLOB field in the query bound to the report, then every row
of the resultset will have a new field of aprox. 40Kb. size. Previous
reports of 500 rows of just 1Kb of data in every row, would then requiere
the SQL Server to transfer 20,5Mb of data to the MS Access client instead of
500Kb. That is not efficient at all. There must be another solution not
requiring the OLE Object included in the recordset.

¿Any other idea?