Excel Driven Access Queries
 

I'm trying to use VBA to

1. Update a query in Access based on Excel based input
(i.e. an updated array(X) contains new data points to
filter against). In other words, how do I reference an
Access database and 'talk' to it? Does it need to be
open?
2. Once the query has been run, import the updated query
data into a worksheet in Excel.

Any starter advice/steps/code would be greatly
appreciated.

Thanks.

Excel Driven Access Queries
 

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table and add data to it,
* select data from a table,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

"Ed" wrote in message
...
I'm trying to use VBA to

1. Update a query in Access based on Excel based input
(i.e. an updated array(X) contains new data points to
filter against). In other words, how do I reference an
Access database and 'talk' to it? Does it need to be
open?
2. Once the query has been run, import the updated query
data into a worksheet in Excel.

Any starter advice/steps/code would be greatly
appreciated.

Thanks.

Excel Driven Access Queries
 

You're the man! Thanks!

-----Original Message-----
Ed

The following code should get you started. It is
slightly different from your request in that it creates a
temporary query rather than updates an existing MS Access
query. Generation of the sql from the variables in your
spreadsheet should be reasonably easy. The output only
has one heading as the table has one field. This will
have to be modified.

When you build the code, you will have to reference the
DAO 2.5/3.5 compatibility library. To do this in the
VBE, go Tools, References, scroll down to the library and
check.

Tony

Sub ccc()
Dim wrkjet As workspace
Dim dbs As database
Dim qdef As QueryDef
Dim rst As Object


Dim wks As Worksheet, cl As Integer, rw As Integer
Dim mydatabase As String
Dim myquery As String
mydatabase = "full\path\and\databasename.mdb"

Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set dbs = wrkjet.OpenDatabase(mydatabase)

myquery = "select * from tablename where field like 'b*'"

Set qdef = dbs.CreateQueryDef("", myquery)

With qdef
Set rst = .OpenRecordset()
End With


Set ws = Worksheets("sheet2")
With ws
.Range("a1").Value = rst.Fields(0).Name
.Range("a2").CopyFromRecordset rst
End With


rst.Close
dbs.Close
wrkjet.Close

Set ws = Nothing
Set wrkjet = Nothing
Set rst = Nothing
Set dbs = Nothing
Set qdef = Nothing

End Sub

.