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
InvisibleManAsked:
Who is Participating?
 
cjswimmerConnect With a Mentor Commented:
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
 
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
 
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:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.