recordset-connection

[tina104291325]

is it better to use one connection per recordset or to have one global connection for all recordsets also how many connection to an access DB can i open?

thnks

[John McKernan]

When using Access (which I only do when the gun to my head is actually loaded!) I would suggest the following (this assumes you are not using databound controls):

1. Open a data connection (I am assuming we are talking about ADO).
2. Return a recordset
3. Populate your form fields, variables, etc (or update the recodset fields, as appropriate)
4. Close the recordset
5. Close the data connection

[JasonGS]

It's usually best to open the connection when your application starts, this is ideally done when the user logs into your program.

Record sets and queries use that connection and then when the program is exited, you would close the connection and consequentially disconnect all of the record sets.

The MS Jet OLE DB provider only allows 64 simultaneous connections to an Access .mdb file.

[vbCowboy]

NO NO NO NO NO!!!!!!!!!!!!!!

Never open a connection and leave it open. Use it then destroy it. ADO was designed to be "User Friendly" that means if it is busily using the connection and you ask it to do something else, it will very nicely open a new connection and use that... The person I replaced at my current job designed his programs using that method. You will have every IT person in your company hating you, it can cause massive delays on the server. So please I implore you do not maintain one connection throughout an application. Because it can morph into 2 or 3 or 4 or .... multiplied by the number of people concurrently running your App. How many people open their app in the morning and leave it running all day??? Please for the sake of your own sanity MY GOD MAN don't do it

[vbCowboy]

Please for the sake of your own sanity MY GOD MAN don't do it

Sorry :

Please for the sake of your own sanity MY GOD WOMAN don't do it

[tina104291325]

thnks for your help guys but u didn't help me enough maybe i should analyse my problem further so here it is: I have created some classes per table each one is representing the recordset of one table and have all the recordset properties plus some other methods that i need it. All the classes r using the same connection but as the number of tables in the DB is growing i encounter some very strange bugs e.g i see some of the objects-classes having a recordcount property >0 even if the reffering table has no records

any ideas ? have to have at least one connection open through all the application but something is puzzle the whole thing