Replies

Ted, re. “I think you have to back up each PC on it’s own.” – I agree, that’s probably the case. That’s one improvement I’d like to see in SP1 You could provide the homegroup password to be stored with the job.
mercyh, re. “…does it open and work?” – yes, I can browse to the other library. I can also right-click on the other library to add it when creating a zip file. Just not when creating a backup job. Maybe’ll I open a ticket with Winzip.

I wonder why Network Magic can do this, but Win7 cannot…

Thanks to both of you for replying!
Klaus

I found a solution on microsoft.public.sqlserver.odbc: create a simple query in design view w/o specifying any tables. Right-click in the blank, grey “tables” area to display the query properties. Add the DSN connection string including the password to the Source Connect String, e.g.
ODBC;DSN=MyDSN;Description=MyDSN;UID=MyUser;PWD=MyPWD;DATABASE=MyDB;AutoTranslate=No;TABLE=tblAnyTable

Now just drag a field from tblAnyTable with a criteria that returns no records (e.g., User ID of -1). The SQL View would look something like this:
SELECT tblAnyTable.EmpNo AS Expr1
FROM MyDB IN ” [ODBC;DSN=MyDSN;Description=MyDSN;UID=MyUser;PWD=MyPWD;DATABASE=MyDB;AutoTranslate=No;TABLE=tblAnyTable]
WHERE (((tblAnyTable.EmpNo)=-1));
Make sure the query returns no records. All youwant is to get connected to the database w/o a password prompt. I even hid the query, so the users won’t see it. Save it as qryDSN or whatever.

For the code, I use
Dim db As Database
Dim rs As Recordset
Dim qdfParmQry As QueryDef

Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs(“qryDSN”)
Set rs = qdfParmQry.OpenRecordset()
‘no records are returned to the recordset
DoCmd.OutputTo acOutputReport, “rptMyReport”, acFormatSNP, “MyReport.snp”, False

after which I close the aplication.

Now I can launch the database at night to run unattended reports w/o a password prompt. This is in essence the same thing I did initially, but this one works on the user’s workstations as well.

Francois,

since the application was already installed and running on other PC’s (w/o the report automation), I first had to recreate the user dsn on my machine before writing the code. I used the same login verification as on the other PC’s. I compared each screen to be 100% sure. The password is not stored.

When the users run the reports interactively (as before), they are prompted for the SQL password (as before). So that part works unchanged, as it should.

But when they attempt to run the reports automatically, that’s when they have the problem: they are still prompted for the password, whereas on my machine, running the code eliminates the login prompt.

Thanks for your help, I’m still w/o a clue .

Thanks all for helping.

I think I will go a route I initially rejected, which is a form with two date fields. The dates will still be populated automatically, but can be changed for manual operation.

I will modify the query to get the dates from the form. This should work in both manual operation, as well as in automated mode when I launch the app in the wee hours .

Let’s hope the customer goes for it .

Klaus

Charlotte,
thanks for replying.

When I run the report programmatically, I intend to output the report to a snapshot file using the following code (unless there is a better way):

DoCmd.OutputTo acOutputReport, “rptMyReport”, acFormatSNP, “MyReport.snp”, False

The report’s RecordSource is query “qryMyQuery“, and the query has the “Between [Enter Begin date:] And [Enter End date:]” parameter on a date field.

Could you please give me an example on how to rewrite the above DoCmd, so it feeds the two dates to the “between” parameters, assuming I use my datBDate() function and the Date() function for the two dates?

I really appreciate your help in this.

Klaus

Hey zeddy,

that Matrix-bit was pretty good.
U never know when that comes in handy, I’ll keep that one around for future reference…

Thanks,
Klaus

Hah! I love it – very sweet indeed

Thank you very much,

Klaus

Oh, I get it now .

Re.: “but I do hate that there are two sets of menus & toolbars when you ‘arrange documents’ ” – that’s exactly the annoying behaviour that Romke Soldaat’s clever code fixes (among other things).

Klaus

Phil,

if I understand you correctly, that’s actually what Klaus’ code does. The Chr(9) tabs a table cell entry when I press Ctrl-Tab (after I assigned the shortcut), and “tabs” to the next document if my cursor is outside a table. Beautiful

I can’t believe I didn’t do this sooner. It always annoyed me that Word violated what I considered to be a MS/Windows standard.

You might want to take a look at the template I describe above that restores Word’s MDI, another pet peeve of mine.

Klaus

Hi Klaus ,

your code worked like a charm. I put it in normal.dot, with toolbar icon, assigned shortcut and all.
The universe is balanced once again .

Thanks,

Klaus

Thanks for the help.

Although strCommandLine executes without a problem, EnvVar doesn’t return anything. All I get is a blank MsgBox when I try with WZZIP and Notepad.

I was thinking there might be a solution in WSH somewhere, I just didn’t know how to get started.

Klaus

Wassim,

thanks for replying.

Correct, Shell returns the shelled program’s Task ID, but this basically only tells you if the shelled program was =launched= correctly. If not, it returns 0.

What I need is to capture the return code generated by the shelled program itself =after= it was launched.

Step 1: “Shell” launches program correctly, returns a non-zero task id and is done.

Stpe 2: Launched program attempts to perform the requested operation (i.e., add files to a zip archive), and returns whether it was successful, or if there was trouble (e.g., the file to be zipped does not exist, disk full etc). That’s the value I need to capture.

Re.: “WinZip is not programmer friendly ” – you may want to try their new WinZip Command Line download. It only has two commands, WZZIP and WZUNZIP, with all the commandline parameters you need. No UI to get in the way.

Klaus

Thanks, Sam.
Worked like a charm
Klaus

I have tried several versions of pgp in WInXP, none of which seem to work right. Some won’t install (7.0.3), some say they haven’t been tested under XP but install anyway (7.1), some install without problems (6.5.8).

The biggest problem I see is with pgp disk: when mounting a disk, if it works at all, it will be mounted to a drive without a drive letter (it’s just a space in the drive list).

Once mounted, everything seems to work OK, but I haven’t done any testing yet beyond what I described. I haven’t decide which version to use yet.

Weird, that 7.1 works well under Win2K, but not XP.

Jack,
thanks for replying. Your solution was my first thought, as well. But unlike a msgbox, which would pause execution until the user responds (synchronous), the code would continue past the docmd.openform command asynchronously.

I did come up with a workaround which I won’t describe here, since it is specific to my particular project. It would take too long to explain the circumstances, and the chances anyone else would benefit are slim.

Nonetheless, if there is a way to communicate with a MsgBox, I would still like to know.

Thanks again,

Klaus