Re: using ado db connection in different workbook

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 news:OzhNHcMAHHA.3928@xxxxxxxxxxxxxxxxxxxxxxx
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" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
news:OYyRr2HAHHA.1224@xxxxxxxxxxxxxxxxxxxxxxx
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
news:%23RcGytHAHHA.4992@xxxxxxxxxxxxxxxxxxxxxxx
> 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" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
> news:#FBLmmHAHHA.3836@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>> news:%231tYKhHAHHA.1196@xxxxxxxxxxxxxxxxxxxxxxx
>> > 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" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
>> > news:uCs4faHAHHA.3620@xxxxxxxxxxxxxxxxxxxxxxx
>> >> 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
>> >> news:eE1qpVHAHHA.4024@xxxxxxxxxxxxxxxxxxxxxxx
>> >> > 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" <bartsmissaert@xxxxxxxxxxxxxxxx> wrote in message
>> >> > news:OneBR0FAHHA.5068@xxxxxxxxxxxxxxxxxxxxxxx
>> >> >> 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
>> >> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>




.

Relevant Pages

  • Re: using ado db connection in different workbook
    ... development every possibility of a reference or compile problem later. ... it wouldn't compile until the ref has been added - ... Maybe the ref to the other workbook could be added only when needed ... Sub AddRef() ...
    (microsoft.public.excel.programming)
  • Re: strange compile when using inheritance
    ... Why are you using ref in the first place? ... compile error that I shouldn't have because the type parameter is correct. ... We have an abstract class called MeltPracData which has the following ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Oldest skii Arctic resp Greenland
    ... I gave a ref where EVERY needed information was in the url, IF Peter A had ... language tools for the first picture. ...
    (sci.archaeology)
  • Re: Executing javascript from app?
    ... The problem is, that when I try to compile it with REF, then it marks ... Anyway, it crashes at AddObject, and the ExecuteStatement also crashes ... MSScriptControl.ScriptControlClass myScriptingEngine; ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: using ado db connection in different workbook
    ... it wouldn't compile until the ref has been added - not a ... Otherwise is it viable to distribute with the ref ... established only once in each workbook. ... Sub AddRef() ...
    (microsoft.public.excel.programming)