Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Access Queries not seen in Excel | Excel Discussion (Misc queries) | |||
Access Queries into Excel | Excel Worksheet Functions | |||
Queries from Excel 2003 to Access | Excel Discussion (Misc queries) | |||
Excel Queries using Access data | Excel Discussion (Misc queries) | |||
Using Excel Worksheets like Access Queries | Links and Linking in Excel |