[ASP3]Cannot connect to database 2nd time
 

I have a page that connects to an Access database successfully, displaying all the rows on the page. However, if I hit refresh, I get an error on the line number where I'm opening the connection to the database. If I wait a while, it works.

I have another page that displays a subset of data from the same table and it also shows an error for the line number where the connection is opened.

If I am on the page that shows the whole table, and click a link to go to the subset table, I will get the error. The same is true the other way around. If I wait a while between opening pages that use the table, the page connects as it should.

I close both my connection and recordset and set both of these to Nothing at the end of the document.

What causes me to not be able to connect a second time without waiting?
I am new to ASP, so I hope this doesn't sound stupid. It is hopefully some easy thing I am missing, and hopefully my description may be enough so someone here can tell me what I should do or what topic to read up on!

Thanks, all!

What is the lock type and cursor type of your recordset? You may be locking yourself out. Also, do you close your connection and recordset objects on the page?

Ok, I got help from someone at work, so I'll put it here:

objConnection.Timeout = 10 <-- Putting this just before opening the connection solved the problem

common practice for accessing databases is to close the connection as soon as possible.
If you close your connection at the end you can get the following situation:
opening connection -> get data -> click on link for sub-table/refresh -> tries to open connection -> error
why? because the connection to that database is already open. (and locked).
After a while the connection will time out, and you can open it again (as you experienced)
a better way instead of setting the standard time-out to a shorter period would be:
opening connection -> get data -> close connection -> click link for subtable/refresh -> open connection -> get data -> close connection.

Now maybe for an access db this kind of practice isn't -that- important, but as soon as you're using bigger multi-user db's, things like this will get quite important.

Thanks for the extra suggestion, T.U.B.! I will try that as well.