Run query from external program

  • Thread starter Thread starter Mitch Powell
  • Start date Start date
M

Mitch Powell

I need an Excel VBA routine to be able to attach to Access 2003 and run a
query. Is this possible?
 
You can either use automation (heavy) or try to add a reference to DAO (or
ADO) and use it to open a database. With automation, you can do, in theory,
every thing Access can do, but it may requires a lot of extra memory. Using
DAO (or ADO), you cannot use externally defined function (like a user
defined function, something you can do using under Access). In fact, you
will be using the database a little bit like you would from VB6.


Here a small example with ADO:

Private mCnn As ADODB.Connection
Dim rst As ADODB.Recordset
' mDB is a string identifying the mdb file to connect to
mCnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & mDB & _
";Persist Security Info=False"
mCnn .Open
If mCnn.State = adStateOpen Then
Set rst = mCnn.Execute("SELECT ... ", , adCmdText)
... ' do something with that recordset
... 'and close the connection, if done.
End If




(you will still need the reference included in your app.)


Vanderghast, Access MVP
 
I need an Excel VBA routine to be able to attach to Access 2003 and run a
query.  Is this possible?
Click to expand...

Can't you just connect to the Access table/query from Excel and return
the data that way? Or do you need this in Access? What exactly are
you trying to do (in business terms, not database terms)?
 
Business objective: There is a data load routine that currently requires
that processes in three separate applications be run in sequence. It is
currently a manual process in which a user has to go to each application and
run a process, query, etc.

Step 1 - The first application takes data from an accounting system and puts
in SQL Server.

Step 2 - An Access query pulls the SQL Server data into Access and
manipulates it to a point that can imported by a third (and final)
application (Cognos TM1)

Step 3 - User runs a process in TM1 to pull the Access data in to TM1

So what I want is a VBA routine of some sort that performs these three steps
in sequence to replace a manual process. This obviously requires that each
application can be accessed programatically, hence my questions here about
the Access piece (Step 2).

Hope that helps.
 
You may or may not be able to do it all from within Access, but it depends on
the capabilites of the other applications involved.

For example, in step 1, you would have to have the ability in the accounting
system to pass it a command to load the data to SQL. If the accounting
system has a command line option like command line options for Access and
many other Windows applications, you could use the Shell function in Access
to execute the routine.

Step 2 is simple. You link to the SQL tables and run the queries you need
to manipulate the data.

Step 3 is like step 1. It depends on how much control you have with TM1.
 
Back
Top