Automating Macros

[kmacdonald]

I have a series of Queries that I run each morning. I am trying to automate this process as much as possible...

I have set up some simple macro's in Access to run the queries I need and output them in Pivot table view.
I created a Macro that calls each of my macros, so that I could schedule this (as one step) through Windows Scheduled Tasks:

Run: "C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Metrics\VantiveMetrics.mdb"/xDaily Macro Series

Start in: "C:\Program Files\Microsoft Office\OFFICE11"


Ideally, this process needs to run early in the AM each day.
The problem I am having is that the scheduled task opens Access and this them prompts me for my login/password for the OBDC connection I have to the data. So... I come in in the morning and the login screen is waiting for me, rather than the output of my queries.

Is there a way to feed my login/password? Or some other way to approach this?

Thanks
Katie

 

[MrTibbs]

change your security and workgroup

The username / password is because it is trying to log in to your workgroup.

Consider allowing all users to log in and run the queries then finding a default security file (system.mdw)
Specify the default security file on the command line and it should not prompt for the password (true in Access '97)

Or you may be able to include the username and password on the command line.

Good luck

Tony

 

[koenswolfs]

Similar problem

Hello,

I have a similar problem to Katie's. Some of the queries which are called in the macro are based on linked Sybase tables and some of them are based on linked Oracle tables. For both systems (Sybase and Oracle) I have a username and password. Each time I want to execute the macro I have to enter both usernames and passwords. Is there a way to bypass this, so the usernames and passwords are filled in automatically?

Thanks in advance,

Koen.

 

[MrTibbs]

Different problem: ODBC passwords vs. Access passwords

Koen,

To avoid the passwords for the connected Oracle and Sybase tables you need to configure the connection to include the username and password: This may be done in ODBC configuration or on a table by table basis.
Security concerns apply: The connection string has the password in plain text.

To see what your connection settings are currently:
SELECT MSysObjects.ForeignName, MSysObjects.Database, MSysObjects.Connect, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.ForeignName) Is Not Null))
ORDER BY MSysObjects.ForeignName;

Then add UID and PWD settings: Position and syntax depends on the connection requirements.

For selected Oracle connections I changed the connection string using in a query (access 97, oracle 8). The before and after connection strings are below.

Before:
="DSN=ORACLEODBC;DBQ=MYORACLEDB;DBA=R;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;"));

After:
= "DSN=ORACLEODBC;UID=JUNKUID;PWD=NOTSET;DBQ=MYORACLEDB;DBA=R;APA=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;FRL=F;MTS=F;CSR=F;PFC=10;TLO=0;"

Oracle / Sybase connection passwords apply per connected table.
Access workgroup passwords apply per workgroup.

 

[koenswolfs]

Can't edit the MSysObjects table

Thank you MrTibbs for your reply. I tried changing the connection string in the query, but I'm not allowed to. I get: "Control can't be edited; it's bound to replication system column 'Connect'." Is there any workaround?

Also, do you know how I change the connection string for the Sybase tables? The query you suggested gives: "DSN=syb;SRVR=SYB01;DB=dwh;" for the Sybase tables.

Thanks again!

Koen.

 

[MrTibbs]

Sybase, Replication and editing msysobjects tables

Koen,

I have never used Sybase or Access replication. The last time I edited a msysobjects table I did it by ploughing through an ODBC connection setup, supplying passwords when offered to get the right connection string and then just did an update query replacing the old string wih the new, password enabled, string. It worked for me because the Oracle and Access were both quite old (8i and 97 respectively) so allowed system table tinkering but I cannot recommend the method: I was surprised when it worked! [I also made sure I had several current backups before trying it].

Someone out there must surely know a better method.

Sorry I cannot be more help.

Good luck, Tony

 

[FrankvdWindt]

Koen,

Did you find a way to edit the MSysObjects table? I'm having te same problem and can't find a solution.

 

[dfenton]

The error on not being able to edit the CONNECT field is in error in saying that it is a replication field -- it is not. But the connect strings are only editable via DAO/ADO or the Linked Table Manager.

 

[halem2]

Why not create a simple Sendkeys script and run it when the login window opens up so it would log you in automatically?

The example below is what I use to purge my Outlook 2003 deleted & recover items. You can modify it as it suits you. Goggle SENDKEYS to get full list.

set WshShell = CreateObject("WScript.Shell")
While WshShell.AppActivate("Deleted Items - Microsoft Outlook") = FALSE
wscript.sleep 1000
Wend
'
WshShell.SendKeys "%T"
wscript.sleep 100
WshShell.Sendkeys "{TAB 7}"
wscript.sleep 400
WshShell.SendKeys "{ENTER}"
wscript.sleep 600
WshShell.SendKeys "%T"
wscript.sleep 100
WshShell.Sendkeys "{TAB 8}"
wscript.sleep 400
WshShell.SendKeys "{ENTER}"
wscript.sleep 400
WshShell.SendKeys "{END}"
wscript.sleep 400
WshShell.SendKeys "+{PGUP}"
wscript.sleep 2000

WshShell.SendKeys "{ENTER}"