Prev: Need to normalize some tables after database has been split
Next: Reminder - Microsoft Responds to the Evolution of Community
From: Richard on 20 May 2010 03:26 hi Instead of Dim and Set a connection, close and set to nothing, can I just use currentproject.connection.execute "code" what will be the consequences and will there be a memory set somewhere? Or should I go the long way.... many thanks in advance for your help. Richard
From: Dirk Goldgar on 20 May 2010 08:57 "Richard" <Richard(a)discussions.microsoft.com> wrote in message news:8B80452B-F67B-4730-8D40-25F20921F9A2(a)microsoft.com... > hi > > Instead of Dim and Set a connection, close and set to nothing, can I just > use > > currentproject.connection.execute "code" > > what will be the consequences and will there be a memory set somewhere? Or > should I go the long way.... There should be no consequences. If you're going to use the connection more than once in a procedure, it's better to define and set a Connection object, but if you're only going to use it once, you don't need to. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: Banana on 20 May 2010 09:29 On 5/20/10 5:57 AM, Dirk Goldgar wrote: > "Richard" <Richard(a)discussions.microsoft.com> wrote in message > news:8B80452B-F67B-4730-8D40-25F20921F9A2(a)microsoft.com... >> hi >> >> Instead of Dim and Set a connection, close and set to nothing, can I >> just use >> >> currentproject.connection.execute "code" >> >> what will be the consequences and will there be a memory set >> somewhere? Or >> should I go the long way.... > > > There should be no consequences. If you're going to use the connection > more than once in a procedure, it's better to define and set a > Connection object, but if you're only going to use it once, you don't > need to. > As an alternative where I need what is called "lazy instantiation", I usually wrap the call to a self-healing property: <code> Property Get MyConn() As ADODB.Connection Static c As ADODB.Connection Select Case True Case c Is Nothing, c.State = adStateClosed Set c = New ADODB.Connection c.ConnectionString = ... ... End Select Set MyConn = c End Property </code> I can still dispose of MyConn by doing this: <code> Set MyConn = Nothing </code> Which usually is only called at the end of session (e.g. Access is about to close down for example). This approach allows me to reference MyConn without needing to worry whether it's open and active or set up a variable. This works very well especially when I'm uncertain which procedure that depends on this connection will be called first or even if at all. This is also safer than a global variable because if an error reset the state or the connection gets closed, the subsequent call to the property will heal. (BTW, it uses a Select Case instead of If/Then with a Or - Doing a Or eagerly evaluates both cases which is impossible and will cause an error - Select Case allows to evaluate both possibility sequentially without an error/need to handle the error) HTH.
From: Richard on 20 May 2010 21:08 Hey guys Thanks for taking time to help and advise. Help appreciated Richard "Banana" wrote: > On 5/20/10 5:57 AM, Dirk Goldgar wrote: > > "Richard" <Richard(a)discussions.microsoft.com> wrote in message > > news:8B80452B-F67B-4730-8D40-25F20921F9A2(a)microsoft.com... > >> hi > >> > >> Instead of Dim and Set a connection, close and set to nothing, can I > >> just use > >> > >> currentproject.connection.execute "code" > >> > >> what will be the consequences and will there be a memory set > >> somewhere? Or > >> should I go the long way.... > > > > > > There should be no consequences. If you're going to use the connection > > more than once in a procedure, it's better to define and set a > > Connection object, but if you're only going to use it once, you don't > > need to. > > > > As an alternative where I need what is called "lazy instantiation", I > usually wrap the call to a self-healing property: > > <code> > Property Get MyConn() As ADODB.Connection > > Static c As ADODB.Connection > > Select Case True > Case c Is Nothing, c.State = adStateClosed > Set c = New ADODB.Connection > c.ConnectionString = ... > ... > End Select > > Set MyConn = c > > End Property > </code> > > I can still dispose of MyConn by doing this: > > <code> > Set MyConn = Nothing > </code> > > Which usually is only called at the end of session (e.g. Access is about > to close down for example). This approach allows me to reference MyConn > without needing to worry whether it's open and active or set up a > variable. This works very well especially when I'm uncertain which > procedure that depends on this connection will be called first or even > if at all. This is also safer than a global variable because if an error > reset the state or the connection gets closed, the subsequent call to > the property will heal. (BTW, it uses a Select Case instead of If/Then > with a Or - Doing a Or eagerly evaluates both cases which is impossible > and will cause an error - Select Case allows to evaluate both > possibility sequentially without an error/need to handle the error) > > HTH. > . >
From: Tony Toews [MVP] on 24 May 2010 15:38
"Dirk Goldgar" <dg(a)NOdataSPAMgnostics.com.invalid> wrote: >If you're going to use the connection more >than once in a procedure, it's better to define and set a Connection object, >but if you're only going to use it once, you don't need to. Why is it better? Save time? Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ Granite Fleet Manager http://www.granitefleet.com/ |