using ado db connection in different workbook
 

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

using ado db connection in different workbook
 

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

"RB Smissaert" wrote:
| 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
|

using ado db connection in different workbook
 

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" wrote in message
...
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

using ado db connection in different workbook
 

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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

using ado db connection in different workbook
 

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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

using ado db connection in different workbook
 

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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

using ado db connection in different workbook
 

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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

using ado db connection in different workbook
 

OK, what other ways did you have in mind?

RBS

"Dave Patrick" wrote in message
...
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

"RB Smissaert" wrote:
| 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
|

using ado db connection in different workbook
 

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

"RB Smissaert" wrote:
| OK, what other ways did you have in mind?
|
| RBS

using ado db connection in different workbook
 

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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

using ado db connection in different workbook
 

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" wrote in message
...
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

"RB Smissaert" wrote:
| OK, what other ways did you have in mind?
|
| RBS

using ado db connection in different workbook
 

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

"RB Smissaert" wrote:
| 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

using ado db connection in different workbook
 

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

RBS

"Dave Patrick" wrote in message
...
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

"RB Smissaert" wrote:
| 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

using ado db connection in different workbook
 

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" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

using ado db connection in different workbook
 

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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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