Opening the same table twice in one procedure (ODBC) (1 Viewer)

ian_w

Registered User.
Local time
Today, 02:25
Joined
Jun 13, 2006
Messages
64
Access 2010 with an SQL Server 2008 R2 backend, all tables linked to backed using DSN Less odbc connections.

All working well but when I want to run two different queries on the same table in one module it hangs and say's odbc connection timeout on the second query.

Example

Code:
Set x = db.OpenRecordset("SELECT * FROM Table1 WHERE Field1 = 'A'")
Set y =  db.OpenRecordset("SELECT * FROM Table1 WHERE Field2 = 'B'")

Simple example but hopefully you get the picture.

How can it be done?

I don't need them open at the same time so am I happy to close connection one then open connection two but I can't figure that one out either :eek:
 

namliam

The Mailman - AWF VIP
Local time
Today, 03:25
Joined
Aug 11, 2003
Messages
11,696
use
x.close

to close the connection, in addition perhaps
set x = nothing

Thought the timeout probably isnt the result of having the two recordsets open... More than likely you are searching a large table on an unindexed field which is simply taking too long
 

ian_w

Registered User.
Local time
Today, 02:25
Joined
Jun 13, 2006
Messages
64
Hi,

Thanks for the reply, already tried x.close and then x = nothing but makes no difference.

Query is a find max based on the main index so runs instantly. Just ran it again and the actual error is 'error 3146' odbc - call failed.
 

kevlray

Registered User.
Local time
Yesterday, 19:25
Joined
Apr 5, 2010
Messages
1,046
Just an idea. Is there some reason you could not alias the table on the second query?. I am guessing that the code engine is getting confused somehow.
 

jdraw

Super Moderator
Staff member
Local time
Yesterday, 22:25
Joined
Jan 23, 2006
Messages
15,364
From some other posts and google, you might try adding some code to your error handler routine.
From reading it seems access just reports 1 error, but there is an Errors collection and this routine may provide more info

Code:
Dim i As Long
For i = 0 To Errors.Count - 1
    Debug.Print Errors(i).Number, Errors(i).Description
Next i

Good luck. Let us know if it showed more.

Update after posting:

My first thought was similar to kevlray, or perhaps even 2 different DSNs with similar names is causing confusion. Then some googling suggested there may be additional info in related errors.
 
Last edited:

namliam

The Mailman - AWF VIP
Local time
Today, 03:25
Joined
Aug 11, 2003
Messages
11,696
Might depend on ... are you building the full connection for both?

Perhaps your user is limited to one connection at a time to the database?
 

CSL

Registered User.
Local time
Yesterday, 19:25
Joined
Mar 2, 2015
Messages
19
Just as a quick check have you run each line in SSMS just to check them there, you should get a better idea of any errors that way rather than the access message.
 

ian_w

Registered User.
Local time
Today, 02:25
Joined
Jun 13, 2006
Messages
64
Just thought I would update this, never managed to get it to work using ODBC dbo connections, tried aliasing, closing connections etc, always resulted in the same error.

Just rewrote it using ADODB which actually worked a lot better, got rid of my errors and where able to enclose it all in a transaction.
 

Rx_

Nothing In Moderation
Local time
Yesterday, 20:25
Joined
Oct 22, 2009
Messages
2,803
Would you please try this, it was suggested to me on another project by someone who was headed to work for Microsoft. It has been a point of discussion, but we couldn't replicate the error you are having to validate it.

Create a View of the table, the entire table.
The run the same code with the two recordsets on the view of the table.

This timeout error may seem to be random. Since yours appears to be consistent, I would really appreciate if you would take the time to create a view of the entire table and try the same code, process again.

The ODBC error "Timeout expired" in the SQL Log typically means that a query did not start to return rows with the timeout period, which by default (I think it still is 30 seconds) is 30 seconds.
The reason for this could be that the query itself needed longer time to execute, because of large amounts of data to search and/or poor indexing. It could also be that the query was blocked by another query, and that query was long-running. In fact it does not have to be a query as such that was blocking, but a process holding locks because of an earlier abandoned query, which was not properly handled from SQL Server's point-of-view.

In theory.... creating a view invokes a different execution plan.
I have come across projects where every one of the over 200 tables included a view of that table that was linked to Access.
e.g. Customers with vCustomers - the vCustomers was the linked table to Access.
In some cases, where a table such as Customers is called by many forms, queries and reports, I create a view of the most common fields used in Customers. However, it is out of being lazy and only wanting a dozen fields instead of the dozens during design mode.
However, somewhere in the back of my mind, the execution plan on this view should be more efficient than calling the larger table.

While it won't answer every question for every situation, I would really appreciate if you could perform this experiment of creating a view of the table and running the code against the table then against the view.
Thanks
 

Rx_

Nothing In Moderation
Local time
Yesterday, 20:25
Joined
Oct 22, 2009
Messages
2,803
Sorry to go on about this. Just had to mention the SQLServer:Buffer Manager:Buffer cache hit ratio counter indicates that data is retrieved from memory cache. The number should be around 90. A lower value indicates that SQL Server requires more memory.

At the SQL Server Users Group, the rubber-stamp answer is "more memory".
I recently had a nice tech set up SQL Server on a virtual machine that used the Microsoft Recommendation of 1 GB RAM. Yes, thanks to the sales information that is the recommendation. Time-outs and long queries were a common problem. The virtual disk thrashing was the majority of microprocessor time. Once they finally raised the RAM to 16GB, all of those problems just went away.
So, the SQL Server memory is an honorable mention.
 

Users who are viewing this thread

Top Bottom