closing database connections

[lindajames]

hi everyone,

i have a asp application that uses a ms access database but sometimes connections are left open and as a result i cannot compact and repair the database. a while ago i came across a script that basically kills all open connections whether active or inactive. but i cant seem to find this script anymore. can anyone tell me how i can do this? i want to create this script and execute it once a day at least that way less connections will be left open.

linda

[txbakers]

Hi Linda, I do this with code on every page. At the bottom of the page (usually) I have two lines for every object created:

myCon.Close() (for connections/RS, etc.)
set myCon = nothing

everytime I create an object I make sure it's nulled before I leave the page. That will help.

[lindajames]

i have that at the bottom of all my pages but still it seems that the database is in use. Sometimes when i try to access my asp pages that use the database, the page just doesnt load, i wait for 3minutes and it still doesnt load until i reboot the server. What can be the cause of this? is there a maximum concurrent connection limit on MS Access?

any advice will be very much appreciated.

cheers
linda

[txbakers]

Yes. Access isn't designed to be a database "server", just a very good desktop database.

I don't know the technical specifics, but Access will leave open pipes. After a certain number of opens and closes, the whole thing will shut down.

You don't need to reboot your computer though - just stop and start the IIS service. That will kill the open pipes and refresh your ability to query and update.

If you are doing multiple updates and other SQL interaction with the web, you really need a "server" type of database like mySQL or MSSQL.

I had the same problem about 3 years ago (search these very forums for my problems from then!) and they all went away when I installed mySQL.

These days I have 1000+ concurrent users making inserts, updates, deletes, addtables, drop tables, and of course selects. Without any drop in service.

[lindajames]

the problem is that, i cannot install SQL server as i have a windows2003 web edition server and i've been told that SQL server is not supported on windows2003 web edition.

how about if I delete all Unclosed DbSessions? will that be better than restarting IIS? if thats the case dont you think it would be a good idea to create a script and and schedule it to run evert hr or so to delete all Unclosed DbSessions?

cheers

[txbakers]

what about mySQL? That is a free database and will work on just about any operating system, so it should work on the WE of 2K3.

I think the problem is Access and the only way to really solve it is to ditch access.

[lindajames]

its just that the application i have uses MS Access. one quick question. i've just been throught the code of some pages of the application and it has alot of .close functions but none of the .close functions have set name = nothing, shall i add that below every .close function?

[txbakers]

wouldn't hurt to destroy the objects themselves.

[lindajames]

so setting the object to nothing will help?

[Xoc]

Yes. Every object has reference count, the number of things pointing at it. When the reference count goes to zero, the object destroys itself. "Set obj = Nothing" causes the variable to no longer point at the object, causing the reference count to drop by one.