UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Runtime - Can't Print Report In Another Db, Access 2016    
 
   
tina t
post Feb 18 2020, 04:25 PM
Post#1



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


hi folks, i'm using an A2016 .accde 32-bit in A2016 Runtime, on Win7. i have the same setup, using a copy of the same .accde, on another PC with full-version A2016. in the "full version", i can open a form, choose a record from the underlying table, and run code that opens another db (.accdb), prints a report (hard copy or pdf) for the record i chose in the form, and then closes the other db.

the above doesn't work in the runtime. i don't get any error msg, but nothing prints. does the runtime not allow manipulating a second db programmatically from the first db?

tia,
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Feb 18 2020, 04:28 PM
Post#2


UA Moderator
Posts: 77,715
Joined: 19-June 07
From: SunnySandyEggo


How exactly were you opening the other db? If you're using anything like CreateObject, then I don't think that will work. Runtimes cannot create a db object.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Feb 18 2020, 10:22 PM
Post#3



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


hi DBguy and thanks for responding. here's the code i'm using:

CODE
Public Sub isExternalPrintCode(ByVal strForm As String, ByVal strRpt As String, _
    ByVal strWhere As String, ByVal i As Integer, strDbFilepath As String)

    On Error GoTo Err_Sub

    Dim app As Access.Application
    
    Set app = New Access.Application
    app.OpenCurrentDatabase strDbFilepath, False

    app.DoCmd.OpenReport strRpt, acPreview, , strWhere
    app.DoCmd.PrintOut , , , , i
    app.DoCmd.Close acReport, strRpt, acSaveNo
    app.Application.CloseCurrentDatabase
    Set app = Nothing
    
Exit_Sub:
    DoCmd.SelectObject acForm, strForm
    Exit Sub

Err_Sub:
    Select Case err.Number
        Case 2103  ' report not in active db
            app.Application.CloseCurrentDatabase
            Set app = Nothing
            
            Set app = New Access.Application
            app.OpenCurrentDatabase DLookup("fpPath", "tbl00Filepaths", "fpID=31"), False
            app.DoCmd.OpenReport strRpt, acPreview, , strWhere
            app.DoCmd.PrintOut , , , , i
            app.DoCmd.Close acReport, strRpt, acSaveNo
            app.Application.CloseCurrentDatabase
            Set app = Nothing
            Resume Exit_Sub
        Case Else
            Resume Next
    End Select

End Sub

where am i going wrong here, re using the code in a runtime environment?

tia,
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
FrankRuperto
post Feb 18 2020, 11:18 PM
Post#4



Posts: 789
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA


Could it be that in your first PC the report's printer settings are set to a specific printer that doesn't exist on the second PC?
Have you looked in the printer queues to see if there's anything sitting there?
This post has been edited by FrankRuperto: Feb 18 2020, 11:33 PM

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
 
theDBguy
post Feb 18 2020, 11:51 PM
Post#5


UA Moderator
Posts: 77,715
Joined: 19-June 07
From: SunnySandyEggo


Hi Tina. Instead of Resume Next in your Error Handler, try doing a MsgBox Err.Description. I am thinking the Set app = New Access.Application may be failing. Just a thought...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Feb 19 2020, 12:48 AM
Post#6



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


good idea, DBguy. i'll try to take a look tomorrow.

thanks for the suggestions, Frank. i'm able to print reports that are in the db i'm working in - both full version A2016 and runtime - just not reports in the "external" db that i'm opening (or trying to open) programmatically while using the runtime.

tia,
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
tina t
post Feb 22 2020, 01:37 AM
Post#7



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


QUOTE
Instead of Resume Next in your Error Handler, try doing a MsgBox Err.Description. I am thinking the Set app = New Access.Application may be failing. Just a thought...

hi DBguy, sorry it took me so long to get back. this week has been...not fun. anyway, i did as you suggested. the Set app line runs, but the next line

app.OpenCurrentDatabase strDbFilepath, False

fails with

Attached File  Runtime_PrintCertError.JPG ( 19.91K )Number of downloads: 0


i'm not sure what to do at this point, or if anything can be done. is there another way to manipulate another .accdb file programmatically from the VBA running in a Runtime environment? i know this thread is starting to get a bit old; if anyone has anything to contribute, i'd be grateful. even a solid "nope, can't be done any way" would be helpful (if not happy-making) so i don't keep spinning my wheels. :)

tia
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cheekybuddha
post Feb 22 2020, 06:18 AM
Post#8


UtterAccess Moderator
Posts: 12,619
Joined: 6-December 03
From: Telegraph Hill


Hi Tina,

Have you tried shelling to the app and using a command line switch to trigger the print?

Create a function in your remote database:
CODE
Function PrintTest(strMsg As String) As Boolean

  MsgBox strMsg
  PrintTest = True

End Function


Then create a new text file - name it 'remote_db_fn.vbs', save it in the same folder as your first db (the controlling db) and paste in the following code:
CODE
Option Explicit
' Usage:
' C:\>cscript.exe C:\Path\To\remote_db_fn.vbs "C:\Path\To\Target.accdb" "NameOfFunction" ["Argument1", ["Argument2", ["Argument...n"]]]

