ADO Syntax, from DAO to ADO, from Access to SQL


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: ADO Syntax, from DAO to ADO, from Access to SQL

  1. #1
    T. Bradley Dean Guest

    ADO Syntax, from DAO to ADO, from Access to SQL

    I'm in the process of converting an app that currently uses DAO to ADO 2.5.
    (Visual Basic 6, SP4). Currently the app connects to an Access 97 DB, but we
    will be switching to SQL 7. Hence the change from DAO to ADO.

    So here is my question. How and when do I open a connection to the DB?
    Currently I am defining the connection object and recordset objects
    individually for each form.

    For Example:

    Option Explicit

    Dim objConn as ADODB.Connection
    Dim objRst as ADODB.Recordset

    Private Sub cmdButton_Click()

    Set objConn = New ADODB.Connection
    objConn.Open ConnectionString 'ConnectionString is just
    what it sounds like. It's actually a function

    Set objRst = New ADODB.Recordset
    objRst.Open "Select * from tblCustomers",objConn, adOpenKeyset,
    adLockOptimistic

    '*** Do Whatever Here ***

    objRst.Close: Set objRst = Nothing
    objConn.Close: Set objConn = Nothing

    End Sub

    Is this right? One concern I have is that I am opening and closing the
    database too frequently. For SQL this probably won't be too big of a deal
    but an Access DB might choke.

    What do you guys think? I know it's a broad question but as I've been
    researching ADO I've seen it done different ways. Any help would be greatly
    appreciated.

    T. Bradley Dean



  2. #2
    Q Guest

    Re: ADO Syntax, from DAO to ADO, from Access to SQL


    "T. Bradley Dean" <Bradley.Dean@InfoDish.com> wrote:
    >I'm in the process of converting an app that currently uses DAO to ADO 2.5.
    >(Visual Basic 6, SP4). Currently the app connects to an Access 97 DB, but

    we
    >will be switching to SQL 7. Hence the change from DAO to ADO.
    >
    >So here is my question. How and when do I open a connection to the DB?
    >Currently I am defining the connection object and recordset objects
    >individually for each form.
    >
    >For Example:
    >
    >Option Explicit
    >
    >Dim objConn as ADODB.Connection
    >Dim objRst as ADODB.Recordset
    >
    >Private Sub cmdButton_Click()
    >
    > Set objConn = New ADODB.Connection
    > objConn.Open ConnectionString 'ConnectionString is just
    >what it sounds like. It's actually a function
    >
    > Set objRst = New ADODB.Recordset
    > objRst.Open "Select * from tblCustomers",objConn, adOpenKeyset,
    >adLockOptimistic
    >
    > '*** Do Whatever Here ***
    >
    > objRst.Close: Set objRst = Nothing
    > objConn.Close: Set objConn = Nothing
    >
    >End Sub
    >
    >Is this right? One concern I have is that I am opening and closing the
    >database too frequently. For SQL this probably won't be too big of a deal
    >but an Access DB might choke.
    >
    >What do you guys think? I know it's a broad question but as I've been
    >researching ADO I've seen it done different ways. Any help would be greatly
    >appreciated.
    >
    >T. Bradley Dean
    >
    >


    For starters, a global Connection object is a good idea. Instead of destroying
    it after each use, just close it. That will free the DB but you won't have
    the overhead of creating a connection object over and again.




  3. #3
    T. Bradley Dean Guest

    Re: ADO Syntax, from DAO to ADO, from Access to SQL

    What if it get's closed from one routine while another is still using it?

    Well, wait. How about if I wrote a CloseConnection routine that checked to
    see how many recordsets where open. If there are none then it closes it. If
    there is one or more then obviosly some other routine needs the connection
    open and it should leave it alone. Just thinking outloud...

    On the same basis, an OpenConnection could check to see if the connection
    was already open. If it is, cool, do nothing. If it's not, then open it.

    I think that may work...

    (Thanks!)

    Q <michaelq@towersoft.com.au> wrote in message
    news:39c02680$1@news.devx.com...
    >
    > "T. Bradley Dean" <Bradley.Dean@InfoDish.com> wrote:
    > >I'm in the process of converting an app that currently uses DAO to ADO

    2.5.
    > >(Visual Basic 6, SP4). Currently the app connects to an Access 97 DB, but

    > we
    > >will be switching to SQL 7. Hence the change from DAO to ADO.
    > >
    > >So here is my question. How and when do I open a connection to the DB?
    > >Currently I am defining the connection object and recordset objects
    > >individually for each form.
    > >
    > >For Example:
    > >
    > >Option Explicit
    > >
    > >Dim objConn as ADODB.Connection
    > >Dim objRst as ADODB.Recordset
    > >
    > >Private Sub cmdButton_Click()
    > >
    > > Set objConn = New ADODB.Connection
    > > objConn.Open ConnectionString 'ConnectionString is

    just
    > >what it sounds like. It's actually a function
    > >
    > > Set objRst = New ADODB.Recordset
    > > objRst.Open "Select * from tblCustomers",objConn, adOpenKeyset,
    > >adLockOptimistic
    > >
    > > '*** Do Whatever Here ***
    > >
    > > objRst.Close: Set objRst = Nothing
    > > objConn.Close: Set objConn = Nothing
    > >
    > >End Sub
    > >
    > >Is this right? One concern I have is that I am opening and closing the
    > >database too frequently. For SQL this probably won't be too big of a deal
    > >but an Access DB might choke.
    > >
    > >What do you guys think? I know it's a broad question but as I've been
    > >researching ADO I've seen it done different ways. Any help would be

    greatly
    > >appreciated.
    > >
    > >T. Bradley Dean
    > >
    > >

    >
    > For starters, a global Connection object is a good idea. Instead of

    destroying
    > it after each use, just close it. That will free the DB but you won't

    have
    > the overhead of creating a connection object over and again.
    >
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center