Query from Access to Excel OR getting Excel to query Access DB?

I wrote a lot of queries in Access, however, when I imported the queries into Excel, I only found maybe 10-20% of my queries.

Here is a copy of my Access DB:

http://www.2mbit.com/~teslerd/acwwalog.zip

David

David,

Use MS Query (C:\Program Files\Microsoft Office\Office10\MSQRY32.EXE) to create your query.. it is very similar to Access, so you shouldn't have a problem creating it..

Save it as YourQuery.dqy

When you import to Excel.. look in "My Data Sources" and select YourQuery.dqy

I experienced the same missing queries when importing directly to Excel.. but all were available with MSQuery..

Bud

I'm sorry.. I forgot to explain the creation steps..

You will select New from the file menu and navigate to your DB.. then select the table or query.. save as YourQuery.dqy..

Bud

Weird.

When I put this in MSQuery this sample from my Access SQL Query:

SELECT IIf(AvgInfluentBOD!Expr1<>0,AvgInfluentBOD!Expr1*8.34*AvgInfluent!Expr1000,"") AS 30dayBOD FROM AvgInfluent, AvgInfluentBOD;

it responded with the following errors:

SQL Query can't be represented graphically. Continue Anyway? (I said OK)

Could not add table 'AvgInfluentBOD'

What do I need to do?

Increasing points due to urgency of the problem.

It sounds like you have multiple OUTER JOINS in your query..

Microsoft has published details on what causes the error as well as some workaround solutions..

http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fbin%2Fkbsearch.asp%3FArticle%3D115340

Bud

This is not an outer join.

According to Access Query, my AvgInfluentBOD is this:

SELECT Avg([300I (Influent)]![BOD Lab mg/l]) AS Expr1, Format(Date,"yyyy-mm") AS Expr2
FROM [300I (Influent)]
WHERE ((([300I (Influent)].Date) Between [Start Date] And [End Date]))
GROUP BY Format(Date,"yyyy-mm");

And AvgInfluent looks like this:

SELECT Avg([Flow Data Calculations]![Influent Calc]), Format(Date,"yyyy-mm")
FROM [Flow Data Calculations]
WHERE Date Between [Start Date] And [End Date]
GROUP BY Format(Date,"yyyy-mm");

David,

I apologize.. raw SQL is above my level of expertise..

If you don't recieve further suggestions here.. you might want to post a pointer to this question in the Access forum..

Best of luck,

Bud

hawkeyex,

I've been looking at your DB. The following worked fine for me (Office 2003):

In Excel, use menu Data/Import External Data/New Database Query...; select the Access DB and choose your query. I've chosen one by random and it worked fine. It appears as I've seen all your queries in the menu, I haven't checked thoroughly though.

Do not use MS Query for complex queries. It's too simplistic for this. Build the queries in MS Access and and use them directly (i.e., use MS Query to get the results directly as they are in the MS Access created query).

Hab

You can pass SQL statements directly to Access (or any other ODBC datasource) using ADO.

In Excel, open the VB Editor (Alt+F11), add a reference to Microsoft ActiveX Data Objects 2.x

...then use code like this to run your statements.

Sub Test()

    Dim con As ADODB.Connection
    Dim rs As ADODB.Recordset
   
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Test2.mdb;User Id=admin;Password="
       
    strSQL = "SELECT * FROM MyTable"
   
    Set rs = con.Execute(strSQL)

    'your recordset now contains the results of the SQL query...
    'you can use that however you want such as to populate as range of cells.
   
    con.Close
    Set con = Nothing
    Set rs = Nothing
   
End Sub



WATYF

Hi WATYF, long time no see....

There is a good example of WATYFS suggestion at http://www.erlandsendata.no/english/index.php?d=envbadacimportado. If you go down the ADO route then please don't award me any points, this post is just a follow up to WATYF's.

As per the previous post it requires that you send a reference to Microsoft ActiveX Data Objects 2.x

Cheers

Dave