Here you are. This is a simple function for returning an ADO recordset based
on an SQL Query...

private const m_cDBLocation as string = "C:\Mine.mdb"

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

On Error GoTo ErrorHandler
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
Exit Function

ErrorHandler:
modErrors.HandleError m_cModule, "RunQuery"
End Function

Here is how you use the function...
Dim rst As ADODB.Recordset

Set rst = RunQuery(strSelect, strFrom, strWhere, strOrderBy, False)

The function either returns a connected or disconnected recordset, depending
on whether you want to update the recordset back to the database.

To define the SQL statement write a query in access and then change the view
to SQL and Tada... There it is.

Fore something else cool try creating a pivot table and attaching it to the
Access database. This also gets you around the pesky 65,535 limit of access
and is a cool wat to summarize data, and even bring it straight into a chart.

HTH

"JonR" wrote:

I'd like to see the code, if you would please.

At present there is no good reason to do it with VBA other than my own
education, but I'm starting to get into data sets that are reaching the
limits of Excel and may need to come up with alternatives to loading a sheet
full of data.

Thanks.

"Jim Thomlinson" wrote:

Sure you can use VBA to do that, but why not just use Get External Data and
embed a query in your spread sheet. It is a lot easier than going the VBA
route (unless you have good reason to do so). If you need to to go the VBA
route reply back and I can give you some code. I am assuming that you know A
little bit about Access...

"JonR" wrote:

Hello,

I've developed an Access database that tracks service requests and when they
are opened, closed, or if and when they are canceled. I would like to
summarize this in an Excel chart such as number of requests opened and closed
by month, but would rather not import the entire data table to perform this
calculation.

Is it possible to query the data table as it resides in Access so all I have
to pull over is a few summary numbers?

TIA
Jon