MS Access macro on terminal server 1

[knbink]

Hi all

This might be a silly question.

I am trying to run ms access macro on a terminal server windows 2003 and i want to make it automatic by using the scheduler.

I tried, but it doesnt work for some reason.
The scheduler doesnt open access to start the macro.

I can see the access started in the task manager, but the macro is not running for some reason.

Any help?

 

[kjv1611]

Okay.... so to see if I've got this right...

You have task scheduler opening the database, and that part works.
You have a macro that you want to run automatically, but it is not starting.

If that is correct, then there are a couple of ways to accomplish this:
[ol][li]Use the AutoExec Macro in Access.[/li]
[li]Create a user form that opens your macro from it's Form_Load() event, and then set that form as the default form to open when the database is opened from the Access Options.[/li][/ol]

 

[knbink]

ok the problem i am having is following.

When i run the batch file automatically, it works without any problems.

I have the same batch file at the moment on a local machine, and running with a scheduler, but someone needs to be logged in all the time so it can run.
Thats what i am doing right now.

So i want it to be moved to the terminal server, to be run automatically without anyone to be logged in.

I setup a scheduler, but whenever i try to run the scheduler, i can see in task manager, that access is started but its not doing anything.

when i run the batch file manually, it is working everything.

 

[kjv1611]

OK...

HOW is the macro running? Something has to kick it off. If it's running on your PC, but not on the terminal server, then it's not being kicked off.

Also, are you absolutely SURE that the database is being opened, and not that the event is running.. but just not kicking off the database?

The reason I ask this is that what used to work for me (on Windows XP) with task scheduler didn't work the same on Windows 7... unless I just forgot.

Anyway, on XP, I was able to tell task scheduler to open a specific Access datbase. That didn't seem to work on Win7, so I had to set up a Windows batch file which then kicked off the database.. Then in the database, I have a form set to open at startup, and in that form, I have Form_Open event populated, and from there, everything is controlled via the VBA code.

Any hints there?

 

[knbink]

Ok i found out 1 problem what it was.
It was not running because i had it setup to be run as NT AUTHORITY\SYSTEM which it should, but nevermind.

When i changed it to a specific user, it started from the scheduler and i can see that it opens the database, but it doesnt run the macro.

When i try to run the batch file manually, it works as well.

The only thing i can see is that it creates 1 Export configuration file, and 1 of the 3 files that needs to be created, but the file is empty.

 

[knbink]

this is happening with the Administrator user.

I will try it also with a normal user that has administrative rights and see what will come up.

Will keep you posted.

 

[knbink]

ok i just realized that the macro is just not running.
commands in the batch file are executed untill it reaches the macro. Access is started, but the macro is not started and it stuck there, because it can not finish the exporting of the tables from access to txt file.

 

[knbink]

but when the user is logged in, it works fine.

This problem is only when the user is not logged in.

 

[kjv1611]

How is it attempting to export the files? Perhaps it's referencing a user-referenced location/variable?

 

[knbink]

it exports the files using a macro, and the location is c:\export.txt

I can see that the database is opened, because i am looking in the folder where the database file is, and i can see that it creates another file forgot the extension, but this file is created only when the database file is opened.

 

[randy700]

knbink,

kjv has suggested, more than once, that your problem may be from the way your macro is supposed to start.
You have yet to show the macro itself, or any code.
Help is available, but you need to provide the answers.


Randy

 

[knbink]

I have a form that at load has run macro1.
The database starts the form once it is opened.

here is the macro:
Action1 Transfer Text
Action Type: Export Delimited
Table: company1( which is odbc connection to a FoxPro Database) Filename: c:\export1.txt
Action 2: Transfer Text
Action 2 Type: Export Delimited
Table: Company2 (which is odbc connection to a FoxPro Database)
Filename: c:\export2.txt
Action3: Transfer Text,
Action Type: Export Delimited
Table: Company3 (which is odbc connection to a FoxPro Database)
Filename: c:\export3.txt
Action 4: Beep
Action 5: Quit

What more information you need?

 

[knbink]

anyone?

 

[Hap007]

Several thoughts.

1) I would have written the code in VBA rather than a macro since it would be easier to trouble shoot, but that is actually a preference.
2) How are your ODBC links defined. If they are 'User' and no user is logged in, then the ODBC links would not exist. Make sure they are System DNS
3) Make sure that prompts are disabled. If the macro is running silently and you attempt to over write the file Export1.txt, then the process may be waiting for a user response to continue.

Hope This Helps
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Accounting Solutions - with free source code
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[knbink]

thanks i will check this and get back.