• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

AUTO REPORT ACCESS/START COMMAND

Hello all.  Does anyone know how I can setup an Access database to open up and run through a table I have of report names that exist and print directly to a default printer on a scheduled basis?  Any suggestions?  Thanks
0
InvisibleMan
Asked:
InvisibleMan
  • 12
  • 9
  • 8
1 Solution
 
PsychoDazeyCommented:
You could build a small mdb that is in your startup menu and runs minimized.  When the date came up that it had to print the reports, it could open up the other DB and use a docmd.Openreport rst![FieldName]
rst.Movenext.....
and it will open and run the report.
0
 
InvisibleManAuthor Commented:
Any idea how I in the command prompt pass a set database password.  Not the MDW User/Pass I just have a database password set.  Thanks.
Here is what I have so far:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "G:\Bob\MyDB" /X AutoReport

If I add "/Pwd reports"
it goes to the system MDW UName/Pass.  I only have a database password set and I need to know how to pass that thanks.
0
 
cjswimmerCommented:
paste this into a module:

Public Function PrintReports()
On Error Resume Next
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT ReportName from tblReports")
    Do Until rs.EOF
        DoCmd.OpenReport rs(0), acViewNormal
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Quit
End Function


then make a macro called macPrintReports with a RunCode command.  In the function name, put PrintReports().

Make a file shortcut anywhere on your hard drive with the following text:
"C:\Program Files\Microsoft Office\Office\Msaccess.exe" "c:\YourDatabase.mdb" /x macPrintReports


I'm not sure about the scheduling yet but I'll check it out.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
PsychoDazeyCommented:
Please give me the full line of code that goes before this.
0
 
InvisibleManAuthor Commented:
cjswimmer thanks I am almost there.  What do I need to pass for the DATABASE PASSWORD?  It is not /pwd because that is going to the system MDW.  IT is the option in Access 2000 to set password I have set one that I need.  Any idea?
0
 
PsychoDazeyCommented:
If you create a macro and name it autoexec, it will automatically run when you open the db, therefore, I dont think you need to pass that in the code.
0
 
InvisibleManAuthor Commented:
cjswimmer thanks I am almost there.  What do I need to pass for the DATABASE PASSWORD?  It is not /pwd because that is going to the system MDW.  IT is the option in Access 2000 to set password I have set one that I need.  Any idea?
0
 
PsychoDazeyCommented:
CJS-
I am tired of you stealing my ideas.  I am reporting you to EE!
0
 
cjswimmerCommented:
I'll tell you're wife that you were hitting on me...
0
 
InvisibleManAuthor Commented:
First one to get me the answer to the password will get the points.
0
 
cjswimmerCommented:
well, according to:
http://support.microsoft.com/support/kb/articles/Q161/1/77.asp?LN=EN-US&SD=gn&FR=0&qry=command%20line%20password&rnk=2&src=DHCS_MSPSS_gn_SRCH&SPR=ACC
you cannot supply a database level password in the command-line options.  I'm trying to work on a workaround for you...
0
 
cjswimmerCommented:
you might have to implement user level security instead...
0
 
