Link to home
Start Free TrialLog in
Avatar of InvisibleMan
InvisibleMan

asked on

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
Avatar of PsychoDazey
PsychoDazey

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.
Avatar of InvisibleMan

ASKER

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.
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.
Please give me the full line of code that goes before this.
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?
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.
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?
CJS-
I am tired of you stealing my ideas.  I am reporting you to EE!
I'll tell you're wife that you were hitting on me...
First one to get me the answer to the password will get the points.
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...
you might have to implement user level security instead...
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
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
I don't think so PsychoDazey because that is the UID/Pass to the DSN.  I will check though.
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
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
Who is going to win?  Will it be cjswimmer or PsychoDazey ?  Stay Tuned folks we have a hot race here :)
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
ASKER CERTIFIED SOLUTION
Avatar of cjswimmer
cjswimmer

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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...
yup, I smell him.
Dont listen to CJS, he smells bad
PsychoDazey feels that he has to lash out at those he feels are superior to him.  We're investigating whether medication is necessary.
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.

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