xp_cmdshell to launch MS Access database
-
Hello all,
I am trying to run an Access database from SQL Server using xp_cmdshell. The Access database is on my local machine. Here is the syntax.
exec master..xp_cmdshell '\\MyMachine\MyShare\MyFolder\MyAccessDB.mdb'
Running Start \\MyMachine\MyShare\MyFolder\MyAccessDB.mdb in the command window launches the Access app so I know the path is correct. I can execute xp_cmdshell with other parameters such as xp_cmdshell 'dir *.exe', so I know I have the privilidges to do so.
What happens is when I execute the statement the command runs and hangs, and the Access app is never launched.
The Access app does not require any user interaction to start so I don't know why it would hang.
Any suggestions would be great.
Thanks
-
I've never tried that but I think it's a bad idea in general. If what you really need is access to the tables then look into using OPENDATASOURCE and OPENROWSET. Both of these will allow you to select data from within an Access MDB. The problem with opening an MDB (or any UI program for that matter) from a command line call is that since the SQL Server could be on a different machine with no one logged in you could cause the SQL Server machine to crash. Not something you really want to do!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Have you checked Task Manager after running the command? You'll probably find MSACCESS.EXE in the list of processes. The most likely reason it's not a visible application is because the SQL Service doesn't interact with the desktop.
Why do you need to launch Access from xp_cmdshell?
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
I am attempting to do this as a final step in an import and scrub process in SQL Server. The Access database, I am thinking, will be used to log some load events in SQL Server. I can only create temp objects on our server. The Access db uses linked tables to access those temp tables in SQL Server. After the SQL process runs, the Access db should launch and run a macro that inserts the data in the SQL log tables to tables in Access before the SQL Server tables are deleted. I don’t quite understand how using xp_cmdshell in this way can take down a server, but if it can, that wouldn’t be too cool. Perhaps I need to think of another solution if this can happen. Thanks for your comments.
-
Can you create a linked server that connects to your Access db? If so, you can insert/update your access tables via the linked server.
If not, then try using OPENDATASOURCE and/or OPENROWSET as mentioned by Gary.
Basically, you want to push data into Access, not launch Access to pull data out of SQL Server.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface -
well, I also had this problem in past. But it really dint work. What I had to do is to write a small vb prog which could run and do the job.
Cheers!
-
If you really want to lunch Access from T-SQL using the xp_cmdshell. The you have to lunch the Access Runtime app, pass the access full name and the macro name as a parameter to runs when the MDB opens, which should be like this
master..xp_cmdshell '\\MyMachine\MSACCESS.EXE \\MyMachine\MyShare\MyFolder\MyAccessDB.mdb /x:MacroName'
This should work. Try it.
-
I'll add my vote for it being a bad idea. Whether you need to do it depends on whether you just need access to the data or if you're using some Access specific functionality you're using via a macro.
Andy
-
If all you need is a simple PDF, we had a recent article that showed how to build PDF directly from a stored proc. Not fancy, but sometimes good enough!
Andy
-
hi!
voting for bad idea as well! don't know but, some guys around seem to use sql server for anything else but its initial purpose (to be a *database* server).
skip starting excel/access/other applications, stop executing asp pages from within, stop calling stored procedures that invoke com objects!
there are different solution to such tasks, most of them easier, more secure, less risky (system task scheduler, msmq, dts, etc.)
best regards,
chris.
-
Why not use DTS?
Transform Data Task to export to Access, then Execute Process Task to start up Access MDB once the data is exported.
-
Looks like the easiest solution is to add a linked server. This should have been obvious from the start but…I played with the opendatsource and openrowset as well. These solutions worked fine. Tried yaweah’s solution but could not get it to work. Running DTS with the DTSRun utility and xp_cmdshell would work too, as BillNye101 mentioned. Thanks for all the advice guys. I’m going with linked server.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply