ADO connections and multi-use forms

[Caroline Just]

I am at a mental roadblock when it comes to opening and closing database connections.
I thought I had the problem licked, but then started reusing forms and now
each instance of a form opens a connection. I can move it to my main form,
but then it will remain open throughout the application. I have searched
all over the web and books and cannot find an example that applies to reusing
forms - at least none that make any sense to me. All of them open a connection
and recordset with every instance of a form. I am perplexed ... does every
recordset need its own connection?

Would someone please give me some guidelines or point me to a helpful site?

I'm using ADO, object classes, disconnected recordsets with an MS Access
back-end.

Thanks,

Caroline

[G. Allen]

Let me see if I understand you correctly: You have a form that requires a
database connection. The form is accessed multiple times. You want it to
open the connection and retrieve the data the first time it is viewed but
not on subsequent viewings.

It sounds like what you need to do is check the State property of your recordset
object. If this property indicates that the recordset is closed then open
a connection and retrieve your data. Otherwise skip the connection code
and move on to whatever else the form needs to do before being displayed.

Hope this helps.

"Caroline Just" <cjust@io.com> wrote:
>
>I am at a mental roadblock when it comes to opening and closing database
connections.
> I thought I had the problem licked, but then started reusing forms and
now
>each instance of a form opens a connection. I can move it to my main form,
>but then it will remain open throughout the application. I have searched
>all over the web and books and cannot find an example that applies to reusing
>forms - at least none that make any sense to me. All of them open a connection
>and recordset with every instance of a form. I am perplexed ... does every
>recordset need its own connection?
>
>Would someone please give me some guidelines or point me to a helpful site?
>
>I'm using ADO, object classes, disconnected recordsets with an MS Access
>back-end.
>
>Thanks,
>
>Caroline

[Filipo Mór]

"Caroline Just" <cjust@io.com> wrote:
>
>I am at a mental roadblock when it comes to opening and closing database
connections.
> I thought I had the problem licked, but then started reusing forms and
now
>each instance of a form opens a connection. I can move it to my main form,
>but then it will remain open throughout the application. I have searched
>all over the web and books and cannot find an example that applies to reusing
>forms - at least none that make any sense to me. All of them open a connection
>and recordset with every instance of a form. I am perplexed ... does every
>recordset need its own connection?
>
>Would someone please give me some guidelines or point me to a helpful site?
>
>I'm using ADO, object classes, disconnected recordsets with an MS Access
>back-end.
>
>Thanks,
>
>Caroline

Hi, Caroline!

I think that the best way to manage multiple forms with data access in
your app is keep a public connection opened during the execution time. If
you don't desire use a public variable, you can try to create a persistent
data class. Don't worry about the use of the resources. The OLE DB Provider
manage the connections and network access (this feature is named 'Resource
Dispenser'). However, take care when acessing many forms at the same time.
In this case, the performance may be awful.
Apologise by my horrible English. I hope it helps!

Filipo

[K.Ramaraj]

"G. Allen" <gallenspam@worldnet.att.net> wrote:
>
>Let me see if I understand you correctly: You have a form that requires
a
>database connection. The form is accessed multiple times. You want it
to
>open the connection and retrieve the data the first time it is viewed but
>not on subsequent viewings.
>
>It sounds like what you need to do is check the State property of your recordset
>object. If this property indicates that the recordset is closed then open
>a connection and retrieve your data. Otherwise skip the connection code
>and move on to whatever else the form needs to do before being displayed.
>
>Hope this helps.
>
>"Caroline Just" <cjust@io.com> wrote:
>>
>>I am at a mental roadblock when it comes to opening and closing database
>connections.
>> I thought I had the problem licked, but then started reusing forms and
>now
>>each instance of a form opens a connection. I can move it to my main form,
>>but then it will remain open throughout the application. I have searched
>>all over the web and books and cannot find an example that applies to reusing
>>forms - at least none that make any sense to me. All of them open a connection
>>and recordset with every instance of a form. I am perplexed ... does every
>>recordset need its own connection?
>>
>>Would someone please give me some guidelines or point me to a helpful site?
>>
>>I'm using ADO, object classes, disconnected recordsets with an MS Access
>>back-end.
>>
>>Thanks,
>>
>>Caroline
>