How can I launch an MSAccess macro through xp_cmdshell?

Question

How can I launch an MSAccess macro through xp_cmdshell?

UNCRushFan

SSC Veteran

Points: 281
More actions
June 12, 2009 at 1:18 pm

#201391

How can I launch an MSAccess macro through xp_cmdshell?
I've tried to do this through xp_cmdshell - but it only seems to start the MSACCESS.exe process, not open the actual *.mdb. And - it appears to "hang" and not really do anything. The xp_cmdshell query will run until I kill the process through task manager. Here's my syntax:
exec xp_cmdshell "(cd /d c:\Program Files\Microsoft Office\Office12&&msaccess.exe&&cd /d c:\&&AccessDB.mdb)"
I've even tried to compose it with dynamic SQL, breaking the code into @SQL1, @SQL2 and using @SQL3 to compose the first two. I'm a relatively new DBA, so any help is GREATLY appreciated.
Thanks -
Tim
Membrane.Inside("Insane");

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

I'm guessing its the security warning dialog box (which is not visible). If you run your command from a command prompt, when MSAccess opens does it display any security warnings? - If so that will be why it appears to hang. You may have to alter security settings, or check the MSAccess help for command line settings (I know you can cause it to execute a macro from the command line, you may be able to surpress the security warnings)

Or - if you're running xp_cmdshell from a client, then MSAccess may be opening on the server?

UNCRushFan SSC Veteran Points: 281 More actions · Answer 2

Thanks for the post Tom. The security warning may very well be the issue. I'll give it a try.

T

Membrane.Inside("Insane");

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Unless things have changed with Access, when you attempt to open it and run a macro, Access wants to interact with the desktop, something it can't do when you try to run it using xp_cmdshell.

What are you attempting to do within Access?

UNCRushFan SSC Veteran Points: 281 More actions · Answer 4

Hi Lynn - thanks for the post.

One of our clients has a data scrubber macro that runs before an automated import job (into SQL). The scrubber prepares the data files before the SQL job runs that triggers the import. I've suggested (and even offered to program it myself) that we handle the scrubbing in SQL, but the client prefers not to so the users can make any changes to the macro in Access (as they are not SQL programmers). They want to put a step in the SQL job that triggers the macro, then starts the import process into the SQL database. There are other ways of scheduling this process to happen in serial - I realize this - but this is the client's preference.

So, what do you think?

Membrane.Inside("Insane");

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

In our case, anything that needed to run inside the Access database was put into the hands of the users. Anything we had to do in an automated way was handled within our DTS packages (this was at a previous employer and we were using SQL Server 2000).