DSN-less connection and ADO vs. DAO

[Corey-g via AccessMonster.com]

Hi,

I am trying to create a single connection procedure that will be re-used for
every SQL call.

Does it matter if I use ADO? Do I need to link / relink tables, or can this
all be done with out linking, and using passthrough queries? How do I go
about updating records in the back end if something changes?

Am i setting myself up for a ton of extra work this way?

What I have so far is a code module (basGlobal), and I have declared cn & rs
as public ADODB objects (connection & recordset) and strSQL, strUID, & strPWD
as strings just after "Option Explicit"

I then have a public function OracleConnect that takes 2 params (UID & PWD)
and returns a ADODB.Connection -- Should I be returning anything?

The first thing I am doing is testing the connection state "If IsNull(cn.
state) then" does this make sense?
If it is null, I set "cn" to a new connection, set the properties, and open.
What would be an apropriate else?

Should I have a single function to handle all queries as well, and how would
I do that? Have the query function call the connection function, the use the
returned connection? Or as I have it at the module level, can I just use
"cn" (I would code these into the same module)? Should all of my calls go to
the query function, and have that open & close the connection each time?

TIA,

Corey

 

[RD]

LOL! Ask questions much? See below.

Hi,

I am trying to create a single connection procedure that will be re-used for
every SQL call.

Good idea.

Does it matter if I use ADO?

I think you should.

Do I need to link / relink tables, or can this
all be done with out linking, and using passthrough queries?

No need to link.

How do I go
about updating records in the back end if something changes?

Use the record writing function I posted. Just take out my UDL and substitute
your own connection. Two parameters: table name and SQL. Works like a charm.

Am i setting myself up for a ton of extra work this way?

I don't think so. Sometimes, though, it's better to put a little more work in
up front to save a lot of work down the road. Modularizing your code is an
excellent thing to do.

What I have so far is a code module (basGlobal), and I have declared cn & rs
as public ADODB objects (connection & recordset) and strSQL, strUID, & strPWD
as strings just after "Option Explicit"

I then have a public function OracleConnect that takes 2 params (UID & PWD)
and returns a ADODB.Connection -- Should I be returning anything?

Interesting. Does it work? Have you made a connection this way? I've never
tried this. Heck, I've never seen this.

The first thing I am doing is testing the connection state "If IsNull(cn.
state) then" does this make sense?

It does if it works.

If it is null, I set "cn" to a new connection, set the properties, and open.
What would be an apropriate else?

Should I have a single function to handle all queries as well, and how would
I do that?

You could adapt the record writing function pretty easily.

Have the query function call the connection function, the use the
returned connection? Or as I have it at the module level, can I just use
"cn" (I would code these into the same module)? Should all of my calls go to
the query function, and have that open & close the connection each time?

TIA,

Corey

I would create a module that did nothing but record handling. And yes, I would
open the connection when needed and close it when done.

Having fun yet? ;-)

Hope this helps,
RD

 

[Corey-g via AccessMonster.com]

THANK YOU RD !!! I was trying to ask a few up front, so that I wasn't asking
too many down the road

I have been testing how this works, and so far I think it may (spent alot of
time digging through posts to see what others were doing, rather than testing
the last couple days)... I will adapt your function and let you know how
things look.

Thanks again,

Corey

 

[Brendan Reynolds]

The first thing I am doing is testing the connection state "If IsNull(cn.
state) then" does this make sense?
If it is null, I set "cn" to a new connection, set the properties, and
open.
What would be an apropriate else?

<snip>

I have no experience with Oracle, so I have avoided responding to your
questions so far, but there is one thing about this post that immediately
rings a warning bell. The State property of the ADODB.Connection object is a
long integer. And a long integer can never, under any circumstances, be
Null.

 

[Corey-g via AccessMonster.com]

Thanks Brendan. I actually changed the test to "If cn is nothing", and that
seems to work (more testing today)...

 

[Brendan Reynolds]

Well, there are two separate questions here - has it been instantiated and
is it open. If it hasn't been instantiated, it can not be open, but if it
has been instantiated, it may or may not be open. So if you want to be sure
that you have an open connection, first check whether it is Nothing, and if
it is not Nothing, then check the State property.