Home |
Search |
Today's Posts |
#1
|
|||
|
|||
ODBC Error
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? |
#2
|
|||
|
|||
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. "Dkline" wrote: 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? |
#3
|
|||
|
|||
Dkline wrote: 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. -- |
#4
|
|||
|
|||
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, "Gary Rowe" wrote in message ... 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. "Dkline" wrote: 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? |
#5
|
|||
|
|||
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. "Jamie Collins" wrote in message oups.com... Dkline wrote: 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. -- |
#6
|
|||
|
|||
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. "Dkline" wrote in message ... 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? |
#7
|
|||
|
|||
Dkline wrote: 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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Findlink Error | Links and Linking in Excel | |||
Illegal operation error while printing EXCEL or WORD Files | Excel Discussion (Misc queries) | |||
Error when entering and exiting excel | Excel Discussion (Misc queries) | |||
Downloading Templates for Excel error with a windows error. | Excel Discussion (Misc queries) | |||
#REF error | Excel Worksheet Functions |