DAO Recordset: [Microsoft][ODBC SQL Server Driver]Timeout expired (#0)

  • Thread starter Thread starter Mark Meyers
  • Start date Start date
M

Mark Meyers

I have a very long running query time for queries against a very large SQL
Server 2K table, accessing it using Access 2K. These queries simply take
several minutes to run (and that isn't going to change).
I'm getting the error above after 60 seconds.

To further complicate matters, I set up these various queries by using a
form, and programmatically customize/construct the form's recordsource -
that's the query. It's not an Access query, it's the recordsource of the
form being customized and then "refreshed".

This is an Access MDB/MDE, not a project, and the forms are DAO-based.

What do I do?

- Mark
 
What are these queries? Are they pass-through, so that they run on the SQL
Server server, or do you simply have linked tables, and you're running the
queries againsts the linked tables?

If they're pass-through queries, one of the properties you can set is ODBC
Timeout. The default is 60: either increase it, or set it to 0 (which means
that there won't be a timeout)
 
Doug;
I'm sorry about the terminology being misleading. If you read further into
the note, you will see this:

"It's not an Access query, it's the recordsource of the form being
customized and then "refreshed"."
------------

This is a form's recordsource being programmatically set to a custom value,
in the form of a select statement. So it isn't any form of "Access query".
(But as a 'select' statement, I was thinking of it as a query).

Do you suppose I would have to work with a QueryDef object in code instead?
The only thought I've had to this point involves a bit of work, actually on
more than one form :-(
The thought is maybe create a QueryDef object (in VBA), and then set the
recordsource of the form to that query. The implications include being an
MDE that is used by multiple users. I suppose I could come up with a way to
generate unique query names. Thanks for your response Any other thoughts
on this?

- Mark
 
I may be on a track for success. It's

CurrentProject.Connection.ConnectionTimeout

Only trouble is, Access doesn't allow me to change this, because it says
this object is in use. Even after I try

CurrentProject.Connection.Close

It still says "Operation is not allowed when object is open" on the attempt
to change the timeout. Anyone know how I can change this property?

Thanx in advance.

- Mark
 
This approach should improve response time dramatically, too!

Douglas J. Steele said:
Yes, I'd try creating a pass-through QueryDef object and using it as the
recordset. That way, you can control the Timeout for the query.

--
Doug Steele, Microsoft Access MVP



Mark Meyers said:
Doug;
I'm sorry about the terminology being misleading. If you read further into
the note, you will see this:

"It's not an Access query, it's the recordsource of the form being
customized and then "refreshed"."
------------

This is a form's recordsource being programmatically set to a custom value,
in the form of a select statement. So it isn't any form of "Access query".
(But as a 'select' statement, I was thinking of it as a query).

Do you suppose I would have to work with a QueryDef object in code instead?
The only thought I've had to this point involves a bit of work, actually on
more than one form :-(
The thought is maybe create a QueryDef object (in VBA), and then set the
recordsource of the form to that query. The implications include being an
MDE that is used by multiple users. I suppose I could come up with a
way
to
generate unique query names. Thanks for your response Any other thoughts
on this?

- Mark


Douglas J. Steele said:
What are these queries? Are they pass-through, so that they run on the SQL
Server server, or do you simply have linked tables, and you're running the
queries againsts the linked tables?

If they're pass-through queries, one of the properties you can set is ODBC
Timeout. The default is 60: either increase it, or set it to 0 (which means
that there won't be a timeout)

--
Doug Steele, Microsoft Access MVP



"Mark Meyers" <mmeyers[at]hydrill-usa.com> wrote in message
I have a very long running query time for queries against a very
large
SQL
Server 2K table, accessing it using Access 2K. These queries simply take
several minutes to run (and that isn't going to change).
I'm getting the error above after 60 seconds.

To further complicate matters, I set up these various queries by
using
 
There is an Access Option for the ODBC timeout.
Tools | Options | Advanced

or
Application.SetOption
(not dbengine.setoption)

and the forms are DAO-based.
No, I don't think so.... Your code may be dao based, but I don't
think Access 2K uses DAO for bound forms.

(david)
 
david epsom dot com dot au said:
No, I don't think so.... Your code may be dao based, but I don't
think Access 2K uses DAO for bound forms.

I was under the impression that bound forms in an .mdb will use a DAO
recordset. They certainly do when bound to a local table:

?Typeof Forms(0).Recordset is DAO.Recordset
True
?Typeof Forms(0).Recordset is ADODB.Recordset
False

I think they will when bound to a linked SQL-Server table, too, but have
no way at the moment to verify that.
 
david epsom dot com dot au said:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281998
How to Bind Microsoft Access Forms to ADO Recordsets

Microsoft recommends using DAO rather than ADO for binding to a
form when using JET: ADO rather than JET when using SQL Server.

But Access will start and run even if the DAO files are missing
from your computer, so I think that is probably not helpful to
say that 'forms are DAO based' when considering ODBC timeouts on
forms.

Your thoughts???

I think you're quite right that the ODBC timeout is certainly the issue,
though I don't know whether this can be adjusted on the Tools ->
Options -> Advanced tab, or whether it's necessary to modify the
registry, as directed in, for example, KB article 295231 (discussing
problems in upsizing large tables to SQL Server):

http://support.microsoft.com/default.aspx?scid=kb;en-us;295231

I don't see anything on my Advanced Options page that seems to relate to
the ODBC timeout. So if adjusting something in the Advanced Options
dialog doesn't do it, then following the directions in the KB article,
one might try adjusting the QueryTimeout value under the following
subkey in the registry:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC

Although the article I cited suggested disabling it entirely by setting
the value to 0, I imagine the Mr. Meyers' problem might be solved by
setting it to 120, or 180, or some other larger setting than 60 seconds.
I am far from knowledgeable in this area, however.

All this aside, my comment was addressing what seemed to me to be an
incorrect -- or at least over-simplified -- statement in your response
to the original post. No doubt I misinterpreted your statement, and I
didn't intend to be unhelpful, but I am concerned that others might
misinterpret it the same way.

I'm well aware that it's possible to bind a form to an ADO recordset,
even in an .mdb, but it requires special programming to do so. As far
as Access starting and running even if the DAO files are missing, I
don't know the details of this, so I don't have any comment on it. Do
bound forms work normally, and their Recordset properties return DAO
recordsets, under these conditions? If so, I'd have to guess that the
DAO code is statically linked in Access. But this is another of the
many areas I know little about.
 
I don't see anything on my Advanced Options page that seems to
the ODBC timeout. So if adjusting something in the Advanced

What? UrK?!?! It's not there. That means I've never used it.
That means it's all 'air code'. That means....


Perhaps Doug Steele's suggestion will be appropriate,
or a general equivalent:
application.dbengine.Workspaces(0).Databases(0).QueryTimeout = 120

(MS has used the term "PassThrough" indiscriminately
in the past, which has muddied the waters: we are talking
about a JET workspace here, with JET SQL)

I'm well aware that it's possible to bind a form to an ADO
even in an .mdb, but it requires special programming to do

No more special than binding to a DAO recordset - which
means that even if it calls itself a DAO recordset, it
must be a special 'ADO compatible' recordset, which is not
really a DAO recordset at all...

My thought was that, underneath, Access now uses OLEDB
-- which is not DAO at all, even if it masquerades as DAO.
But you will notice that I've had to abandon the Access
Option suggestion, and adopt Doug's suggestion (DAO interface
to JET engine), so my initial objection (That DAO was the
wrong place to look for ODBC timeout) looks pretty weak now.

(david)
 
Hi ,
Let me tell you an amazing story...

Do not throw this mail away before you have read it. You have all to win and
nothing to loose. Here is a chance to earn a lot of money with no costs so
ever, and no obligations. So why not read the rest of this story and then,
if you think that this business is nothing for you? Throw this mail away
or...give it a try.

In this letter there is a part that tells you about the legalities of this
process. A part that tells of glamour stories, and a part that explains how
this "madness" actually works. The only part you truly have to concern
yourself with is the instructions. Follow the instructions carefully and you
can see how five dollars, probability, and multiplication will work to make
you thousands. I know it sounds too easy, I thought so too, but my advice to
you is...read on, what do you have to lose?


THE FORWARDED LETTER

Dear Friends: Greetings: I am a retired attorney. A few years ago a man came
to me with a letter. He asked me to verify the fact that this was legal to
do. I told him I would review it and get back to him. When I first read the
letter my client brought me, I thought it was some "off-the-wall" idea to
make money. A week and a half later we met in my office to discuss the
issue. I told him the letter he originally brought me was not 100% legal. My
client then asked me to alter it to make it perfectly legal. I asked him to
make one small change in the letter. I was still curious about the letter,
so he explained to me how it works. I thought it seemed like a long shot, so
I decided against participating. But before my client left, I asked him to
keep me updated on his results. About two months later, he called me to tell
me he had received over $800,000 in cash. I didn't believe him, so he asked
me to try this idea and find out for myself. I thought about it for a couple
of days and decided I really didn't have anything to lose, so I asked him
for a copy of the letters. I followed the instructions exactly, mailed 200
copies, and sure enough, the money started coming in! It arrived slowly at
first, but coming. I kept a precise record of the earnings, and in the end,
it totalled $978,493! I could hardly believe it. I met with my friend for
lunch to find out exactly how it worked. My part in this was to give my help
to him, making sure that the whole thing was legal, since no one wants to
take the risk of doing something illegal.

By now you are surely curious to know what small changes to make. If you
sent a letter like this one out, in order to be completely legal, you must
actually sell something in order to receive a dollar in return. So when you
send a dollar to each of the names on the list, you must include these words
in the message-box, "PLEASE PUT ME ON YOUR MAILING LIST" and include your
name and emailaddress. The item you will receive for the dollar you sent to
the five people below is the message with the request.

At the time I first tried this idea, I was earning a good living as a
lawyer. But everyone in the legal profession will tell you there is a lot of
stress that comes with the job. I told myself if things worked out, I would
retire from my practice and play golf. I decided to try the letter again,
but this time I sent 500 copies. Three months later, I had totalled
$2,341,178!


Here are a few reasons a person might give for not trying this program:

€ Some people think they can never make a lot of money with anything this
simple.

€ Some are afraid they will be ridiculed for trying

€ Some dream of large sums of money, but do nothing to actually achieve it.

€ Some are just plain lazy.

€ Some are afraid of losing their investment. They think this program is
designed to beat them out of a few dollars.

The system works if you will just try it. But you must follow the simple
instructions exactly, and in less than three months, you will be looking at
$800,000 ! Keep what you are doing to yourself for awhile. Many will tell
you it won`t work and will try to talk you out of your dreams. Let them know
of your success after it works.



LETTERS FROM PARTICIPANTS IN THIS PROGRAM:
My name is David Rhodes. In 1992 my car was repossessed and bill collectors
were hounding me. I was laid off and my unemployment ran out. In October of
1992, I received a letter telling me how to earn a large sum of money
anytime I wanted. Of course, I was skeptical. But because I was so desperate
and virtually had nothing to lose, I gave it a try. In January 1993, my
family and I went on a 10-day cruise. The next month I bought a brand new
Mercedes with cash! I am currently building a home in Virginia and I will
never have to work again. This money program really works perfectly every
time. I have never failed to receive less than $500,000. This is a
legitimate, money-making opportunity. It does not require you to sell
anything or to come in contact with people. And , best of all, you only
leave the house to mail the letters. If you have always believed that
someday you would get the lucky break, then simply follow the instructions
and make dreams come true.

Larry McMahon, Norfolk, VA Six months ago, I received this letter and
ignored it. Five more came within a period of time and I ignored them also.
I was tempted, but I was convinced that they were just a Hoax. After three
weeks of deliberating, I decided to give it a try ( not expecting much ).
Two weeks went by and nothing happened. The fourth week was unbelievable! I
can't say I received $800,000 but I have received over $120,000. For the
first time in years, I am debt free. I am doing this again, only this time
starting with 500 post. I strongly recommend that you follow the
instructions exactly as outlined in this letter.






INSTRUCTIONS

1. Go to www.paypal.com and open an account. (If you do this before the last
September you will be paid by Paypal with five dollar as a introduction
bonus so this effort will not cost you anything.) Forward a payment of
totally 5 dollar through your new PayPal account, 1 dollar each to the five
peoples mail-address listed in the bottom of this page. Select "Service" as
payment type. In the Subject field you should write "Mailing list" and in
the Note field write the following phrase, "PLEASE PUT ME ON YOUR MAILING
LIST" and include your name and emailaddress. What you are doing is
requesting a legitimate service and you are paying for it!


2. Now take the #1 name off the list that you see at the bottom, move the
other names up (5 becomes 4, 4 becomes 3, etc...) and add YOUR name as
number 5 on the list.

3. COPY this letter. You do not have to type it 200 times. Simply place your
cursor at the top of the page, hold it and drag it all the way down to the
end of the letter. Then click on "edit" and select "copy". Now open up a
notepad file on your computer and put the cursor at the top of the page in
the notepad, click on 'edit' and then select 'paste' it will copy the letter
for you onto your computer.
Remove the name next to the #1 on the list (the list at the bottom of the
message) and move the rest of the names up one position (#2 becomes #1, #3
becomes #2, etc.....) Then place your name and your mail-address (which is
your payment address at payPal) in the #5 position. Then save it, make sure
it is saved as a .txt file.

4. When you have completed the instructions, type the address of one of
these search engines.

www.google.com
www.yahoo.com
www.altavista.com
www.askjeeves.com
www.Altavista.com
www.Fathead.com
www.TotalSEEk.com
www.Dmoz.com
www.SearchPort.com
www.Jayde.com
www.HotBot.com
www.ICQ IT!.com
www.WorldLight.com
www.Dogpile.com

In the search box, type "message forums" or "discussion forums". A list of
over 2 million boards will come up. Go to each board name and right click on
the mouse. Select 'copy'. Then go to your "write mail" box, as if you were
about to write a letter and select 'paste'. Do that until you have at least
200 locations. The more boards you find, the higher your
income potential will be. The search engine will give you a ton of message
forums; don't just grab the ones on the first page, dig deep and grab some
from the middle and the back also, to help make sure you're visiting places
no one has been to already. When you've found your 200+ locations, "copy"
all your locations, "paste" them in a word document or notepad, and "Save"
the file. Once you have the locations, visit each one, register, and post
your letter. It's that simple. How many hours at your current job would it
take for you to make 6000 dollars ?

Post this article as a new message by highlighting the text of this letter
and selecting paste from the edit menu. Fill in the Subject with "This is
pretty amazing.......", THAT'S IT! You're done with your first one,
Congratulations. Some boards may be difficult to figure out where to post.
If any board is too problematic for any reason, simply move on to the next
board. Get some of your favourite CDs to listen to while you do this also.
Keep a copy of this letter so you can use it a second time. Post it out
again in six months, but Post it with the addresses you receive with each
dollar. It will work better the second time. NOTE: This service is 100%
legal - (Refer to title 18 section 1302 of the U.S. Postal & lottery laws).
You can also call the U.S. Post Office (1-800-725-2161) to verify this. Hold
on to every letter and mailing list request you receive. They will be proof
of your service.



HOW THIS WORKS
When you send out 200 Posts, it is estimated that at least 15 people will
respond and send you a $1.00 to be placed on your mailing list ($15.00).
Those 15 will Post 200 Posts each and 225 people send you $1.00 to be placed
on your mailing list ($225.00). Those 225 people Post 200 Posts each and
3,375 people send you $1.00 to be placed on your mailing list ($3,375.00)
Those 3,375 post 200 posts each and 50,625 people send you $1.00 each
($50,625). Those 50,625 post 200 posts each and 759,375 people send you
$1.00 ($759,375.00) At this point your name drops off the list, but so far
you have received $813,615.00. Even if less then 15 people respond each
time, you will still receive an income in the tens of thousands of dollars.
The reality is, even if you only made a few hundred dollars out of all this,
that's still an excellent return for such a miniscule investment. Most
people spend a lot more on lottery tickets and have nothing to show for it,
and forget about how much money is needed to play the market. Also, after
posting this message on 100 message boards, it may get boring.
Stay focused on what you want and don't quit until you finish.


P.S.
This program remains successful because of the honesty and integrity of the
participants and by their carefully adhering to the directions. Look at it
this way. If you are of integrity, the program will continue and the money
that so many others have received will come your way.

When your money begins to come in, give the first 10% to charity with spirit
and share a good fortune!



ADDRESSES TO SEND YOUR PAYMENT OF 1 DOLLAR EACH TO (THROUGH YOUR PAYPAL
ACCOUNT) AND THE MAILING LIST REQUEST:

(If you open a Pay-pal account before last September Pay-pal will give you a
bonus, 5 dollar, so this experience will cost you nothing!!!)


1)
Alex O'Leary
(e-mail address removed)
8377 APT. I MONTGOMERY RUN RD.
ELLICOTT CITY MD 21043

2)
Ralph H Sweeney
(e-mail address removed)

3)
Dick Baldwin, Santa Monica
(e-mail address removed)

4)
Clark Olsen, Minnesota
(e-mail address removed)

5)
Sarah Becker
(e-mail address removed)
Homestead, Fl 33033
 
Back
Top