using ado db connection in different workbook

[RB Smissaert]

Is it possible to use an ADO connection that has been established in one
workbook
in another workbook?
Simplified the connection has been set like this:

Public ADOConn As ADODB.Connection

Sub CreateADOConnection()

If ADOConn Is Nothing Then
Set ADOConn = New ADODB.Connection
End If

End Sub

And in another Sub:

strConn = "DSN=System 6000;" & _
"UID=" & Username & ";" & _
"PWD=" & Password
ADOConn.Open strConn


Thanks for any advice.

RBS

 

[Dave Patrick]

AFAIK not possible but there might be other ways to get the desired end
result.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Is it possible to use an ADO connection that has been established in one
| workbook
| in another workbook?
| Simplified the connection has been set like this:
|
| Public ADOConn As ADODB.Connection
|
| Sub CreateADOConnection()
|
| If ADOConn Is Nothing Then
| Set ADOConn = New ADODB.Connection
| End If
|
| End Sub
|
| And in another Sub:
|
| strConn = "DSN=System 6000;" & _
| "UID=" & Username & ";" & _
| "PWD=" & Password
| ADOConn.Open strConn
|
|
| Thanks for any advice.
|
| RBS
|

 

[Peter T]

Hi Bart,

How about something like this -

Sub AddRef()
Dim ref As Object

' "Book1.xls" file name, already open
' "proBook1" project name of Book1.xls

On Error Resume Next

Set ref = ThisWorkbook.VBProject.References("proBook1")

If ref Is Nothing Then
ThisWorkbook.VBProject.References.AddFromFile "Book1.xls"
End If

End Sub

Sub test()
If proBook1.ADOConn Is Nothing Then

proBook1.CreateADOConnection

End If

strConn = "DSN=System 6000;" & _
"UID=" & UserName & ";" & _
"PWD=" & Password
proBook1.ADOConn.Open strConn
End Sub

in Book1.xls your example below Sub CreateADOConnection() with the public
ADOConn

Regards,
Peter T

 

[RB Smissaert]

Hi Peter,

Interesting idea and I will try that.
In general I have found that setting references to other workbooks can cause
a lot of trouble and is best
avoided. It really is not a big issue as the connection will have to be
established only once in each workbook.
Still, I will see.

RBS

 

[Peter T]

Hi again,

Maybe the ref to the other workbook could be added only when needed and
deleted immediately after. This would avoid the potential problem of trying
to close a referenced work while the wb holding the ref is still open.
Obviously would need a quick check to ensure the other wb is open before
adding the ref.

Regards,
Peter T

 

[RB Smissaert]

Hi Peter,

Very interesting idea, but not sure this bit can work:

proBook1.ADOConn.Open strConn

It definitely doesn't compile as above.
Will experiment some more.

RBS

 

[Peter T]

Hi Bart,

Yes you're right, it wouldn't compile until the ref has been added - not a
good idea!

In theory though might be possible to place in a dedicated non-compiled
module without Option Explicit. Apart from not doing a debug compile with
this module included also means never running any code in the module before
saving & distributing. Otherwise is it viable to distribute with the ref
already added.

Regards,
Peter T

 

[RB Smissaert]

OK, what other ways did you have in mind?

RBS

 

[Dave Patrick]

Nothing in particular. I don't know what you're trying to accomplish.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| OK, what other ways did you have in mind?
|
| RBS

 

[RB Smissaert]

Hi again,

Still interesting, but I don't think now it is worth the
trouble just to avoid the one off setting of the connection.
An option is to avoid the 2 workbooks (2 .xla's) and lump them both
together. The idea of having them separate is that one of them can be used
on it's own, but in practice that rarely if ever happens. So that probably
will
make sense. It also would make it more likely that I will have the worlds
largest
commercial .xla

RBS

 

[RB Smissaert]

Let me try to exlain.
I have 2 .xla files, both not loaded as add-ins, so not ticked under Tools,
Add-ins.
Both need a connection to an Interbase database. This is with ADO and ODBC.
At the moment this connection has to be established twice, once for each
..xla.
My idea was to avoid the second one and use the connection of the other
..xla.

RBS

 

[Dave Patrick]

Sorry, I don't have any suggestion for this.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Let me try to exlain.
| I have 2 .xla files, both not loaded as add-ins, so not ticked under
Tools,
| Add-ins.
| Both need a connection to an Interbase database. This is with ADO and
ODBC.
| At the moment this connection has to be established twice, once for each
| .xla.
| My idea was to avoid the second one and use the connection of the other
| .xla.
|
| RBS

 

[RB Smissaert]

No trouble, I will probably just leave it as it is.

RBS

 

[Peter T]

Hi Bart,

As you say probably not worth the trouble, even if it all works in
development every possibility of a reference or compile problem later.

I take it Application.Run + arg's is not an option and get the wb with the
ADO to do all the work.

It also would make it more likely that I will have the worlds
largest commercial .xla

Go for it <g>

Regards,
Peter T

 

[RB Smissaert]

Hi Peter,

I take it Application.Run + arg's is not an option

It would be possible to let the other wb do the job, but then it makes more
sense to lump the 2 wb's into one.
I think that might be the best thing. It would simplify a lot of things.

RBS