Const SLEEP_WHILE_ACCESS_OPENS = 3000

Dim acc, wsh, _
    args(), i, _
    dbpath, fn, _
    accpath, _
    param_msg, success, _
    has_args, exec_cmd

' At least 2 parameters required for this script
' 1. Full Path to db
' 2. Name of function to call within the db
' Remaining parameters are function arguments (Max: 30)
With Wscript.Arguments
  If .Count >= 2 And _
     .Count <= 32 Then
    If (.Count - 2) > 0 Then
      has_args = True
      ReDim args((.Count - 2) - 1)
    End If
    For i = 0 To .Count - 1
      Select Case i
      Case 0
        dbpath = .Item(i)
      Case 1
        fn = .Item(i)
      Case Else
        args(i - 2) = .Item(i)
      End Select
    Next
  Else
    If .Count > 32 Then
      param_msg = "many"
    Else
      param_msg = "few"
    End If
    param_msg = "Too " & param_msg & " parameters for this script." & Chr(10) & Chr(13) & Chr(10) & Chr(13) & _
                "Parameters:" & Chr(10) & Chr(13) & _
                "1. Full path to Access database" & Chr(10) & Chr(13) & _
                "2. Name of function to be called" & Chr(10) & Chr(13) & _
                "3 - 32. Function arguments (Max: 30)"
    MsgBox param_msg, vbOKOnly + vbInformation, "Hmmm ..."
    Wscript.Quit
  End If
End With
Set wsh = CreateObject("WScript.Shell")

On Error Resume Next
Set acc = GetObject(, "Access.Application")
If err Then
  err.Clear
  On Error GoTo 0
  accpath = GetProgramPath(".accdb")
  wsh.Run """" & accpath & """ """ & dbpath & """"
' Allow Access some time to open
  Wscript.Sleep SLEEP_WHILE_ACCESS_OPENS
  Set acc = GetObject(, "Access.Application")
Else
  On Error GoTo 0
  If acc.CurrentDb.Name <> dbpath Then
    MsgBox acc.CurrentDb.Name, vbOKOnly, "Wrong db!"
    Set acc = Nothing
  End If
End If
If Not acc Is Nothing Then
  With wsh
    ' success = .AppActivate("Microsoft Access")
    success = .AppActivate(GetPID("msaccess.exe"))
    If Not success Then .SendKeys "% r"       ' Restore if minimised
  End With
  exec_cmd = "acc.Run fn"
  If has_args Then
    For i = 0 To UBound(args)
      exec_cmd = exec_cmd & ", args(" & i & ")"
    Next
  End If
  Execute exec_cmd
Else
  MsgBox "Can't find " & dbpath & "!"
End If

