more ADO stuff


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 12 of 12

Thread: more ADO stuff

Hybrid View

  1. #1
    Garry Charles Guest

    more ADO stuff


    I have been experimenting a little further with ADO and the use of DLL class
    files for connectivity to an Access database table etc . Now I can not seem
    to work out how to utilise the connection's recordset (two fields for two
    text boxes) and display it in the two text boxes in the main project. I am
    specifically trying to keep the database functions in the class file (referenced
    from the main project files) and the display (GUI) stuff) in the main project................Can
    someone please help ?

    =============
    project file
    =============

    Option Explicit

    Private dbConnectionClass As DBConnectionProject.dbConnectionClass

    Private Sub OpenConnectionCommand_Click()
    Dim strConnect As String
    Dim strProvider As String
    Dim strDataSource As String
    Dim strDatabaseName As String
    Dim strLogon As String

    strProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
    strDataSource = App.Path
    strDatabaseName = "\northwind.mdb;"
    strDataSource = "Data Source=" & strDataSource & strDatabaseName
    strConnect = strProvider & strDataSource

    Set dbConnectionClass = New DBConnectionProject.dbConnectionClass
    dbConnectionClass.DataBaseConnectionInformation = strConnect
    dbConnectionClass.OpenDatabaseConnection
    ConnectionLabel.Caption = strConnect
    ShowButtons (2)
    dbConnectionClass.OpenRecords ("select * from employees")
    End Sub

    Private Sub CloseConnectionCommand_Click()
    dbConnectionClass.CloseRecords
    dbConnectionClass.CloseDatabaseConnection
    ShowButtons (1)
    End Sub

    Private Sub ShowButtons(number As Integer)
    Select Case (number)
    Case 1: OpenConnectionCommand.Enabled = True
    CloseConnectionCommand.Enabled = False
    Case 2: OpenConnectionCommand.Enabled = False
    CloseConnectionCommand.Enabled = True
    End Select
    End Sub

    Private Sub FirstCommand_Click()
    dbConnectionClass.FirstRecord
    LoadControls
    End Sub

    Private Sub PreviousCommand_Click()
    dbConnectionClass.PreviousRecord
    LoadControls
    End Sub

    Private Sub NextCommand_Click()
    dbConnectionClass.NextRecord
    LoadControls
    End Sub

    Private Sub LastCommand_Click()
    dbConnectionClass.LastRecord
    LoadControls
    End Sub

    Private Sub LoadControls()
    End Sub

    Private Sub ClearControls()
    FirstNameText.Text = ""
    SecondNameText.Text = ""
    End Sub


    =============
    DLL class file
    =============
    Option Explicit

    Public mdbConnection As ADODB.Connection
    Public mdbRecords As ADODB.Recordset
    Private mstrDataBaseConnectionInformation As String

    Public Property Let DataBaseConnectionInformation(ByVal vData As String)
    mstrDataBaseConnectionInformation = vData
    End Property

    Public Property Get DataBaseConnectionInformation() As String
    DataBaseConnectionInformation = mstrDataBaseConnectionInformation
    End Property

    Public Sub OpenDatabaseConnection()
    Set mdbConnection = New ADODB.Connection
    mdbConnection.ConnectionString = mstrDataBaseConnectionInformation
    mdbConnection.Open
    End Sub

    Public Sub OpenRecords(SQL As String)
    Set mdbRecords = New ADODB.Recordset
    mdbRecords.ActiveConnection = mdbConnection
    mdbRecords.Open SQL, mdbConnection, adOpenDynamic
    End Sub

    Public Sub FirstRecord()
    If mdbRecords.BOF = False Then mdbRecords.MoveFirst
    End Sub

    Public Sub PreviousRecord()
    If mdbRecords.BOF = False Then mdbRecords.MovePrevious
    If mdbRecords.BOF = True Then mdbRecords.MoveFirst
    End Sub

    Public Sub NextRecord()
    If mdbRecords.BOF = False Then mdbRecords.MoveNext
    If mdbRecords.BOF = True Then mdbRecords.MoveLast
    End Sub

    Public Sub LastRecord()
    If mdbRecords.BOF = False Then mdbRecords.MoveLast
    End Sub

    Public Sub CloseRecords()
    mdbRecords.Close
    Set mdbRecords = Nothing
    End Sub

    Public Sub CloseDatabaseConnection()
    mdbConnection.Close
    Set mdbConnection = Nothing
    End Sub



  2. #2
    Steve Guest

    Re: more ADO stuff


    Hmm, it looks like you've got the start of a Connection class, but you're
    likely going to need either a recordset wrapping class to actually expose
    records, or a collection container and data classes to use the connection
    class to read, then store and retrieve the data as your form requires it.

    I've tried the recordset approach with limited success, but I find the biggest
    drawback is the record navigation and ensuring that the data on the form
    is always in sync with the row pointed to by the RS. The bonus of this approach
    is that it gives you access to built in sorting, filtering, and finding.

    Typically I use a collection container that uses the connection to open a
    recordset then create instances of data for each row and add them to a private
    collection. This method indexes quite fast, but isn't ideal for large number
    of rows. This way, your form can retrieve one or more of the objects from
    the contained collection and access the data values through properties.

    It's quite a topic to try and discuss over a newsgroup.

    Steve.

    "Garry Charles" <grc-the-master@brain-cpu.freeserve.co.uk> wrote:
    >
    >I have been experimenting a little further with ADO and the use of DLL class
    >files for connectivity to an Access database table etc . Now I can not seem
    >to work out how to utilise the connection's recordset (two fields for two
    >text boxes) and display it in the two text boxes in the main project. I

    am
    >specifically trying to keep the database functions in the class file (referenced
    >from the main project files) and the display (GUI) stuff) in the main project................Can
    >someone please help ?
    >
    >=============
    >project file
    >=============
    >
    >Option Explicit
    >
    >Private dbConnectionClass As DBConnectionProject.dbConnectionClass
    >
    >Private Sub OpenConnectionCommand_Click()
    >Dim strConnect As String
    >Dim strProvider As String
    >Dim strDataSource As String
    >Dim strDatabaseName As String
    >Dim strLogon As String
    >
    >strProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
    >strDataSource = App.Path
    >strDatabaseName = "\northwind.mdb;"
    >strDataSource = "Data Source=" & strDataSource & strDatabaseName
    >strConnect = strProvider & strDataSource
    >
    >Set dbConnectionClass = New DBConnectionProject.dbConnectionClass
    >dbConnectionClass.DataBaseConnectionInformation = strConnect
    >dbConnectionClass.OpenDatabaseConnection
    >ConnectionLabel.Caption = strConnect
    >ShowButtons (2)
    >dbConnectionClass.OpenRecords ("select * from employees")
    >End Sub
    >
    >Private Sub CloseConnectionCommand_Click()
    >dbConnectionClass.CloseRecords
    >dbConnectionClass.CloseDatabaseConnection
    >ShowButtons (1)
    >End Sub
    >
    >Private Sub ShowButtons(number As Integer)
    >Select Case (number)
    >Case 1: OpenConnectionCommand.Enabled = True
    > CloseConnectionCommand.Enabled = False
    >Case 2: OpenConnectionCommand.Enabled = False
    > CloseConnectionCommand.Enabled = True
    >End Select
    >End Sub
    >
    >Private Sub FirstCommand_Click()
    >dbConnectionClass.FirstRecord
    >LoadControls
    >End Sub
    >
    >Private Sub PreviousCommand_Click()
    >dbConnectionClass.PreviousRecord
    >LoadControls
    >End Sub
    >
    >Private Sub NextCommand_Click()
    >dbConnectionClass.NextRecord
    >LoadControls
    >End Sub
    >
    >Private Sub LastCommand_Click()
    >dbConnectionClass.LastRecord
    >LoadControls
    >End Sub
    >
    >Private Sub LoadControls()
    >End Sub
    >
    >Private Sub ClearControls()
    >FirstNameText.Text = ""
    >SecondNameText.Text = ""
    >End Sub
    >
    >
    >=============
    >DLL class file
    >=============
    >Option Explicit
    >
    >Public mdbConnection As ADODB.Connection
    >Public mdbRecords As ADODB.Recordset
    >Private mstrDataBaseConnectionInformation As String
    >
    >Public Property Let DataBaseConnectionInformation(ByVal vData As String)
    > mstrDataBaseConnectionInformation = vData
    >End Property
    >
    >Public Property Get DataBaseConnectionInformation() As String
    > DataBaseConnectionInformation = mstrDataBaseConnectionInformation
    >End Property
    >
    >Public Sub OpenDatabaseConnection()
    >Set mdbConnection = New ADODB.Connection
    >mdbConnection.ConnectionString = mstrDataBaseConnectionInformation
    >mdbConnection.Open
    >End Sub
    >
    >Public Sub OpenRecords(SQL As String)
    >Set mdbRecords = New ADODB.Recordset
    >mdbRecords.ActiveConnection = mdbConnection
    >mdbRecords.Open SQL, mdbConnection, adOpenDynamic
    >End Sub
    >
    >Public Sub FirstRecord()
    >If mdbRecords.BOF = False Then mdbRecords.MoveFirst
    >End Sub
    >
    >Public Sub PreviousRecord()
    >If mdbRecords.BOF = False Then mdbRecords.MovePrevious
    >If mdbRecords.BOF = True Then mdbRecords.MoveFirst
    >End Sub
    >
    >Public Sub NextRecord()
    >If mdbRecords.BOF = False Then mdbRecords.MoveNext
    >If mdbRecords.BOF = True Then mdbRecords.MoveLast
    >End Sub
    >
    >Public Sub LastRecord()
    >If mdbRecords.BOF = False Then mdbRecords.MoveLast
    >End Sub
    >
    >Public Sub CloseRecords()
    >mdbRecords.Close
    >Set mdbRecords = Nothing
    >End Sub
    >
    >Public Sub CloseDatabaseConnection()
    >mdbConnection.Close
    >Set mdbConnection = Nothing
    >End Sub
    >
    >



  3. #3
    Garry Charles Guest

    Re: more ADO stuff


    Hi Steve. Is there any way that you can give me further info or source code
    to get a better idea of what you refer to in your reply. Don't forget, I
    Am a novice in this area and I do not understand everything about containers
    etc, basis , yes!

    feel free to email direct !!

    "Steve" <stevepyn@hotmail.com> wrote:
    >
    >Hmm, it looks like you've got the start of a Connection class, but you're
    >likely going to need either a recordset wrapping class to actually expose
    >records, or a collection container and data classes to use the connection
    >class to read, then store and retrieve the data as your form requires it.
    >
    >I've tried the recordset approach with limited success, but I find the biggest
    >drawback is the record navigation and ensuring that the data on the form
    >is always in sync with the row pointed to by the RS. The bonus of this approach
    >is that it gives you access to built in sorting, filtering, and finding.
    >
    >Typically I use a collection container that uses the connection to open

    a
    >recordset then create instances of data for each row and add them to a private
    >collection. This method indexes quite fast, but isn't ideal for large number
    >of rows. This way, your form can retrieve one or more of the objects from
    >the contained collection and access the data values through properties.
    >
    >It's quite a topic to try and discuss over a newsgroup.
    >
    >Steve.
    >
    >"Garry Charles" <grc-the-master@brain-cpu.freeserve.co.uk> wrote:
    >>
    >>I have been experimenting a little further with ADO and the use of DLL

    class
    >>files for connectivity to an Access database table etc . Now I can not

    seem
    >>to work out how to utilise the connection's recordset (two fields for two
    >>text boxes) and display it in the two text boxes in the main project. I

    >am
    >>specifically trying to keep the database functions in the class file (referenced
    >>from the main project files) and the display (GUI) stuff) in the main project................Can
    >>someone please help ?
    >>
    >>=============
    >>project file
    >>=============
    >>
    >>Option Explicit
    >>
    >>Private dbConnectionClass As DBConnectionProject.dbConnectionClass
    >>
    >>Private Sub OpenConnectionCommand_Click()
    >>Dim strConnect As String
    >>Dim strProvider As String
    >>Dim strDataSource As String
    >>Dim strDatabaseName As String
    >>Dim strLogon As String
    >>
    >>strProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
    >>strDataSource = App.Path
    >>strDatabaseName = "\northwind.mdb;"
    >>strDataSource = "Data Source=" & strDataSource & strDatabaseName
    >>strConnect = strProvider & strDataSource
    >>
    >>Set dbConnectionClass = New DBConnectionProject.dbConnectionClass
    >>dbConnectionClass.DataBaseConnectionInformation = strConnect
    >>dbConnectionClass.OpenDatabaseConnection
    >>ConnectionLabel.Caption = strConnect
    >>ShowButtons (2)
    >>dbConnectionClass.OpenRecords ("select * from employees")
    >>End Sub
    >>
    >>Private Sub CloseConnectionCommand_Click()
    >>dbConnectionClass.CloseRecords
    >>dbConnectionClass.CloseDatabaseConnection
    >>ShowButtons (1)
    >>End Sub
    >>
    >>Private Sub ShowButtons(number As Integer)
    >>Select Case (number)
    >>Case 1: OpenConnectionCommand.Enabled = True
    >> CloseConnectionCommand.Enabled = False
    >>Case 2: OpenConnectionCommand.Enabled = False
    >> CloseConnectionCommand.Enabled = True
    >>End Select
    >>End Sub
    >>
    >>Private Sub FirstCommand_Click()
    >>dbConnectionClass.FirstRecord
    >>LoadControls
    >>End Sub
    >>
    >>Private Sub PreviousCommand_Click()
    >>dbConnectionClass.PreviousRecord
    >>LoadControls
    >>End Sub
    >>
    >>Private Sub NextCommand_Click()
    >>dbConnectionClass.NextRecord
    >>LoadControls
    >>End Sub
    >>
    >>Private Sub LastCommand_Click()
    >>dbConnectionClass.LastRecord
    >>LoadControls
    >>End Sub
    >>
    >>Private Sub LoadControls()
    >>End Sub
    >>
    >>Private Sub ClearControls()
    >>FirstNameText.Text = ""
    >>SecondNameText.Text = ""
    >>End Sub
    >>
    >>
    >>=============
    >>DLL class file
    >>=============
    >>Option Explicit
    >>
    >>Public mdbConnection As ADODB.Connection
    >>Public mdbRecords As ADODB.Recordset
    >>Private mstrDataBaseConnectionInformation As String
    >>
    >>Public Property Let DataBaseConnectionInformation(ByVal vData As String)
    >> mstrDataBaseConnectionInformation = vData
    >>End Property
    >>
    >>Public Property Get DataBaseConnectionInformation() As String
    >> DataBaseConnectionInformation = mstrDataBaseConnectionInformation
    >>End Property
    >>
    >>Public Sub OpenDatabaseConnection()
    >>Set mdbConnection = New ADODB.Connection
    >>mdbConnection.ConnectionString = mstrDataBaseConnectionInformation
    >>mdbConnection.Open
    >>End Sub
    >>
    >>Public Sub OpenRecords(SQL As String)
    >>Set mdbRecords = New ADODB.Recordset
    >>mdbRecords.ActiveConnection = mdbConnection
    >>mdbRecords.Open SQL, mdbConnection, adOpenDynamic
    >>End Sub
    >>
    >>Public Sub FirstRecord()
    >>If mdbRecords.BOF = False Then mdbRecords.MoveFirst
    >>End Sub
    >>
    >>Public Sub PreviousRecord()
    >>If mdbRecords.BOF = False Then mdbRecords.MovePrevious
    >>If mdbRecords.BOF = True Then mdbRecords.MoveFirst
    >>End Sub
    >>
    >>Public Sub NextRecord()
    >>If mdbRecords.BOF = False Then mdbRecords.MoveNext
    >>If mdbRecords.BOF = True Then mdbRecords.MoveLast
    >>End Sub
    >>
    >>Public Sub LastRecord()
    >>If mdbRecords.BOF = False Then mdbRecords.MoveLast
    >>End Sub
    >>
    >>Public Sub CloseRecords()
    >>mdbRecords.Close
    >>Set mdbRecords = Nothing
    >>End Sub
    >>
    >>Public Sub CloseDatabaseConnection()
    >>mdbConnection.Close
    >>Set mdbConnection = Nothing
    >>End Sub
    >>
    >>

    >



  4. #4
    Steve Guest

    Re: more ADO stuff


    Ok.. I'll put a couple quick examples with a database together this weekend
    and drop them in your e-mail.

    Steve.

    "Garry Charles" <grc-the-master@brain-cpu.freeserve.co.uk> wrote:
    >
    >Hi Steve. Is there any way that you can give me further info or source code
    >to get a better idea of what you refer to in your reply. Don't forget, I
    >Am a novice in this area and I do not understand everything about containers
    >etc, basis , yes!
    >
    >feel free to email direct !!
    >
    >"Steve" <stevepyn@hotmail.com> wrote:
    >>
    >>Hmm, it looks like you've got the start of a Connection class, but you're
    >>likely going to need either a recordset wrapping class to actually expose
    >>records, or a collection container and data classes to use the connection
    >>class to read, then store and retrieve the data as your form requires it.
    >>
    >>I've tried the recordset approach with limited success, but I find the

    biggest
    >>drawback is the record navigation and ensuring that the data on the form
    >>is always in sync with the row pointed to by the RS. The bonus of this

    approach
    >>is that it gives you access to built in sorting, filtering, and finding.
    >>
    >>Typically I use a collection container that uses the connection to open

    >a
    >>recordset then create instances of data for each row and add them to a

    private
    >>collection. This method indexes quite fast, but isn't ideal for large number
    >>of rows. This way, your form can retrieve one or more of the objects from
    >>the contained collection and access the data values through properties.
    >>
    >>It's quite a topic to try and discuss over a newsgroup.
    >>
    >>Steve.
    >>
    >>"Garry Charles" <grc-the-master@brain-cpu.freeserve.co.uk> wrote:
    >>>
    >>>I have been experimenting a little further with ADO and the use of DLL

    >class
    >>>files for connectivity to an Access database table etc . Now I can not

    >seem
    >>>to work out how to utilise the connection's recordset (two fields for

    two
    >>>text boxes) and display it in the two text boxes in the main project.

    I
    >>am
    >>>specifically trying to keep the database functions in the class file (referenced
    >>>from the main project files) and the display (GUI) stuff) in the main

    project................Can
    >>>someone please help ?
    >>>
    >>>=============
    >>>project file
    >>>=============
    >>>
    >>>Option Explicit
    >>>
    >>>Private dbConnectionClass As DBConnectionProject.dbConnectionClass
    >>>
    >>>Private Sub OpenConnectionCommand_Click()
    >>>Dim strConnect As String
    >>>Dim strProvider As String
    >>>Dim strDataSource As String
    >>>Dim strDatabaseName As String
    >>>Dim strLogon As String
    >>>
    >>>strProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
    >>>strDataSource = App.Path
    >>>strDatabaseName = "\northwind.mdb;"
    >>>strDataSource = "Data Source=" & strDataSource & strDatabaseName
    >>>strConnect = strProvider & strDataSource
    >>>
    >>>Set dbConnectionClass = New DBConnectionProject.dbConnectionClass
    >>>dbConnectionClass.DataBaseConnectionInformation = strConnect
    >>>dbConnectionClass.OpenDatabaseConnection
    >>>ConnectionLabel.Caption = strConnect
    >>>ShowButtons (2)
    >>>dbConnectionClass.OpenRecords ("select * from employees")
    >>>End Sub
    >>>
    >>>Private Sub CloseConnectionCommand_Click()
    >>>dbConnectionClass.CloseRecords
    >>>dbConnectionClass.CloseDatabaseConnection
    >>>ShowButtons (1)
    >>>End Sub
    >>>
    >>>Private Sub ShowButtons(number As Integer)
    >>>Select Case (number)
    >>>Case 1: OpenConnectionCommand.Enabled = True
    >>> CloseConnectionCommand.Enabled = False
    >>>Case 2: OpenConnectionCommand.Enabled = False
    >>> CloseConnectionCommand.Enabled = True
    >>>End Select
    >>>End Sub
    >>>
    >>>Private Sub FirstCommand_Click()
    >>>dbConnectionClass.FirstRecord
    >>>LoadControls
    >>>End Sub
    >>>
    >>>Private Sub PreviousCommand_Click()
    >>>dbConnectionClass.PreviousRecord
    >>>LoadControls
    >>>End Sub
    >>>
    >>>Private Sub NextCommand_Click()
    >>>dbConnectionClass.NextRecord
    >>>LoadControls
    >>>End Sub
    >>>
    >>>Private Sub LastCommand_Click()
    >>>dbConnectionClass.LastRecord
    >>>LoadControls
    >>>End Sub
    >>>
    >>>Private Sub LoadControls()
    >>>End Sub
    >>>
    >>>Private Sub ClearControls()
    >>>FirstNameText.Text = ""
    >>>SecondNameText.Text = ""
    >>>End Sub
    >>>
    >>>
    >>>=============
    >>>DLL class file
    >>>=============
    >>>Option Explicit
    >>>
    >>>Public mdbConnection As ADODB.Connection
    >>>Public mdbRecords As ADODB.Recordset
    >>>Private mstrDataBaseConnectionInformation As String
    >>>
    >>>Public Property Let DataBaseConnectionInformation(ByVal vData As String)
    >>> mstrDataBaseConnectionInformation = vData
    >>>End Property
    >>>
    >>>Public Property Get DataBaseConnectionInformation() As String
    >>> DataBaseConnectionInformation = mstrDataBaseConnectionInformation
    >>>End Property
    >>>
    >>>Public Sub OpenDatabaseConnection()
    >>>Set mdbConnection = New ADODB.Connection
    >>>mdbConnection.ConnectionString = mstrDataBaseConnectionInformation
    >>>mdbConnection.Open
    >>>End Sub
    >>>
    >>>Public Sub OpenRecords(SQL As String)
    >>>Set mdbRecords = New ADODB.Recordset
    >>>mdbRecords.ActiveConnection = mdbConnection
    >>>mdbRecords.Open SQL, mdbConnection, adOpenDynamic
    >>>End Sub
    >>>
    >>>Public Sub FirstRecord()
    >>>If mdbRecords.BOF = False Then mdbRecords.MoveFirst
    >>>End Sub
    >>>
    >>>Public Sub PreviousRecord()
    >>>If mdbRecords.BOF = False Then mdbRecords.MovePrevious
    >>>If mdbRecords.BOF = True Then mdbRecords.MoveFirst
    >>>End Sub
    >>>
    >>>Public Sub NextRecord()
    >>>If mdbRecords.BOF = False Then mdbRecords.MoveNext
    >>>If mdbRecords.BOF = True Then mdbRecords.MoveLast
    >>>End Sub
    >>>
    >>>Public Sub LastRecord()
    >>>If mdbRecords.BOF = False Then mdbRecords.MoveLast
    >>>End Sub
    >>>
    >>>Public Sub CloseRecords()
    >>>mdbRecords.Close
    >>>Set mdbRecords = Nothing
    >>>End Sub
    >>>
    >>>Public Sub CloseDatabaseConnection()
    >>>mdbConnection.Close
    >>>Set mdbConnection = Nothing
    >>>End Sub
    >>>
    >>>

    >>

    >



  5. #5
    Guy Smith Guest

    Re: more ADO stuff


    I understand that you want to separate the database functions and the GUI
    stuff. That is good. Your running into complications because you've taken
    the separation a little too far.

    The biggest problem with your code is you have tightly coupled the ADO recordset
    with the connection. With the design, each recordset will have it's own
    connection. That is not good. Connections are an expensive, slow process
    and in most DBs (not Access) you will have a licence limit on the number
    you can open. So, if your making a client-server architecture, you want
    to share "one" connection for all the queries (and recordsets) that you
    run.

    So, to start, you want a class that opens a connection and does the basic
    DB operations. It should also be able to execute queries (Select and Update/Delete/Insert
    (sometimes called CRUD). In addition it must allow transaction handling
    (start/ commit) transactions.

    So (writing on the fly....) in a Class Module

    ====================================
    Private mADOConn as ADOConnection

    Sub OpenConnection(strConnect as string)
    Set mADOConn = New ADODB.Connection
    mADOConn.Open strConnect
    End Sub

    Sub CloseConnection()
    mADOConn.Close
    (probably want to set moADOConn = Nothing in the class destructor)
    End Sub

    Sub ExecuteQueryAction(strSQL as string)
    'this will handle "Deletes", "Updates" and "Inserts"
    mADOConn.Execute(strSQL)
    End Sub

    Sub ExecuteQuerySelect (strSQL as string, adoRec as ADODB.Recordset )
    'only thing left to handle are selects, which return data
    set adoRec = new ADODB.Recordset
    set adoRec = mADOConn.Execute (strSQL)
    End Sub

    Sub BeginTrans
    moADOConn.BeginTrans
    End Sub

    Sub CommitTrans
    moADOConn.CommitTrans
    End Sub

    ====================================

    When you use this class, you want to create to create only one instance of
    it and then use that instance throughout your project. If you create more
    than one instance you'll make more than one connection.

    You'll notice that I did not try to encapsulate the ADO Recordset in this
    class. Think of the ADO Recordset as an application form in some office
    building. The application get filled out by one person, partially approved
    in one department, the rest approved in another, and it is filed in yet
    another (must be a govt office!). The application form simply acts to hold
    and transfer information between the departments (layers). It is not a part
    of any department. You refered to the Recordset as a part of the DB funtions.
    Don't think of it that way. Think of it as something that is used to transfer
    info between layers (i.e. the DB and the UI).

    So feel free to use Recordsets in the UI layer. To use this class, you want
    to create an ADO recordset in the UI layer, send it to the DB via the class,
    then manipulate it locally.

    Hope this helps!?

    Guy Smith

  6. #6
    Chris Hylton Guest

    Re: more ADO stuff


    I'm not sure I see much of the benefit here...maybe it's just me...but wrappering
    all this in a class seems to be serious overkill...it's one thing to wrapper
    your connection properties into an object, so that the connection to the
    datasource is isolated and can be reused over and over...but, I would think
    you'd be better off just returning an ADO recordset to your application and
    leave it at that.

    I've seen (over the years) a number of projects where the developers 'wrappered'
    recordsets into objects...and while there were some code 'reuse' advantages
    in some (not all) of those projects...mostly I just found performance problems
    that didn't end up being a good tradeoff for the functionality developed...i.e.
    the stuff they were doing just ended up being a 'class' that did the same
    darn stuff that the recordset supported natively...move, search, sort, etc...and
    it was just extra code to execute (performance).

    All I'm saying is make sure your 'design' here doensn't get in the way of
    your application...not everything needs to be 'classed' to death just for
    the sake of building objects...that's what ADO is, your object into the database...not
    much need to class it unless your app REALLY requires the data it deals with
    to be a 'class'...

    Chris

  7. #7
    Steve Guest

    Re: more ADO stuff


    It depends on the problem. It can certainly be overkill if you're working
    on a simple small application that isn't going to be changed very often,
    but a couple of scenarios where it can be useful are:

    When your schema can be changed rapidly. If your Forms and objects access
    an ADO recordset directly, accessing fields directly can be a pain in the
    butt if field names change. Aliasing can avoid most of these situations.

    When you need the flexibility to attach to different data sources. If a class
    wraps your recordset, it doesn't matter what datasource schema you connect
    to, your presentation layer only talks to business objects, not the data
    layer directly. It is much easier to "mark" what fields are and are not available
    to keep your PL isolated from the data.

    Encapsulating validation code. As properties are changed, or as you issue
    a Save call, validation code can be executed before the data is actually
    changed. (Rule validation, simple things like length checking, [for fields
    that don't support MaxLength] etc.)

    Overall I'll wrap RS's or introduce collection containers to define a contract
    for accessing data. Exposing a recordset offers free reign over who changes
    values, and performs actions like updates, or even introduces bugs by doing
    something like closing the RS.

    Steve.

    "Chris Hylton" <cchylton@hotmail.com> wrote:
    >
    >I'm not sure I see much of the benefit here...maybe it's just me...but wrappering
    >all this in a class seems to be serious overkill...it's one thing to wrapper
    >your connection properties into an object, so that the connection to the
    >datasource is isolated and can be reused over and over...but, I would think
    >you'd be better off just returning an ADO recordset to your application

    and
    >leave it at that.
    >
    >I've seen (over the years) a number of projects where the developers 'wrappered'
    >recordsets into objects...and while there were some code 'reuse' advantages
    >in some (not all) of those projects...mostly I just found performance problems
    >that didn't end up being a good tradeoff for the functionality developed...i.e.
    >the stuff they were doing just ended up being a 'class' that did the same
    >darn stuff that the recordset supported natively...move, search, sort, etc...and
    >it was just extra code to execute (performance).
    >
    >All I'm saying is make sure your 'design' here doensn't get in the way of
    >your application...not everything needs to be 'classed' to death just for
    >the sake of building objects...that's what ADO is, your object into the

    database...not
    >much need to class it unless your app REALLY requires the data it deals

    with
    >to be a 'class'...
    >
    >Chris



  8. #8
    Garry Charles Guest

    Re: more ADO stuff



    Guy, thanks for all that. It makes things a bit clearer. However, there is
    one little point, as a novice to ADO, that I must ask and that is

    In your ExecuteQuerySelect sub...why do you have two lines with
    'set adoRec' ? (I'm not sure about this bit)




    "Guy Smith" <no@email.com> wrote:
    >
    >I understand that you want to separate the database functions and the GUI
    >stuff. That is good. Your running into complications because you've taken
    >the separation a little too far.
    >
    >The biggest problem with your code is you have tightly coupled the ADO recordset
    >with the connection. With the design, each recordset will have it's own
    >connection. That is not good. Connections are an expensive, slow process
    >and in most DBs (not Access) you will have a licence limit on the number
    >you can open. So, if your making a client-server architecture, you want
    >to share "one" connection for all the queries (and recordsets) that you
    >run.
    >
    >So, to start, you want a class that opens a connection and does the basic
    >DB operations. It should also be able to execute queries (Select and Update/Delete/Insert
    >(sometimes called CRUD). In addition it must allow transaction handling
    >(start/ commit) transactions.
    >
    >So (writing on the fly....) in a Class Module
    >
    >====================================
    >Private mADOConn as ADOConnection
    >
    >Sub OpenConnection(strConnect as string)
    > Set mADOConn = New ADODB.Connection
    > mADOConn.Open strConnect
    >End Sub
    >
    >Sub CloseConnection()
    > mADOConn.Close
    > (probably want to set moADOConn = Nothing in the class destructor)
    >End Sub
    >
    >Sub ExecuteQueryAction(strSQL as string)
    > 'this will handle "Deletes", "Updates" and "Inserts"
    > mADOConn.Execute(strSQL)
    >End Sub
    >
    >Sub ExecuteQuerySelect (strSQL as string, adoRec as ADODB.Recordset )


    > 'only thing left to handle are selects, which return data
    > set adoRec = new ADODB.Recordset
    > set adoRec = mADOConn.Execute (strSQL)
    >End Sub
    >
    >Sub BeginTrans
    > moADOConn.BeginTrans
    >End Sub
    >
    >Sub CommitTrans
    > moADOConn.CommitTrans
    >End Sub
    >
    >====================================
    >
    >When you use this class, you want to create to create only one instance

    of
    >it and then use that instance throughout your project. If you create more
    >than one instance you'll make more than one connection.
    >
    >You'll notice that I did not try to encapsulate the ADO Recordset in this
    >class. Think of the ADO Recordset as an application form in some office
    >building. The application get filled out by one person, partially approved
    >in one department, the rest approved in another, and it is filed in yet
    >another (must be a govt office!). The application form simply acts to hold
    >and transfer information between the departments (layers). It is not a

    part
    >of any department. You refered to the Recordset as a part of the DB funtions.
    > Don't think of it that way. Think of it as something that is used to transfer
    >info between layers (i.e. the DB and the UI).
    >
    >So feel free to use Recordsets in the UI layer. To use this class, you

    want
    >to create an ADO recordset in the UI layer, send it to the DB via the class,
    >then manipulate it locally.
    >
    >Hope this helps!?
    >
    >Guy Smith



  9. #9
    Guy Smith Guest

    Re: more ADO stuff


    "Garry Charles" <grc-the-master@brain-cpu.freeserve.co.uk> wrote:
    >
    >
    >Guy, thanks for all that. It makes things a bit clearer. However, there

    is
    >one little point, as a novice to ADO, that I must ask and that is
    >
    >In your ExecuteQuerySelect sub...why do you have two lines with
    >'set adoRec' ? (I'm not sure about this bit)


    Oops... I apologize, that is my fault (typing too fast). You don't want
    to set it twice. Remove the line "set adoRec = new ADODB.Recordset". It
    shouldn't be there.

    Guy

  10. #10
    Chris Hylton Guest

    Re: more ADO stuff


    Yea, I definitely see your point...I typically don't use recordsets in that
    manner anyway...I'm a bigger fan of coding this into the database, but that's
    probably 'old hat' for most OO folks...business objects are nice, but most
    of my projects have always ended up stressing performance over flexibility...I
    guess that's why I have concern over wrapping recordsets in classes. I've
    seen orders of magnitude on performance hits when doing this. I guess if
    you load the class and then destroy the recordset this isn't near the issue
    in the cases I've seen where the recordset is continually accessed inside
    the object.

    I tend to use disconnected recordsets and never allow direct access to the
    database through a recordset. Even when I build business objects, I still
    access the database via stored procedures, so the recordset never becomes
    an issue, it's only a data retrieval mechanism. This is pretty much the
    direction of .NET anyway, so changing mindsets won't be too difficult.

    I was just pointing out a common issue I see with wrappering objects with
    things already developed that handle the majority of application needs.
    But, when the business rules require it, there can definitely be a big advantage
    to the way a business object is designed...hope you didn't think I was trying
    to rain on your parade...just pointing out an observation to keep in mind
    in general design work.

    Chris

    "Steve" <stevepyn@hotmail.com> wrote:
    >
    >It depends on the problem. It can certainly be overkill if you're working
    >on a simple small application that isn't going to be changed very often,
    >but a couple of scenarios where it can be useful are:
    >
    >When your schema can be changed rapidly. If your Forms and objects access
    >an ADO recordset directly, accessing fields directly can be a pain in the
    >butt if field names change. Aliasing can avoid most of these situations.
    >
    >When you need the flexibility to attach to different data sources. If a

    class
    >wraps your recordset, it doesn't matter what datasource schema you connect
    >to, your presentation layer only talks to business objects, not the data
    >layer directly. It is much easier to "mark" what fields are and are not

    available
    >to keep your PL isolated from the data.
    >
    >Encapsulating validation code. As properties are changed, or as you issue
    >a Save call, validation code can be executed before the data is actually
    >changed. (Rule validation, simple things like length checking, [for fields
    >that don't support MaxLength] etc.)
    >
    >Overall I'll wrap RS's or introduce collection containers to define a contract
    >for accessing data. Exposing a recordset offers free reign over who changes
    >values, and performs actions like updates, or even introduces bugs by doing
    >something like closing the RS.
    >
    >Steve.
    >
    >"Chris Hylton" <cchylton@hotmail.com> wrote:
    >>
    >>I'm not sure I see much of the benefit here...maybe it's just me...but

    wrappering
    >>all this in a class seems to be serious overkill...it's one thing to wrapper
    >>your connection properties into an object, so that the connection to the
    >>datasource is isolated and can be reused over and over...but, I would think
    >>you'd be better off just returning an ADO recordset to your application

    >and
    >>leave it at that.
    >>
    >>I've seen (over the years) a number of projects where the developers 'wrappered'
    >>recordsets into objects...and while there were some code 'reuse' advantages
    >>in some (not all) of those projects...mostly I just found performance problems
    >>that didn't end up being a good tradeoff for the functionality developed...i.e.
    >>the stuff they were doing just ended up being a 'class' that did the same
    >>darn stuff that the recordset supported natively...move, search, sort,

    etc...and
    >>it was just extra code to execute (performance).
    >>
    >>All I'm saying is make sure your 'design' here doensn't get in the way

    of
    >>your application...not everything needs to be 'classed' to death just for
    >>the sake of building objects...that's what ADO is, your object into the

    >database...not
    >>much need to class it unless your app REALLY requires the data it deals

    >with
    >>to be a 'class'...
    >>
    >>Chris

    >



  11. #11
    Garry Charles Guest

    Re: more ADO stuff


    Firstly, I’d like to say thanks to Guy and Steve for their help so far.
    Anyway, here’s my code (after adding Steve’s latest help hint). The project
    basically comprises of text boxes to display data (through the recordset)
    and buttons for navigation. My “add” record functions do not yet work properly,
    typical for a beginner eh?

    Any additional constructive criticism is welcome…(via email if necessary)

    ====================================================
    main project code
    ====================================================
    Option Explicit

    Private dbConnectionClass As DBConnectionProject.dbConnectionClass
    Private dbRec As ADODB.Recordset

    Dim strConnect As String
    Dim strProvider As String
    Dim strDataSource As String
    Dim strDatabaseName As String
    Dim strLogon As String
    Dim strSQL As String
    Dim dbConnected As Boolean

    Private Sub SetConnectionDetails(fPath As String, fFilename As String)
    strProvider = "Provider=Microsoft.Jet.OLEDB.3.51;"
    strDataSource = fPath
    strDatabaseName = fFilename
    strDataSource = "Data Source=" & strDataSource & strDatabaseName
    strConnect = strProvider & strDataSource
    strSQL = "select * from employees"
    ConnectionLabel.Caption = strConnect
    End Sub

    Private Sub OpenConnectionCommand_Click()
    If Right(File1.FileName, 4) = ".mdb" Then
    InitConnection
    Else: MsgBox ("Can not open the connection. Wrong type of file")
    End If
    End Sub

    Private Sub InitConnection()
    Set dbConnectionClass = New DBConnectionProject.dbConnectionClass
    dbConnectionClass.DataBaseConnectionInformation = strConnect
    dbConnectionClass.OpenDatabaseConnection
    ConnectionLabel.Caption = strConnect
    ShowButtons (2)
    dbConnected = True
    InitRecordSet
    FirstCommand_Click
    End Sub

    Private Sub InitRecordSet()
    Set dbRec = New ADODB.Recordset
    dbRec.ActiveConnection = strConnect
    dbRec.Source = strSQL
    dbRec.CursorType = adOpenKeyset
    dbRec.LockType = adLockPessimistic
    If dbConnected = True Then dbRec.Open
    End Sub

    Private Sub CloseRecordSet()
    ClearControls 1
    Set dbRec = Nothing
    End Sub

    Private Sub CloseConnectionCommand_Click()
    ClearControls 1
    CloseRecordSet
    dbConnectionClass.CloseDatabaseConnection
    ShowButtons 1
    dbConnected = False
    End Sub

    Private Sub FirstCommand_Click()
    If dbConnected = True Then
    If dbRec.BOF = False Then dbRec.MoveFirst
    LoadControls
    End If
    End Sub

    Private Sub PreviousCommand_Click()
    If dbConnected = True Then
    If dbRec.BOF = False Then dbRec.MovePrevious
    If dbRec.BOF = True Then dbRec.MoveFirst
    LoadControls
    End If
    End Sub

    Private Sub NextCommand_Click()
    If dbConnected = True Then
    If dbRec.EOF = False Then dbRec.MoveNext
    If dbRec.EOF = True Then dbRec.MoveLast
    LoadControls
    End If
    End Sub

    Private Sub LastCommand_Click()
    If dbConnected = True Then
    If dbRec.EOF = False Then dbRec.MoveLast
    LoadControls
    End If
    End Sub

    Private Sub LoadControls()
    If dbConnected = True Then
    FirstNameText.Text = dbRec("FirstName").Value
    SecondNameText.Text = dbRec("LastName").Value
    End If
    End Sub

    Private Sub ClearControls(number As Integer)
    Select Case (number)
    Case 1: FirstNameText.Text = ""
    SecondNameText.Text = ""
    ConnectionLabel.Caption = ""
    Case 2: FirstNameText.Text = ""
    SecondNameText.Text = ""
    End Select
    End Sub

    Private Sub ShowButtons(number As Integer)
    Select Case (number)
    Case 1: OpenConnectionCommand.Enabled = True
    CloseConnectionCommand.Enabled = False
    Case 2: OpenConnectionCommand.Enabled = False
    CloseConnectionCommand.Enabled = True
    End Select
    End Sub

    Private Sub ClearCommand_Click()
    ClearControls 2
    End Sub

    Private Sub Drive1_Change()
    Dir1.Path = Drive1.Drive
    End Sub

    Private Sub Dir1_Change()
    File1.Path = Dir1.Path
    End Sub

    Private Sub File1_DblClick()
    SetConnectionDetails Dir1.Path & "\", File1.FileName
    End Sub

    Private Sub UpdateCommand_Click()
    If dbConnected = True Then
    dbConnectionClass.Begin
    dbRec("FirstName").Value = FirstNameText.Text
    dbRec("LastName").Value = SecondNameText.Text
    dbRec.Update
    dbConnectionClass.Terminate
    End If
    End Sub

    Private Sub AddCommand_Click()
    If dbConnected = True Then
    dbConnectionClass.Begin
    dbRec("FirstName").Value = FirstNameText.Text
    dbRec("LastName").Value = SecondNameText.Text
    dbRec.AddNew
    dbConnectionClass.Terminate
    End If
    End Sub

    ====================================================
    class connection code
    ====================================================

    Option Explicit

    Private mdbConnection As ADODB.Connection
    Private mdbRecords As ADODB.Recordset
    Private mstrDataBaseConnectionInformation As String

    Public Property Let DataBaseConnectionInformation(ByVal vData As String)
    mstrDataBaseConnectionInformation = vData
    End Property

    Public Property Get DataBaseConnectionInformation() As String
    DataBaseConnectionInformation = mstrDataBaseConnectionInformation
    End Property

    Public Sub OpenDatabaseConnection()
    Set mdbConnection = New ADODB.Connection
    mdbConnection.ConnectionString = mstrDataBaseConnectionInformation
    mdbConnection.Open
    End Sub

    Public Sub CloseDatabaseConnection()
    mdbConnection.Close
    Set mdbConnection = Nothing
    End Sub

    Public Sub Begin()
    mdbConnection.BeginTrans
    End Sub

    Public Sub Terminate()
    mdbConnection.CommitTrans
    End Sub


  12. #12
    Bernie Guest

    Re: more ADO stuff


    Hi,
    Without penetrating your code it seem to me that you set the column values
    before AddNew. BeginTrans/CommitTrans also seems a bit overkill in this case...

    Basics are

    1) An open, updatable recordset
    2) rs.AddNew
    3) set the columns with their values
    4) rs.Update

    or

    1) An open connection
    2) Create an insert-command
    3) Execute the command

    or

    1) An open connection
    2) rs.Open "INSERT INTO MyTable...."

    Bernie

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