Reports in library databases

  • Thread starter Thread starter ToniMaura
  • Start date Start date
T

ToniMaura

Is posible in Access (2000, XP...) make a report, save it
in a library database, and then call it other database
using the data stored in this database (not the data
stored in the library database). If yes, how...
 
ToniMaura said:
Is posible in Access (2000, XP...) make a report, save it
in a library database, and then call it other database
using the data stored in this database (not the data
stored in the library database). If yes, how...
Click to expand...

Yes, it's possible. There are issues of maintaining the
dependencies between the **three** databases (front end
program, back end data, and library).

To open a report in a library database, the front end must
set a reference to the library mdb file. Then, the library
must contain a public function that opens the report (using
the OpenReport method).

To get a report in the library to use the data in the back
end mdb file, you have to tell the report where the data
resides. The library could either link to the data tables
in the back end or use the IN phrase in the report's record
source query's FROM clause, BUT these techniques will
prevent the library from being used in other applications
with different front/back ends. To make the library
general, you have to either set the links or modify the
report's record source on the fly.
 
I prefer change the report recordsource, how I do it with
code 'on the fly', opening the report in design mode and
then change the recordsource, saving changes and then
print?
 
ToniMaura said:
I prefer change the report recordsource, how I do it with
code 'on the fly', opening the report in design mode and
then change the recordsource, saving changes and then
print?
Click to expand...

That would then make different versions of the library mdb,
which kind of defeats the purpose of having a library. The
general way is to use code in each report's Open event to
Incorporate the IN phrase into the record source query's
From clause. In simple cases it might be something along
these lines:

Sub Report_Open()
Dim strSQL As String
Dim intPos As Integer
intPos = InStr(Me.RecordSource, " FROM ")
If intPos = 0 Then MsgBox("No From clause") : Exit Sub
IntPos = InStr(intPos + 6, Me.RecordSource, " ")
If intPos = 0 Then intPos = Len(Me.RecordSource)
strSQL = Left(Me.RecordSource, intPos) _
& "IN """ & pathtobackend & """ " _
& Mid(Me.RecordSource, intPos + 1)
Me.RecordSource = strSQL
. . .
End Sub

But that's just for the simple case of the report already
having an SQL statement in its record source. It gets even
trickier if the query can contain any JOIN clauses or other
complex stuff. It also leaves the open question of how the
report determines pathtobackend, but you'd have to have some
such mechanism regardless of which approach you take.

I really think you'd be better off having the library mdb
link to the tables in the backend mdb using a relinking
procedure from the web such as
http://www.mvps.org/access/tables/tbl0009.htm
At least this allows you to keep the reports independent of
the library management apparatus.

As you can see, placing forms/reports in a library mdb is
not a simple arrangement to set up and regardless of how you
approach it, it takes a lot of thought to work out all the
details.
--
Marsh
MVP [MS Access]


 
Back
Top