Function GetProgramPath(ext)
' https://stackoverflow.com/questions/19867781/determine-default-program-to-open-given-file-extension-vbs'
  Dim strProg, strProgPath
  If Left(ext, 1) <> "." Then ext = "." & ext
  If wsh Is Nothing Then Set wsh = CreateObject("WScript.Shell")
  ' Get Program Association Handle
  With wsh.Exec("cmd.exe /c assoc " & ext)
    strProg = .StdOut.ReadLine()
    strProg = Split(strProg, "=")(1)
  End With
  ' Get Path To Program
  With wsh.Exec("cmd.exe /c ftype " & strProg)
    strProgPath = .StdOut.ReadLine()
    strProgPath = Split(strProgPath, """")(1)
  End With
  ' Return the program path
  GetProgramPath = strProgPath
End Function

Function GetPID(app)
  Dim Processes, Process
  Set Processes = GetObject("winmgmts:").InstancesOf("Win32_Process")
  For Each Process In Processes
    If StrComp(Process.Name, app, vbTextCompare) = 0 Then
      GetPID = Process.ProcessId
      Exit For
    End If
  Next
End Function


Then, in the first controlling db create a function:
CODE
Function PrintInRemoteDB() As Long

  Dim strDbFilepath As String , strShell As String, strFunc As String, strArg As String
  Const SCRIPT_FILE As String = "remote_db_fn.vbs"

  strDbFilepath = Chr(34) & "C:\Path\To\RemoteDB.accde" & Chr(34)
  strFunc = Chr(34) & "PrintTest" & Chr(34)
  strArg = Chr(34) & "Hello Tina - I'm printing!" & Chr(34)
  strShell = "cscript.exe  " & Chr(34) &  CurrentProject.Path & "\" & SCRIPT_FILE & " Chr(34) & " " & strDbFilepath & " " & strArg
  PrintInRemoteDB = Shell(strShell)

End Function


Then, in the Immediate Window try:
CODE
?PrintInRemoteDB


No guarantees it will work, but if it does, we can work on importing the vbs code into your db.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Feb 22 2020, 05:51 PM
Post#9



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


wow, David, thanks! i'll work on it this week, can't guarantee i'll get to it right away - depends on how many fires i have to put out each day, and whether mgt pulls the plug on my OT. but i'll definitely give it a try, and hopefully post back at least by the end of the week. sorry to drag the thread-life out so long, but it's unavoidable, i'm afraid. tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
cheekybuddha
post Feb 23 2020, 07:20 AM
Post#10


UtterAccess Moderator
Posts: 12,619
Joined: 6-December 03
From: Telegraph Hill


Keep us posted! thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
tina t
post Feb 25 2020, 09:03 PM
Post#11



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


hi folks. David, i haven't tried your method yet, though i intend to. here's my question, though. i tried the following simple code, as

CODE
Public Sub isTestMe()

    Dim obj As Object
    
    Set obj = GetObject("C:\MyFolder\xxx.accdb")
    
    obj.DoCmd.OpenReport "PX"
    
    obj.Application.Quit
    
    Set obj = Nothing
    MsgBox "done"

End Sub

and it ran. opened a second session of Access, opened the second database, printed the report, then closed the second database, and finished. no errors.

that was in the full version of Access. in the Runtime, it still errs out. this time the error reads

"429 ActiveX component can't create object"

on the Set obj... line. what i don't get is that i can manipulate an Excel object via code in the Runtime, with no problem, as

CODE
    Dim xlApp As Object
    Dim MyXL As Object

    Set xlApp = CreateObject("excel.application")
    Set MyXL = GetObject(DLookup("fpPath", "tbl00FilePaths", "fpID = 4") _
        & DLookup("[linkPath] & [linkFile]", "tbl02Links", "link_Type = '" _
        & Me!req_Type & "' And link_catID = " & Abs(Me!reqTest)))

    MyXL.worksheets(DLookup("linkWkshtIndex", "tbl02Links", "link_Type = '" _
        & Me!req_Type & "' And link_catID = " & Abs(Me!reqTest))).Activate
    MyXL.ActiveSheet.PrintOut
    MyXL.Application.DisplayAlerts = False

    Set MyXL = Nothing
    xlApp.Quit
    Set xlApp = Nothing

the above code runs without issue, opening an existing Excel file, writing data into specified cells, printing the worksheet, then closing the file without saving and closing Excel.

it's not that the Runtime won't open two sessions, because it will. i just tested it. <sigh> i don't get it. this just shouldn't be that hard. comments, ideas, anyone?

tia,
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Feb 25 2020, 09:20 PM
Post#12


UA Moderator
Posts: 77,715
Joined: 19-June 07
From: SunnySandyEggo


Hi Tina. I think this is related to what I was saying earlier. In an Access Runtime environment, creating an Access object is not allowed because there is no "full" version of Access (I imagine, basically, a Runtime version is just missing this part or it's disabled). So, I know you can't do CreateObject("Access.Application"), but I wasn't sure about GetObject(). Now, I guess we know. As for being able to use GetObject() for an Excel file, I suppose that's allowed because you have a full version of Excel installed on the machine. So, the root of the problem is, the machine doesn't have a full version of Access, so things that only a full version can do will error out in a Runtime-only machine.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 12:20 PM
Post#13


UtterAccess Moderator
Posts: 12,619
Joined: 6-December 03
From: Telegraph Hill


Hi,

If DBG is correct, and he usually is, then this might be a non-starter.

The only other possibility would be to create a macro in your remote db that can call your Print code (using action: Run) - then Shell to the remote db with a command-line switch to call the macro. The only downside is that I don't know then how to pass arguments. shrug.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Feb 26 2020, 12:31 PM
Post#14


UA Moderator
Posts: 77,715
Joined: 19-June 07
From: SunnySandyEggo


QUOTE (cheekybuddha)
- then Shell to the remote db with a command-line switch to call the macro.

Good idea. One way to pass an argument to the macro is by using the /cmd switch and the Command() function.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cheekybuddha
post Feb 26 2020, 12:40 PM
Post#15


UtterAccess Moderator
Posts: 12,619
Joined: 6-December 03
From: Telegraph Hill


I think you're on to something there, Leo

--------------------


Regards,

David Marten
Go to the top of the page
 
theDBguy
post Feb 26 2020, 12:43 PM
Post#16


UA Moderator
Posts: 77,715
Joined: 19-June 07
From: SunnySandyEggo


Like. thumbup.gif

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Feb 26 2020, 02:44 PM
Post#17



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


QUOTE
As for being able to use GetObject() for an Excel file, I suppose that's allowed because you have a full version of Excel installed on the machine.

hi DBguy, yes, i realized about two hours after i posted that it was a stupid question - but of course it was too late to go back and delete it! chalk it up to frustration and not enough sleep for the past two weeks. :( tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
theDBguy
post Feb 26 2020, 02:58 PM
Post#18


UA Moderator
Posts: 77,715
Joined: 19-June 07
From: SunnySandyEggo


Hi Tina. No worries. We've all been there and done all that. Cheers and Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
tina t
post Feb 26 2020, 02:59 PM
Post#19



Posts: 6,437
Joined: 11-November 10
From: SoCal, USA


guys, thank you both. you've given me an idea, and i'll try it out hopefully later today. probably i'll be back crying for more help, but cross your fingers for me! :) tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    31st March 2020 - 01:49 AM