Re: How to do an Access Database Query in Excel

From: John (johndoconnor_at_yahoo.co.uk)
Date: 07/01/04 

Date: Thu, 1 Jul 2004 12:17:23 +0100

Jamie

This is the Macro I've recorded to do exactly what I manually do to extract
the information. Before it exports to Excel I wish it to SUM by Location
each Item sales, so not sure where I would put that in the code below. I
want to sum in order to extract more than 1 months data. Currently 1 month
is approx 59,000 line!

Thanks

With Active***.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DBQ=E:\History.mdb;DefaultDir=E:\;Driver={Microsoft Access
Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxSca" _
        ), Array( _

"nRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;")),
_
        Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT tblSalesMixHistory.StoreNo, tblSalesMixHistory.SalesDate,
tblSalesMixHistory.Item, tblSalesMixHistory.MenuLookupName,
tblSalesMixHistory.Sold" & Chr(13) & "" & Chr(10) & "FROM
`E:\History`.tblSalesMixHistory tblSalesMixHi" _
        , _
        "story" & Chr(13) & "" & Chr(10) & "WHERE
(tblSalesMixHistory.SalesDate>={ts '2004-05-31 00:00:00'} And
tblSalesMixHistory.SalesDate<={ts '2004-06-27 00:00:00'})" & Chr(13) & "" &
Chr(10) & "ORDER BY tblSalesMixHistory.StoreNo" _
        )
        .Name = "Query from HOBO - History Sales Mix"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

"Jamie Collins" <jamiecollins@xsmail.com> wrote in message
news:2ed66b75.0407010201.37f13e27@posting.google.com...
> John,
> If your existing query looks like this:
>
> SELECT
> location, product,
> sale_date, sale_quantity
> FROM
> Sales
> ;
>
> then you want something more like this:
>
> SELECT
> location, product,
> SUM(sale_quantity) AS sale_quantity
> FROM
> Sales
> WHERE
> sale_date BETWEEN
> #01 MAY 2004# AND #30 MAY 2004#
> GROUP BY
> location, product
> ;
>
> For fuller details, post your raw data, what you want your processed
> data to look like and your existing SQL text.
>
> Jamie.
>
> --

Loading