Can I Run Access on Server from VBA?

[margaritavillain]

I am working on pulling report data using Access and Excel. I want to create
the SQL strings in Excel/VBA and send them to Access - which opens on the
server and executes the queries from the server side. I can then use Excel
to pull a recordset.

I understand all the VBA in both Excel and Access to do this except getting
the Access application to run on the server and not the local machine. The
issue is the connection to the server is so slow it's not practical to do
this any other way. Can someone point me to an explaination of how to do
this?

 

[Alex Dybenko]

Hi,
you can't run access on server like sql server. What you can do - is to save
query SQL as text on server, then run access on a server (from server UI!),
let it detect and read new sql, run it, and save result in a file. something
like this. but not sure you gain much on this approach.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[david]

Use the free SQL Server version instead of Access.

Access, DAO, and, as far as I know, ADO don't support
DCOM.

DCOM is Distributed COM, and is the thing that allows
VBScript and VB to open COM objects on a network
server.

COM is the method for automating Access, DAO, and ADO.

I mention DAO and ADO because you only need the
Access object to do reports, or to run code, including
User Defined Functions. To get data, automate a DAO
or ADO object - smaller, quicker, more stable, easier
to use, and has all the data handling ability of the larger
Access object.

What people use to do was create a VB6 object that you
could use DCOM and Windsock with, and use that object
to encapsulate the DAO object and network communication.
Now you could do the same using the COM+ transaction
server instead of building your own VB6 object.

But that was in the old days, before there was a free version
of SQL Server, and when the expensive version of SQL
server missed many of the features of Access/Jet. Now
there is a free version, and it has gradually inherited all
of the database features of Access/Jet, and you will find
examples of doing what you want, using either ODBC
or DCOM with MTS/COM+ and ADO disconnected
recordsets.

(david)