Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Lichfield, UK
    Thanked 0 Times in 0 Posts

    Reading Data into a VB Application

    I hope someone can help me as I've reached a point where I am totally baffled. I apologise in advance for the length of the post but I want to explain clearly what the problem is.

    I currently have a VBA database application which runs on Access and has done so for nearly 10 years. I am looking into moving it across to a VB / SQL Server environment as Access is starting to creak in some places - several hundred thousand records in some tables. I thought that I would separate the 2 migrations so am currently looking at what would be involved in rewriting the front end and for now leaving the back end in Access.

    I've installed Visual Studio Express 2013 and am now just trying to write a stub application to test out the major things I need to understand before starting to move the application over to VB eg db connection, UI design, form transitions etc.

    I am new to VB.Net and realise it is a different beast to VBA but I must be honest I am becoming overwhelmed at the complexity behind doing what I thought would be simple tasks. The piece I am struggling with at the moment is how to read data into the application, navigate through it, amend it if necessary and then write it back to the database. This is a fundamental part of a database application yet I cannot find any documentation on how to do it, just reams of stuff about the different objects available to me: all Microsoft's documentation seems to be presented at you from the perspective of the design of the environment rather than how to build a solution.

    Here is how I have done it in Access / VBA:

    1 - I have created database class objects to separate the back end from the front.
    2 - VBA in the forms passes requirements of what data it needs to the db class object: fields, filters and order.
    3 - The db class object builds the SQL statement and using ADO generates a Recordset and returns to the front end the number of records it has found.
    4 - If the class object has found some records the front end then calls MoveNext, Previous etc methods in the db class object which in turn execute the equivalent methods on the recordset.
    5 - Whenever the db class object moves to a new record in the recordset it sets the class properties to values in that recordset row ( eg ID, Name, Address, PhoneNo etc) which are then exposed to the front end.
    6 - Front end amends properties of the class object if required which causes the db class to write the amendments back into the recordset and update the db.

    This has worked fine for me.

    I understand from reading the vast amount of documentation on ADO.Net that the Recordset concept is no longer used so I am really struggling to achieve step 3 above. What I think I have to do is:

    a) Set up an oleDbDataAdapter
    b) Define an oleDbCommand (using the SQL statement)
    c) Pass the command to the data adapter (SelectCommand) - this creates a Dataset?
    d) Populate the DataAdapter with the dataset.

    However this doesn't let me get at any data (I am obviously equating a "dataset" with a "recordset" and think in the old way).

    The documentation then goes on to say I can put the dataset in a dataview which I understand to be a Form control bound to the dataset which allows the user to amend it. At this stage I do not want to do that.

    So it goes on to tell me I should define the data tables, columns within the tables, relationships between the tables as part of the dataset. Do I really need to do this just to read some data from a database? Also when it talks about DataViews it does so in the context of a single table rather than a query pulling data from multiple tables which surely cannot be correct.

    So could someone help me by giving me the code I would need to do the following:

    Send a SELECT query to an Access db to return some data (the application is connected to the db)
    Make the data (multiple rows) returned by the query available to the application code so that it can be navigated through, read and if necessary amended and written back.

    Again apologies for the long post but I felt I had to provide sufficient information to explain my precise requirement.

    Many thanks in advance.

  2. #2
    Star Lounger
    Join Date
    Jun 2010
    Lichfield, UK
    Thanked 0 Times in 0 Posts
    Sorted it :-)
    The code below reads all data returned as specified in strQuery passed to it and outputs the data to the immediate window as well as counting the number of rows returned.

    Public Function GetData(strQuery As String) As Int16

    Dim intCount As Int16

    Dim objDataAdapter As New OleDb.OleDbDataAdapter
    Dim objCommand As OleDb.OleDbCommand
    Dim objReader As OleDb.OleDbDataReader

    objCommand = New OleDb.OleDbCommand(strQuery, conDb)
    objReader = objCommand.ExecuteReader
    If objReader.HasRows then
    While objReader.Read
    intcount = intcount +1
    Debug.Print (objReader("URN").ToString & " - " & objReader("Applicant").ToString)
    End While
    End If

    Debug.Print ("----------------")
    Debug.Print (intcount)
    Debug.Print ("----------------")

    GetData = intCount

    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts