| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to store a connection with parameters into a workbook | Excel Discussion (Misc queries) | |||
| reconnecting a workbook connection from Access-Excel | Excel Discussion (Misc queries) | |||
| Pivot chart loses data connection when copied to new workbook | Charts and Charting in Excel | |||
| how do i directly edit a connection string in an Excel workbook q. | Excel Programming | |||
| Open a password protected excel workbook from second workbook to fetch data using dynamic connection | Excel Programming | |||
Hybrid Mode
