ODBC Error

[Dkline]

I have an ODBC connection between an Access database and an Excel
spreadsheet. If I open the Access mdb and run the query, everything is just
fine.

The query (although a qurey of queries) is not that complicated or large. It
returns only 240 records.

When I open the Excel file and it goes to the same query as its datasource,
I get this error:
"[Microsoft][ODBC Microsoft Access Driver] Could not start session. Too many
sessions already active."

Why does it work in its native Access but the same query causes Excel have a
conniption?

 

[Guest]

Could it be that your query of queries is the problem? There may be a limit
on the number of ODBC sessions that could be happening at once. Perhaps you
should use a make table query for your last query and then use ODBC to
connect to the table instead.

 

[Jamie Collins]

The query (although a qurey of queries) is not that complicated or

large.

Ah, gotta love that MS Access terminology <g>. A 'Query' is usually a
VIEW, sometimes a PROCEDURE but often something non-standard e.g. a
'pass through' link to an ODBC data source.

If your 'queries' are not complicated, then they are probably VIEWs
e.g. to hide the complexity of a JOIN between tables in the database.
Is this correct, or have you something more complicated e.g. 'linked
tables'? Which version of Jet/MS Access are you using?

Jamie.

--

 

[Dkline]

Would each query in the query be a separate and distinct ODBC session?

I'm going to try the Make Table and see if that cures the problem,

Could it be that your query of queries is the problem? There may be a
limit
on the number of ODBC sessions that could be happening at once. Perhaps
you
should use a make table query for your last query and then use ODBC to
connect to the table instead.

I have an ODBC connection between an Access database and an Excel
spreadsheet. If I open the Access mdb and run the query, everything is
just
fine.

The query (although a qurey of queries) is not that complicated or large.
It
returns only 240 records.

When I open the Excel file and it goes to the same query as its
datasource,
I get this error:
"[Microsoft][ODBC Microsoft Access Driver] Could not start session. Too
many
sessions already active."

Why does it work in its native Access but the same query causes Excel
have a
conniption?

 

[Dkline]

The version in question is Office 2000. All of the underlying tables are
linked. The Access links are to either one of two Access databases. There
are five links spread over three Excel files.

 

[Dkline]

The real problem seems to be the number of worksheets in the workbook that
had an ODBC source. The source is the same for each of those worksheets but
each worksheet seems to represent a separate session. We determined this by
temporarily deleting 3 worksheets of the 23 that had external data.

At one point i thought it was a version problem i.e. Office 2000 vs. Ofice
2003. That was not the case as I tested on verious machines with the several
versions of Office and it failed (or passed) in the same manner on each
machine.

So I'm guesssing that the limit is around 20 or 21 sessions at the same
time. Again the source for each worksheet was the same Access
Query-of-Queries.

Is each worksheet with an ODBC source a separate session or does each query
in the query-of-queries each represent a session? My guess is each worksheet
is a session.

 

[Jamie Collins]

I'm guesssing that the limit is around 20 or 21 sessions at the same

time.

I'd merely be guessing too. Documentation on the inner workings of
MSQuery seem to be non-existent. When using ADO I know how many
connections I've opened, usually just one.

Remember the .mdb will have a limit to the number of concurrent
connections i.e. a theoretical limit of 255 (assuming the .ldb has
cleared successfully) but I've been told the 'safe' limit is more like
10.

Jamie.

--