[AccessD] Running a report in an external database

We use a routine to Open a Remote report.
We only have sometimes problems when close the MDB remote if you have Close
and Compact, then the file could be convert to db1.mdb We check the state of
this file before open.
Normally we put an MDB with all reports external to MDE application, and
make CONFIG.MDB to put parameters to all type reports (Printer, Top, Bottom,
Format to export -xls-snp-txt-rtf-etc...)

Function fOpenRemoteReport(StrMDB As String, StrReport As String, Optional
linkCriteria As String, Optional intView As Variant) As Boolean
Dim objAccess As Access.Application
Dim lngRet As Long
Dim agrega As String
Dim origendatos As String
agrega = StrReport
origendatos = "Select * from ConfigFtra"
On Error GoTo fOpenRemoteReport_Err

If IsMissing(intView) Then intView = acNormal
If IsMissing(linkCriteria) Then linkCriteria = ""
If Len(Dir(StrMDB)) > 0 Then
    Set objAccess = New Access.Application
    With objAccess
        lngRet = apiSetForegroundWindow(.hWndAccessApp)
        lngRet = apiShowWindow(.hWndAccessApp, SW_HIDE) 'ANTES ESTABA EN EL
EJEMPLO COMO NORMAL
        'la primera no suele operar y repetimos la accion
        lngRet = apiShowWindow(.hWndAccessApp, SW_HIDE)
        .OpenCurrentDatabase StrMDB
        .DoCmd.OpenReport StrReport, intView, , linkCriteria
        'Do While Len(.CurrentDb.Name) > 0
        '    DoEvents
        'Loop
    End With
    Else
    MsgBox "Archivo de facturas modelos no encontrado" & vbCrLf & "revise la
configuración del terminal." & vbCrLf & "Archivo= " & StrMDB, vbInformation
+ vbOKOnly, "GESTION DOCUMENTOS EXTERNOS"
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:
    MsgBox "La base de datos esta abierta por otro usuario en modo
exclusivo", vbCritical + vbOKOnly, "STEEL REPORT"
Case 2103:
    MsgBox "El informe " & StrReport & " no se encuentra en la base de
datos", vbCritical + vbOKOnly, "STEEL REPORT"
Case 7952:
    fOpenRemoteReport = True
Case Else:
    MsgBox "Error Nº" & Err.Number & " - " & Err.Description, vbCritical +
vbOKOnly, "STEEL REPORT"
End Select
Resume fOpenRemoteReport_Exit

End Function

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin -
Beach Access Software
Sent: sábado, 19 de marzo de 2005 20:23
To: AccessD at databaseadvisors.com
Subject: [AccessD] Running a report in an external database


Dear List:

Per your instructions I added code to a client's db to run a report in an
external mdb and it works just fine:

Private Sub cmdPrintMemo2Set_Click()
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE from C:\Acessdat\Reports.tUDGlobalMerge"
    DoCmd.RunSQL "INSERT INTO tUDGlobalMerge " & _
                 "IN 'C:\ACESSDAT\Reports.mdb' " & _
                 "SELECT tUDGlobalMerge.* " & _
                 "FROM tUDGlobalMerge"
                 
'Open Reports.mdb hidden, print Report and close that mdb.

Dim acc As Access.Application
Set acc = CreateObject("access.application")
acc.OpenCurrentDatabase ("c:\Acessdat\Reports.mdb") acc.DoCmd.OpenReport
"rptEvictionCase-MemoToSet", acViewNormal acc.CloseCurrentDatabase Set acc =
Nothing

End Sub

Now I get an email from him with the following message:

********************************************
I have had a problem with the code you sent me.  When I run it (that is,
have Forms.mdb print the report in Report.mdb), my computer will no longer
open an access program by double clicking a mdb, either shortcut or regular,
until I reboot.  

In other words, I have the mdb I run my practice with.  I have a short cut
to open it on my desktop.  Once I have run the Forms.mdb, clicking on the
short cut on my desktop, exploring to where the mdb actually resides and
double clicking on that, all get nothing.  From the start button, I CAN open
an instance of MS Access and then once it is open, open an mdb, but other
than that, its as if I had unregistered all the mdbs.  ????

Seems to me that there's an object open somewhere.  If I went to the
processes tab of the task manager I'll bet I'd see more than one instance of
msaccess.
*******************************************

Anyway, any hints as to how to solve this one?

MTIA

Rocky Smolin
Beach Access Software
http://www.e-z-mrp.com
858-259-4334
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com