Passing ADO conection object to form object on form instantiation

[The Frog]

Hi Guys,

Just wanting some opinions on best method to approach this. I am
working on an Access97 db and we have two forms and an ADO connection
object to contend with.

The desired state is this-
first form has the functionality for the user to choose an MDB file to
connect to, the user does as such, and the ado connection object is
set up and the connection is opened.

secondly, there is a command button on form1 that is supposed to
instantiate an instance of form2 which has code set to run in its on
its Load event. The code that is in this load event requires the ADO
connection object created / set up in the first form.

At the time of this writing the ADO connection object is dimmed in the
first form declarations.

The second form is to load, then run the code (does some reading of
excel workbook data amongst other things), and shows its progress on
its controls (it has a lable being used as a progress bar). Then, when
the process is finished, it should close and unload itself.

The query is then: What is the best way to handle the ADO connection
object and pass it to the form2 object?

In this case I would have preferred to use a class module with events,
but access 97 doesnt support that. I am unable to move the app at this
stage to a newer version of Access, and am wondering if I should just
gut this and start it from scratch. Inheriting things can be so much
fun :-)

Your advice is always appreciated

The Frog

[lyle fairfield]

I think that if

1. Form1 has a module (it seems from your description that it does)
and
2. The ADO connection is dimmed as Public Form1ADOConnection as
ADODB.Connection

then it will be available in Form2's module

as Form_Form1.Form1ADOConnection
without your having to do anything.

Sample code which does nothing but works:

In Form1 Module:

Public Form1ADODBConnection As ADODB.Connection

Private Sub Form_Load()
Set Form1ADODBConnection = CurrentProject.Connection
Form_Form2.Visible = True
End Sub

In Form2 Module:

Private Sub Form_Load()
MsgBox Form_Form1.Form1ADODBConnection.ConnectionString
End Sub

On Jun 12, 11:10*am, The Frog <[e-mail]:

Hi Guys,

Just wanting some opinions on best method to approach this. I am
working on an Access97 db and we have two forms and an ADO connection
object to contend with.

The desired state is this-
first form has the functionality for the user to choose an MDB file to
connect to, the user does as such, and the ado connection object is
set up and the connection is opened.

secondly, there is a command button on form1 that is supposed to
instantiate an instance of form2 which has code set to run in its on
its Load event. The code that is in this load event requires the ADO
connection object created / set up in the first form.

At the time of this writing the ADO connection object is dimmed in the
first form declarations.

The second form is to load, then run the code (does some reading of
excel workbook data amongst other things), and shows its progress on
its controls (it has a lable being used as a progress bar). Then, when
the process is finished, it should close and unload itself.

The query is then: What is the best way to handle the ADO connection
object and pass it to the form2 object?

In this case I would have preferred to use a class module with events,
but access 97 doesnt support that. I am unable to move the app at this
stage to a newer version of Access, and am wondering if I should just
gut this and start it from scratch. Inheriting things can be so much
fun :-)

Your advice is always appreciated

The Frog

[Tony Toews [MVP]]

The Frog <Mr************@googlemail.comwrote:

>At the time of this writing the ADO connection object is dimmed in the
first form declarations.

Why not just dim it in a VBA module and declare it as global?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

[The Frog]

Hi Guys,

Thanks for the feedback. I appreciate the input and benefit from your
experience. The global works okay for this solution - reason being
that I may not know what the name of the parent form is. The idea was
to try and produce the Access 97 evuivalent of a class module that
could be used with feedback being shown to the user.

The second form is actually a data 'ripper' from a syndicated data
source, and what I was hoping to do was to be able to use this in
multiple different processes, just calling it (so to speak) when
needed, by passing it the needed connection for the target DB to pump
the data to. Doing this in newer versions of Access is not a problem,
just build a class module with events (so you can give feedback to
display something or react etc...), and pass the necessary details for
it to do the work. Unfortunately a form class module is not so
flexible it seems. I was hoping that there might be a way to achieve
this that would save me doing a re-write and / or duplicating the
code. Thats cool.

I was also thinking about trying the same thing with the form as with
a full class module, but instead of passing the ADO connection, simply
pass the settings for the connection to the form object (as strings -
declare public variables and use them as properties) and calling a
method to get the process rolling. Has anyone given this a go in A97?
Good / bad? Issues?

Cheers

The Frog

[The Frog]

OK, found a simple (and somewhat obvious) solution to the problem.
Here goes:

1/ Create the desired form module with the necessary design elements
on the form - in this case a progress bar.

2/ declare all the needed parts (ie/ Global variables) as private
var's for the form

3/ declare the sub's and func's that are needed as private in the
forms code

4/ create a public function within the form that accepts the required
input parameters (in this case: (ByRef connDB as ADODB.Connection) as
Boolean)

5/ create a second public function that tests the required parameters
before execution and returns a go/ no go so you know whether or not to
run the actual function.

To use it, declare the form as an object and control it from there...

Works like a charm. Progress form pops up nicely for the specific
task, the bar slides across so the user can see what is going on, and
when it is finished the user knows of success or failure (and in this
case also why). Pretty sweet. Obvious really when you think about it
but sometimes you dont see the forrest for the trees.

Thanks for the input guys, it got me thinking in the right direction.
Appreciated.

Cheers

The Frog

[lyle fairfield]

On Jun 18, 4:20*am, The Frog <[e-mail]:

OK, found a simple (and somewhat obvious) solution to the problem.
Here goes:

1/ Create the desired form module with the necessary design elements
on the form - in this case a progress bar.

2/ declare all the needed parts (ie/ Global variables) as private
var's for the form

3/ declare the sub's and func's that are needed as private in the
forms code

4/ create a public function within the form that accepts the required
input parameters (in this case: (ByRef connDB as ADODB.Connection) as
Boolean)

5/ create a second public function that tests the required parameters
before execution and returns a go/ no go so you know whether or not to
run the actual function.

To use it, declare the form as an object and control it from there...

Works like a charm. Progress form pops up nicely for the specific
task, the bar slides across so the user can see what is going on, and
when it is finished the user knows of success or failure (and in this
case also why). Pretty sweet. Obvious really when you think about it
but sometimes you dont see the forrest for the trees.

Thanks for the input guys, it got me thinking in the right direction.
Appreciated.

Cheers

The Frog

No side-trips to the moon?
Thanksgiving Dinners?
Tickets for the Celtics?
Lunchtime Quickie?

[The Frog]

These are all of course necessary components of any development
process, however since they are almost mandatory I decided not to
mention them for the sake of brevity :-)

Something like this is at least a two moon trip and five quickie
development process. Trying to cut down on the turkey though - gaining
a little weight at the moment!

Cheers

The Frog