PsychoDazeyCommented:
Will this funcion work?
Function Establish_Connection ()
         Dim db As Database, dbcurr As Database
         Dim ds As Recordset
         Set dbcurr = CurrentDB()
         Set db = OpenDatabase("", False, False, _
            "ODBC;DSN=<data source name>;" & _            
            "UID=<UserID>;PWD=<Password>;DATABASE=" & _    
            "<database name>;")
         Set ds = db.OpenRecordset("<any table existing in the
          database defined by DATABASE above>")
      End Function
0
 
PsychoDazeyCommented:
This is actually the reverse of your problem.  It tells you how to force the db to ask for a pass word, but if you read the cause, maybe you can get yours to not ask for a password:
You have not specified a Microsoft Access workgroup information file (System.mdw in Microsoft Access version 7.0 or 97, or System.mda in version 1.x and 2.0) to use with the Microsoft Access ODBC data source. The workgroup information file contains a list of users, groups, and passwords.

If you do not specify a workgroup information file, ODBC will open your Microsoft Access database (.mdb) file without a password as a member of the Users group, with the permissions of the Users group.

http://support.microsoft.com/support/kb/articles/Q112/3/46.asp?LN=EN-US&SD=gn&FR=0&qry=database%20password&rnk=14&src=DHCS_MSPSS_gn_SRCH&SPR=ACC
0
 
InvisibleManAuthor Commented:
I don't think so PsychoDazey because that is the UID/Pass to the DSN.  I will check though.
0
 
cjswimmerCommented:
check out:
http://support.microsoft.com/support/kb/articles/Q235/4/22.ASP

I'm trying to see if I can use it to help you
0
 
PsychoDazeyCommented:
Heres another example:
Sub sOpenDBWithPwd()
Dim strDB As String
Dim strCmd As String
Dim objSecuredDB As Access.Application
  strDB = "J:\NewCode97.mdb"
  strCmd = SysCmd(acSysCmdAccessDir) & "\MSAccess.exe " _
      & strDB & " /wrkgrp " & DBEngine.SystemDB _
      & " /user Admin"  '/pwd ''"
  Call Shell(strCmd, vbNormalFocus)
  DoEvents: DoEvents: DoEvents
  Set objSecuredDB = GetObject(strDB)
  Stop
End Sub
'************* Code End *****************
Database Password
To open a database that's secured via the database password through Automation, open it in code  first, specifying the optional password in the OpenDatabase method's arguments.  A subsequent call to OpenCurrentDatabase for the same database will force Access to reuse Jet's setting for the open database.

Note that this technique will also work with TransferDatabase and CopyObject in allowing you to specify the database password.

'************* Code Start *****************
Function foo()
    Dim db As Database
    Dim oAcc As Access.Application

    Const TMP = "fooz.mdb"

    Set db = DBEngine.CreateDatabase(TMP, _
                        dbLangGeneral)
    db.NewPassword "", "doooo"
    db.Close
    Set db = Nothing

    Set oAcc = New Access.Application
    Set db = oAcc.DBEngine.OpenDatabase(TMP, _
                            False, False, ";PWD=doooo")
    oAcc.OpenCurrentDatabase TMP
    db.Close
    Set db = Nothing
End Function
'************* Code End *****************
http://www.mvps.org/access/modules/mdl0036.htm
0
 
InvisibleManAuthor Commented:
Who is going to win?  Will it be cjswimmer or PsychoDazey ?  Stay Tuned folks we have a hot race here :)
0
 
InvisibleManAuthor Commented:
I have a scheduled task that runs this line:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "G:\DB\ACCESSSQLREPORTS" /X AutoReport

This opens and runs the macro AutoReport which starts printing the reports in the table.  The only thing I have left is somehow bypassing the password of the DB.  If it can't be done I will ask a question and the correct answer first gets the points.  But first I will research to make sure there is not a way to do it easily.  Thanks
0
 
cjswimmerCommented:
ok I found out how to do it.  This involves creating a second Access database because it takes some coding.  In this second database, make a reference to DAO 3.6 Object library.  Then, create this function in a module:

Public Function PrintRemoteReports()

    Dim acc As Access.Application
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strDbName As String
    strDbName = "C:\YourProtectedDB.mdb"
    Set acc = New Access.Application
    acc.Visible = False
    Set db = acc.DBEngine.OpenDatabase(strDbName, False, False, ";PWD=ThePassword")
    acc.OpenCurrentDatabase strDbName
    Set rs = db.OpenRecordset("SELECT ReportName from tblReports")
    Do Until rs.EOF
        acc.DoCmd.OpenReport rs(0), acViewNormal
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    Set acc = Nothing
    Quit
End Function

Now create a macro called macPrintRemoteReports in the second database with a RunCode line calling this PrintRemoteReports function.  You can now put the pathway to this second db in the shortcut described earlier and open it with the "/x macPrintRemoteReports" option.
0
 
cjswimmerCommented:
InvisibleMan, the funny thing is that PsychoDazey and I work in the same building.  We are probably about 50 yards away from each other right now...
0
 
cjswimmerCommented:
yup, I smell him.
0
 
PsychoDazeyCommented:
Dont listen to CJS, he smells bad
0
 
cjswimmerCommented:
PsychoDazey feels that he has to lash out at those he feels are superior to him.  We're investigating whether medication is necessary.
0
 
InvisibleManAuthor Commented:
WE HAVE A WINNER.....THE CROWD GOES WILD.   CONGRATULATIONS TO THE WINNER AND NEW HEAVYWEIGHT PROGRAMMER OF THE WORLDDDDDDDDDDDDD.    
          **************CJSWIMMER***************

Thanks both of you for all your help.  Will have to have a rematch.  :)  Thanks guys and gals.

0
 
InvisibleManAuthor Commented:
Great job..
0
 
cjswimmerCommented:
thanks!  glad I could help.  I'd like to thank the Academy, my agent, the fans........
0
 
cjswimmerCommented:
thanks!  glad I could help.  I'd like to thank the Academy, my agent, the fans........
0
 
cjswimmerCommented:
only about 8000 to go til the Top 15....Dovholuk is hot on my heels though
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now