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
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.
Here is what I have so far:
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE
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.
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
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.
ASKER
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.
ASKER
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 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...
ASKER
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...
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=<Passwor d>;DATABAS E=" & _
"<database name>;")
Set ds = db.OpenRecordset("<any table existing in the
database defined by DATABASE above>")
End Function
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=<Passwor
"<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
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
ASKER
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
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(TM P, _
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
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(TM
dbLangGeneral)
db.NewPassword "", "doooo"
db.Close
Set db = Nothing
Set oAcc = New Access.Application
Set db = oAcc.DBEngine.OpenDatabase
False, False, ";PWD=doooo")
oAcc.OpenCurrentDatabase TMP
db.Close
Set db = Nothing
End Function
'************* Code End *****************
http://www.mvps.org/access/modules/mdl0036.htm
ASKER
Who is going to win? Will it be cjswimmer or PsychoDazey ? Stay Tuned folks we have a hot race here :)
ASKER
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
"C:\Program Files\Microsoft Office\Office\MSACCESS.EXE
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
**************CJSWIMMER***
Thanks both of you for all your help. Will have to have a rematch. :) Thanks guys and gals.
ASKER
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
rst.Movenext.....
and it will open and run the report.