Can Access accept an SQL request from another program?

[williamlove]

I have noticed diagrams showing Microsoft SQL Server accepting SQL statements sent by “SQL Requesters” over connections. I would like to know if Microsoft Access can be used in that fashion?

Put another way, can Access be configured to accept an SQL statement that another program (e.g. a VBA program in a VBA enabled 3rd party app) creates?

Currently, my VBA program instantiates an Access database object and then manipulates it (I just add a record to one of the tables), then closes and destroys it. This solution seems fragile (it stops working—I can explain more if needed). I would like to know if the technique inferred by my question would be more reliable.

 

[Sergeant]

By all means...

Here's an example from within an ASP page:
<%
Dim db_conn
db_conn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\Inetpub\wwwroot\Folder\AnotherFolder\MyAccessDB.mdb"

Dim Rst

Set Rst = Server.CreateObject("ADODB.Recordset")
Rst.ActiveConnection = db_conn
Rst.Source = "SELECT * FROM MyAccessTable order by MyField"
Rst.CursorType = 0
Rst.CursorLocation = 2
Rst.LockType = 1
Rst.Open()
'do what you want with it...
Rst.Close
db_conn.close
Set Rst = Nothing
%>

You could instead create an "ADODB.Connection" to execute statements like INSERT, UPDATE, DELETE.

 

[Mile-O]

You can do it from Excel, Word, etc. also - it's just a matter of adding the appropriate references and coding it.