how to put several reports together from different databases

[gc]

Hi!
May be this is a stupid question. I have several reports in different Access databases. Now I want to put these reports together and print it on one report.
Any idea will be greately appreciated and thanks in advance!
george

 

[SgtJarrow]

Just pick one database as the main one, link all the other tables into this one, and as long as you have unique fields and the like, Access doesn't care. Access views a linked table and a table in the database basically the same. So if you are able to establish the related fields, you would simply create the report you want as you normally would. The linked tables act a regular tables. If you need more explaination, please let me know. "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[gc]

Hi! Rebert,
Thank you for your quick response.
I know what you said. This means that I have to link all the tables which related the repoerts. I need import all related querries and reports also, Right?
What I'm thinking is that is there an easier way to get the reports from other database directly?
Thanks again!
George

 

[SgtJarrow]

If all you need to be able to do is print/view them (in other words, you don't need to make any data entries, etc that affect them), you could probably just call the other database and then print the report. I am not familiar with this yet, so I can't help you out. But it is something like creating a connection to the other database and then calling the report. "Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[gc]

Hi! Robert,
Thank you again for your idea and that is exactly what I want.
Now I have to find how to do it. Can someone help me?
Thanks a lot in advance!
George

 

[SgtJarrow]

OK....I found this while surfing the web. I haven't tested it, but you should be able to make it work exactly for what you need it. It came from another Access forum site.

Good Luck!



(Q) How can I open reports present in an external database through Automation?

(A) Access 97 gives us a new method, OpenCurrentDatabase, a member of the Application object. The following code uses this method to get to a report in an external database.

'************ Code Start *************
Private Declare Function apiSetForegroundWindow Lib "user32" _
Alias "SetForegroundWindow" _
(ByVal hwnd As Long) _
As Long

Private Declare Function apiShowWindow Lib "user32" _
Alias "ShowWindow" _
(ByVal hwnd As Long, _
ByVal nCmdShow As Long) _
As Long

Private Const SW_MAXIMIZE = 3
Private Const SW_NORMAL = 1

Function fOpenRemoteReport(strMDB As String, _
strReport As String, _
Optional intView As Variant) _
As Boolean
Dim objAccess As Access.Application
Dim lngRet As Long

On Error GoTo fOpenRemoteReport_Err

If IsMissing(intView) Then intView = acViewPreview

If Len(Dir(strMDB)) > 0 Then
Set objAccess = New Access.Application
With objAccess
lngRet = apiSetForegroundWindow(.hWndAccessApp)
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
'the first call to ShowWindow doesn't seem to do anything
lngRet = apiShowWindow(.hWndAccessApp, SW_NORMAL)
.OpenCurrentDatabase strMDB
.DoCmd.OpenReport strReport, intView
Do While Len(.CurrentDb.Name) > 0
DoEvents
Loop
End With
End If
fOpenRemoteReport_Exit:
On Error Resume Next
objAccess.Quit
Set objAccess = Nothing
Exit Function
fOpenRemoteReport_Err:
fOpenRemoteReport = False
Select Case Err.Number
Case 7866:
'mdb is already exclusively opened
MsgBox "The database you specified " & vbCrLf & strMDB & _
vbCrLf & "is currently open in exclusive mode. " & vbCrLf _
& vbCrLf & "Please reopen in shared mode and try again", _
vbExclamation + vbOKOnly, "Could not open database."
Case 2103:
'form doesn't exist
MsgBox "The report '" & strReport & _
"' doesn't exist in the Database " _
& vbCrLf & strMDB, _
vbExclamation + vbOKOnly, "report not found"
Case 7952:
'user closed mdb
fOpenRemoteReport = True
Case Else:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "Runtime error"
End Select
Resume fOpenRemoteReport_Exit
End Function
'************ Code End *************
"Only two things are infinite, the universe and human stupidity, and I'm not sure about the former." Albert Einstein.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[gc]

Hi! Robert,
Thank you very much for your help!
I'll test it.
George

 

[gc]

Hi!Robert,
There are two functions apiSetForegroungWindow and apiShowWindow in your code. Could you tell me the detail?
Thank you very much!
George

 

[SgtJarrow]

George,
I took a quick look at this,......like I said, I found this code while surfing the web.....I did not write it. But aside from that, the function appear to be api calls to a dll library. Api calls are used to handle things like opening, closing, moving windows, playing sounds in your database....basically anything not "inherent" to Access. These two functions seem to tie into the code because you are opening another database using your active database and therefore cannot see the objects in it. It seems to be using these function in order to have the reports "appear" to be part of your database. I am by no means an api expert.....asiding from copying someone else's code and tweaking it, I don't understand a lot of it. If you need more explanation, I'll try to dig something up.... Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[gc]

Robert,
Thank you very much for your quick response. I don't know the api call. If you can do something for me. It will be great.
Thanks a lot!
George

 

[SgtJarrow]

You shouldn't even have to worry about the api call stuff....they are defined at the top of the module to be used by the function itself.....Access knows what to do....

Here's what you need to do with this.....save the above code in a new module....just copy-paste it in, make sure you have no red lines, and save it. Doesn't matter what you name it.....Access doesn't care....

Then, to open a report from another databse just use the following code....

Call fOpenRemoteReport("name of .mdb file where report is", "name of report object in the .mdb file&quot

And that's it....Access will handle all the rest....if you want the report sent directly to the printer instead of viewing it, use the following....

Call fOpenRemoteReport("name of .mdb file where report is", "name of report object in the .mdb file", acPrint)

I tested it out and it works.....just remember that you will probably need to provide the full path to the file with the .mdb name....i.e. "C:\Documents\test.mdb" Let me know how this works for you.
Programming isn't a profession of choice.
It's a profession of calling...
"Hey Programmer, your application broke again!"

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com