Solved

AUTO REPORT ACCESS/START COMMAND

Posted on 2001-09-07
29
301 Views
Last Modified: 2012-05-05
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
Comment
Question by:InvisibleMan
  • 12
  • 9
  • 8
29 Comments
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6464937
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
 

Author Comment

by:InvisibleMan
ID: 6464959
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
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6464983
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6464984
Please give me the full line of code that goes before this.
0
 

Author Comment

by:InvisibleMan
ID: 6465002
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6465021
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
 

Author Comment

by:InvisibleMan
ID: 6465040
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6465065
CJS-
I am tired of you stealing my ideas.  I am reporting you to EE!
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465073
I'll tell you're wife that you were hitting on me...
0
 

Author Comment

by:InvisibleMan
ID: 6465410
First one to get me the answer to the password will get the points.
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465447
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
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465451
you might have to implement user level security instead...
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6465466
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6465477
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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:InvisibleMan
ID: 6465481
I don't think so PsychoDazey because that is the UID/Pass to the DSN.  I will check though.
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465488
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
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6465491
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
 

Author Comment

by:InvisibleMan
ID: 6465505
Who is going to win?  Will it be cjswimmer or PsychoDazey ?  Stay Tuned folks we have a hot race here :)
0
 

Author Comment

by:InvisibleMan
ID: 6465512
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
 
LVL 6

Accepted Solution

by:
cjswimmer earned 35 total points
ID: 6465520
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
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465530
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
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465533
yup, I smell him.
0
 
LVL 6

Expert Comment

by:PsychoDazey
ID: 6465545
Dont listen to CJS, he smells bad
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465563
PsychoDazey feels that he has to lash out at those he feels are superior to him.  We're investigating whether medication is necessary.
0
 

Author Comment

by:InvisibleMan
ID: 6465573
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
 

Author Comment

by:InvisibleMan
ID: 6465576
Great job..
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465583
thanks!  glad I could help.  I'd like to thank the Academy, my agent, the fans........
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465587
thanks!  glad I could help.  I'd like to thank the Academy, my agent, the fans........
0
 
LVL 6

Expert Comment

by:cjswimmer
ID: 6465593
only about 8000 to go til the Top 15....Dovholuk is hot on my heels though
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now