Can you use VBA to query an Access database without importing data

[Guest]

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

 

[Guest]

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...

 

[Guest]

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.

 

[Guest]

Jon,

The best way to accomplish that task is through ADO. You will also need to
learn a little SQL to query the database. The nice thing about ADO is that it
can be used to connect to any database that is an OLE DB provider, including
Oracle and SQL server. Look at Microsoft Knowledge Base Article 246335 for an
introduction.

 

[Guest]

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

 

[Guest]

Sorry you need to strip out the error handler and the 65,535 limit is part of
Excel not Access. Too much egg nog...

 

[Guest]

Hi Jon

If ADO is your choice I can help with some sample code
If you still need some.

What type of recordset are you importing that would be lager than
Excel can import. I see post all the time that refer to Excel’s 65k limit.
This is a row per column limit. Excel’s CopyFromRecordset can
handle rs’s larger than 65k although it uses the end of column to
terminate writing the rs you can direct it further. Jet uses
the end of recordset (ie. EOF) so if you have a recordset with more
than 65536 records (rows) direct
Excel to write the next record to the column you want it in..

Example:
You wish to bring a 3 field recordset with 80,000 records into Excel
Worksheets("Sheet1").Range("A:A").CopyFromRecordset Rs
Worksheets("Sheet1").Range("D").CopyFromRecordset Rs

This will put the first 65+ records in column ABC and the rest in DEF

Good Luck
TK

 

[onedaywhen]

You wish to bring a 3 field recordset with 80,000 records into Excel

Worksheets("Sheet1").Range("A:A").CopyFromRecordset Rs
Worksheets("Sheet1").Range("D").CopyFromRecordset Rs

A neat trick, however if you have less than 65K rows the second line
will generate an EOF error, so an on error resume next could be useful.
Jamie.

--

 

[Guest]

Hi Jamie

A neat trick, however

if you have less than 65K rows the second line

will generate an EOF error, so an on error resume next
could be useful.
Jamie.

Now that you menshion it I would also expect the EOF error
but in actual test it didn't happen, so I assume it is handled by
CopyFromRecordset.

Good Luck
TK

 

[onedaywhen]

Now that you menshion it I would also expect the EOF error
but in actual test it didn't happen, so I assume it is handled by
CopyFromRecordset.

It's cleverer than I thought <g>.

Jamie.

--

 

[Guest]

I have a similar question. The Get External Data works fine from my
computer, but is it possible for the embedded query to remain connected to
Access, if the .xls is stored in a shared drive folder, and linked to by a
hyperlink/URL from the corporate intranet page? As far as I can tell that
removes the auto-refresh/link to the Access data, or am I missing something
somewhere? Everyone is using Excel 2003, Access 2003 and do not have
SharePoint where I work. They don't want to use DataAccess Pages.